EF Core Migrations Deep-Dive: Production-Ready Database Evolution

Imagine you're building a SaaS platform that handles millions of transactions daily. Your database schema evolves constantly—new features, performance optimizations, compliance requirements. Without a robust migration strategy, you risk data loss, downtime, and inconsistent states across environments. EF Core Migrations provide a structured way to evolve your database schema alongside your application code, ensuring that every deployment is predictable and reversible.

In this deep-dive, we'll go beyond basic Add-Migration and Update-Database. You'll learn how to handle complex scenarios like splitting tables, seeding data, generating idempotent scripts for production, and debugging migration failures. We'll also explore a real-world production incident that caused a 2-hour outage due to a poorly handled migration rollback. By the end, you'll have the confidence to manage database changes in high-stakes environments.

Whether you're working on a monolithic app or a microservices architecture, these techniques will help you maintain data integrity and zero-downtime deployments.

Understanding the Migration Pipeline

EF Core Migrations work by comparing your DbContext model with the current database schema. When you add a migration, EF Core generates a file containing Up and Down methods that describe how to transition between schema versions. The __EFMigrationsHistory table tracks which migrations have been applied.

Let's start with a simple example. Suppose you have a Blog entity and you want to add a Rating column. First, add the property to your model:

``csharp public class Blog { public int Id { get; set; } public string Name { get; set; } public int Rating { get; set; } // New property } ``

Then run: `` dotnet ef migrations add AddBlogRating ``

This generates a migration file with Up and Down methods. The Up method adds the column, and the Down method drops it. To apply, run: `` dotnet ef database update ``

But in production, you rarely run Update-Database directly. Instead, you generate SQL scripts that are reviewed and executed by DBAs. Use: `` dotnet ef migrations script --output script.sql ``

This script is idempotent if you use the --idempotent flag, meaning it can be run multiple times without error.

public partial class AddBlogRating : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<int>(
            name: "Rating",
            table: "Blogs",
            nullable: false,
            defaultValue: 0);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "Rating",
            table: "Blogs");
    }
}

Customizing Migrations with Raw SQL

Sometimes the fluent API isn't enough. You may need to execute raw SQL for complex operations like updating existing data, creating indexes with specific options, or using database-specific features. EF Core allows you to embed SQL in migrations using the Sql() method.

For example, suppose you want to add a computed column that concatenates first and last names:

``csharp protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.Sql(@" ALTER TABLE ""Users"" ADD ""FullName"" AS (""FirstName"" || ' ' || ""LastName"") "); } ``

Or you might need to backfill data after adding a non-nullable column:

```csharp protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.AddColumn<string>( name: "TenantId", table: "Blogs", nullable: false, defaultValue: "");

// Backfill existing rows with a default tenant migrationBuilder.Sql(@" UPDATE ""Blogs"" SET ""TenantId"" = 'default' WHERE ""TenantId"" = '' "); } ```

Be careful with SQL injection. Use parameterized SQL if you must include user input. Also, ensure your SQL is compatible with all target databases (e.g., SQL Server vs PostgreSQL).

public partial class AddComputedColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Add computed column
        migrationBuilder.Sql(@"
            ALTER TABLE ""Users"" ADD ""FullName"" AS (""FirstName"" || ' ' || ""LastName"")
        ");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            ALTER TABLE ""Users"" DROP COLUMN ""FullName""
        ");
    }
}

Seeding Data with Migrations

Seeding initial data is a common requirement. EF Core provides HasData() in the OnModelCreating method, but this only works for seed data that is part of the initial migration. For ongoing data seeding, you can use migration SQL or custom seeding logic.

Using HasData() is simple but has limitations: it only supports simple types and cannot reference other entities easily. For complex seeds, use Sql() in migrations.

Example: Seed a list of roles:

``csharp protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.InsertData( table: "Roles", columns: new[] { "Id", "Name", "NormalizedName" }, values: new object[,] { { Guid.NewGuid().ToString(), "Admin", "ADMIN" }, { Guid.NewGuid().ToString(), "User", "USER" } }); } ``

For production, consider using a separate seed service that runs after migrations, especially for large datasets. This avoids bloating migration files and allows for more complex logic.

public partial class SeedRoles : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: "Roles",
            columns: new[] { "Id", "Name", "NormalizedName" },
            values: new object[,]
            {
                { "1", "Admin", "ADMIN" },
                { "2", "User", "USER" }
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: "Roles",
            keyColumn: "Id",
            keyValues: new object[] { "1", "2" });
    }
}

Handling Complex Schema Changes: Splitting Tables

Sometimes you need to split one table into two, or merge tables. This requires careful migration planning to avoid data loss. Let's walk through splitting a Customer table into Customer and CustomerAddress.

Assume the original Customer table has Id, Name, Street, City. We want to move address fields to a new CustomerAddress table.

Steps: 1. Create the new table with a foreign key to Customer. 2. Copy data from Customer to CustomerAddress. 3. Drop the address columns from Customer.

In the Up method:

```csharp protected override void Up(MigrationBuilder migrationBuilder) { // Step 1: Create new table migrationBuilder.CreateTable( name: "CustomerAddresses", columns: table => new { Id = table.Column<int>(nullable: false) .Annotation("SqlServer:Identity", "1, 1"), CustomerId = table.Column<int>(nullable: false), Street = table.Column<string>(nullable: true), City = table.Column<string>(nullable: true) }, constraints: table => { table.PrimaryKey("PK_CustomerAddresses", x => x.Id); table.ForeignKey( name: "FK_CustomerAddresses_Customers_CustomerId", column: x => x.CustomerId, principalTable: "Customers", principalColumn: "Id", onDelete: ReferentialAction.Cascade); });

// Step 2: Copy data migrationBuilder.Sql(@" INSERT INTO ""CustomerAddresses"" (""CustomerId"", ""Street"", ""City"") SELECT ""Id"", ""Street"", ""City"" FROM ""Customers"" ");

// Step 3: Drop old columns migrationBuilder.DropColumn( name: "Street", table: "Customers"); migrationBuilder.DropColumn( name: "City", table: "Customers"); } ```

The Down method would reverse these steps: add columns back, copy data, drop the new table.

public partial class SplitCustomerTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Step 1: Create CustomerAddresses table
        migrationBuilder.CreateTable(
            name: "CustomerAddresses",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                CustomerId = table.Column<int>(nullable: false),
                Street = table.Column<string>(nullable: true),
                City = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_CustomerAddresses", x => x.Id);
                table.ForeignKey(
                    name: "FK_CustomerAddresses_Customers_CustomerId",
                    column: x => x.CustomerId,
                    principalTable: "Customers",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

        // Step 2: Copy data
        migrationBuilder.Sql(@"
            INSERT INTO ""CustomerAddresses"" (""CustomerId"", ""Street"", ""City"")
            SELECT ""Id"", ""Street"", ""City"" FROM ""Customers""
        ");

        // Step 3: Drop old columns
        migrationBuilder.DropColumn(name: "Street", table: "Customers");
        migrationBuilder.DropColumn(name: "City", table: "Customers");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Reverse: add columns, copy data, drop new table
        migrationBuilder.AddColumn<string>(
            name: "Street",
            table: "Customers",
            nullable: true);
        migrationBuilder.AddColumn<string>(
            name: "City",
            table: "Customers",
            nullable: true);

        migrationBuilder.Sql(@"
            UPDATE ""Customers""
            SET ""Street"" = a.""Street"", ""City"" = a.""City""
            FROM ""Customers"" c
            INNER JOIN ""CustomerAddresses"" a ON c.""Id"" = a.""CustomerId""
        ");

        migrationBuilder.DropTable(name: "CustomerAddresses");
    }
}

Managing Migration History and Versioning

The __EFMigrationsHistory table is crucial for tracking which migrations have been applied. Sometimes you need to manually intervene, e.g., when a migration fails partially or when you need to rebase migration history.

To squash migrations: 1. Ensure the database is up to date with all migrations. 2. Remove the Migrations folder (or rename it). 3. Run dotnet ef migrations add InitialCreate to create a new migration that snapshots the current model. 4. Delete the old migration files from source control. 5. Update __EFMigrationsHistory to reflect the new migration as applied.

This is a delicate operation; always backup the database first.

# Remove last migration (not applied)
dotnet ef migrations remove

# Add new initial migration after squashing
dotnet ef migrations add InitialCreate

# Generate script for squashed migration
dotnet ef migrations script --output squashed.sql

Testing Migrations in CI/CD

Automated testing of migrations is essential to catch issues early. You can write integration tests that apply migrations to a test database and verify the schema and data.

Example using xUnit and a test database (e.g., SQLite in-memory or a local SQL Server):

```csharp public class MigrationTests : IClassFixture<MigrationFixture> { private readonly MigrationFixture _fixture;

public MigrationTests(MigrationFixture fixture) { _fixture = fixture; }

[Fact] public void All_Migrations_Can_Apply_Successfully() { // Arrange var context = _fixture.CreateContext();

// Act context.Database.Migrate();

// Assert var appliedMigrations = context.Database.GetAppliedMigrations(); Assert.Contains("20231001000000_AddBlogRating", appliedMigrations); } }

public class MigrationFixture : IDisposable { private readonly string _connectionString = "Server=(localdb)\\mssqllocaldb;Database=TestMigrations_" + Guid.NewGuid();

public TestDbContext CreateContext() { var options = new DbContextOptionsBuilder<TestDbContext>() .UseSqlServer(_connectionString) .Options; return new TestDbContext(options); }

public void Dispose() { using var context = CreateContext(); context.Database.EnsureDeleted(); } } ```

You can also test rollbacks by applying a migration, then rolling back and verifying the schema reverts.

using Microsoft.EntityFrameworkCore;
using Xunit;

public class MigrationTests : IClassFixture<MigrationFixture>
{
    private readonly MigrationFixture _fixture;

    public MigrationTests(MigrationFixture fixture)
    {
        _fixture = fixture;
    }

    [Fact]
    public void All_Migrations_Can_Apply_Successfully()
    {
        using var context = _fixture.CreateContext();
        context.Database.Migrate();
        var applied = context.Database.GetAppliedMigrations();
        Assert.Contains("20231001000000_AddBlogRating", applied);
    }

    [Fact]
    public void Rollback_Works_Correctly()
    {
        using var context = _fixture.CreateContext();
        context.Database.Migrate();
        // Rollback to initial
        context.Database.Migrate("0");
        var applied = context.Database.GetAppliedMigrations();
        Assert.Empty(applied);
    }
}

public class MigrationFixture : IDisposable
{
    private readonly string _connectionString = $"Server=(localdb)\\mssqllocaldb;Database=TestMigrations_{Guid.NewGuid()}";

    public TestDbContext CreateContext()
    {
        var options = new DbContextOptionsBuilder<TestDbContext>()
            .UseSqlServer(_connectionString)
            .Options;
        return new TestDbContext(options);
    }

    public void Dispose()
    {
        using var context = CreateContext();
        context.Database.EnsureDeleted();
    }
}