Initial Model Setup

To demonstrate database migrations, we will define a domain model with two entities: Department and Employee. These classes will establish a one-to-many relationship where a department contains multiple employees.

public class Department
{
    [Key]
    public int DepartmentId { get; set; }
    public string DepartmentName { get; set; }

    public virtual ICollection<Employee> Staff { get; set; }
}

public class Employee
{
    public int EmployeeId { get; set; }
    public string FullName { get; set; }
    
    public int DeptId { get; set; }
    
    [ForeignKey("DeptId")]
    public Department Department { get; set; }
}

The following snippet initializes the context with sample data:

static void Main(string[] args)
{
    var devDept = new Department { DepartmentName = "Development" };
    var qaDept = new Department { DepartmentName = "QA" };

    var emp1 = new Employee { FullName = "Alice Smith", Department = devDept };
    var emp2 = new Employee { FullName = "Bob Jones", Department = qaDept };

    using (var context = new CompanyContext())
    {
        context.Departments.Add(devDept);
        context.Departments.Add(qaDept);
        context.SaveChanges();
        Console.WriteLine("Database initialized successfully.");
    }
}

Enabling Migrations

When the entity model changes after the database has been created, the Entity Framework runtime will throw an exception stating that the model has changed. To resolve this, we must enable Code First Migrations.

Open the Package Manager Console in Visual Studio and run the following command. Ensure the correct project is selected in the "Default project" dropdown.

Enable-Migrations -EnableAutomaticMigrations

This command generates a Migrations folder in your project and adds a Configuration.cs file. The configuration class is set up as follows:

internal sealed class Configuration : DbMigrationsConfiguration<CompanyContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        ContextKey = "DataAccess.CompanyContext";
    }

    protected override void Seed(CompanyContext context)
    {
        // This method runs after migrating to the latest version.
        // You can use the AddOrUpdate helper method to avoid creating duplicate seed data.
    }
}

Scenario 1: Adding a New Property

Suppose we need to add an EmailAddress property to the Employee class.

public class Employee
{
    public int EmployeeId { get; set; }
    public string FullName { get; set; }
    public string EmailAddress { get; set; } // New property
    
    // ... rest of the class
}

After modifying the class, create a new migration snapshot by running:

Add-Migration AddEmployeeEmail

This generates a new class file (e.g., 201310311234567_AddEmployeeEmail.cs) containing the logic to apply and revert the change.

public partial class AddEmployeeEmail : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.Employees", "EmailAddress", c => c.String());
    }
    
    public override void Down()
    {
        DropColumn("dbo.Employees", "EmailAddress");
    }
}

To apply this change to the actual database, execute:

Update-Database -Verbose

Scenario 2: Adding a New Class

Next, we will add a new entity called Asset to track equipment assigned to employees.

public class Asset
{
    public int AssetId { get; set; }
    public string SerialNumber { get; set; }
    public string ModelName { get; set; }
}

Remember to add the DbSet<Asset> to your context class. Then, create the migration:

Add-Migration AddAssetTable

The generated migration will look like this:

public partial class AddAssetTable : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Assets",
            c => new
                {
                    AssetId = c.Int(nullable: false, identity: true),
                    SerialNumber = c.String(),
                    ModelName = c.String(),
                })
            .PrimaryKey(t => t.AssetId);
    }
    
    public override void Down()
    {
        DropTable("dbo.Assets");
    }
}

Apply the update:

Update-Database -Verbose

Scenario 3: Removing a Property

If we decide that SerialNumber is no longer needed on the Asset class, we can remove the property.

public class Asset
{
    public int AssetId { get; set; }
    // public string SerialNumber { get; set; } -- Removed
    public string ModelName { get; set; }
}

Create the migration for this modification:

Add-Migration RemoveAssetSerial

The generated code drops the column:

public partial class RemoveAssetSerial : DbMigration
{
    public override void Up()
    {
        DropColumn("dbo.Assets", "SerialNumber");
    }
    
    public override void Down()
    {
        AddColumn("dbo.Assets", "SerialNumber", c => c.String());
    }
}

Execute the update command:

Update-Database -Verbose

Scenario 4: Rolling Back Migrations

Sometimes it is necessary to revert the database to a previous state. The Update-Database command supports a -TargetMigration parameter.

To revert the database to the state it was in after the AddAssetTable migration (effectivley undoing RemoveAssetSerial):

Update-Database -TargetMigration:AddAssetTable

To roll back all migrations and return to an empty database state, use:

Update-Database -TargetMigration:$InitialDatabase

Note: Rolling back to the initial database may result in data loss. You must ensure that AutomaticMigrationDataLossAllowed is set to true in the Configuration class constructor.

Scenario 5: Generating SQL Scripts

If you need to deploy changes to a production environment, you might prefer to generate a SQL script rather than executing the migration directly. The following command generates a SQL script representing the changes between two specific migration versions.

Update-Database -Script -SourceMigration:AddAssetTable -TargetMigration:RemoveAssetSerial

This outputs the SQL script to the Visual Studio output window or a new query window. The -SourceMigration indicates the starting point, and -TargetMigration indicates the endpoint. If -SourceMigration is omitted, it defaults to the current state of the database.