Advanced Migrations — Custom SQL, Data Migrations and Squashing

EF Core migrations manage database schema changes as versioned C# classes. The basic migration workflow is well-known, but production scenarios require more — adding custom SQL for views and stored procedures, data migrations to populate new columns, and handling the accumulation of hundreds of migration files over time. migrationBuilder.Sql() is the escape hatch for anything EF Core cannot express in its migration DSL.

Advanced Migration Patterns #

// ── Add custom SQL to a migration ─────────────────────────────────────────
// dotnet ef migrations add AddPublishedPostView
public partial class AddPublishedPostView : Migration
{
    protected override void Up(MigrationBuilder mb)
    {
        // Create a view — EF Core has no native view support
        mb.Sql(@"
            CREATE OR ALTER VIEW dbo.vw_PublishedPostSummary
            WITH SCHEMABINDING AS
            SELECT p.Id, p.Title, p.Slug, p.Excerpt, p.ViewCount, p.CommentCount,
                   p.IsFeatured, p.PublishedAt, p.UpdatedAt,
                   u.DisplayName AS AuthorName, u.AvatarUrl AS AuthorAvatarUrl,
                   c.Name AS CategoryName, c.Slug AS CategorySlug
            FROM   dbo.Posts p
            JOIN   dbo.Users u ON u.Id = p.AuthorId
            LEFT   JOIN dbo.Categories c ON c.Id = p.CategoryId
            WHERE  p.IsPublished = 1 AND p.Status = 'published';");

        // Create an index — EF Core HasIndex is limited
        mb.Sql(@"
            CREATE NONCLUSTERED INDEX IX_Posts_Published_Date
            ON dbo.Posts (IsPublished, PublishedAt DESC)
            INCLUDE (Title, Slug, Excerpt, ViewCount, AuthorId, CategoryId)
            WHERE IsPublished = 1;");
    }

    protected override void Down(MigrationBuilder mb)
    {
        mb.Sql("DROP VIEW IF EXISTS dbo.vw_PublishedPostSummary;");
        mb.Sql("DROP INDEX IF EXISTS IX_Posts_Published_Date ON dbo.Posts;");
    }
}

// ── Data migration — populate new column without losing data ──────────────
// Scenario: Add WordCount column to Posts, populate from Body
public partial class AddWordCountColumn : Migration
{
    protected override void Up(MigrationBuilder mb)
    {
        // Step 1: Add nullable column (cannot add NOT NULL without default on existing data)
        mb.AddColumn<int>("WordCount", "Posts", nullable: true);

        // Step 2: Populate from existing data
        mb.Sql(@"
            UPDATE dbo.Posts
            SET    WordCount = LEN(TRIM(Body)) - LEN(REPLACE(TRIM(Body), ' ', '')) + 1
            WHERE  Body IS NOT NULL;");

        // Step 3: Set NOT NULL after all rows have values
        mb.AlterColumn<int>("WordCount", "Posts", nullable: false, defaultValue: 0);
    }

    protected override void Down(MigrationBuilder mb) =>
        mb.DropColumn("WordCount", "Posts");
}

// ── HasData seed — for reference/lookup data ──────────────────────────────
protected override void OnModelCreating(ModelBuilder mb)
{
    mb.Entity<Category>().HasData(
        new Category { Id = 1, Name = ".NET Development", Slug = "dotnet",    SortOrder = 1 },
        new Category { Id = 2, Name = "Angular",          Slug = "angular",   SortOrder = 2 },
        new Category { Id = 3, Name = "SQL Server",       Slug = "sqlserver", SortOrder = 3 }
    );
}
// dotnet ef migrations add SeedCategories — generates INSERT in migration

// ── Migration bundles — deploy without dotnet ef CLI ─────────────────────
// dotnet ef migrations bundle --output migrate.exe --self-contained
// On server: ./migrate.exe --connection "Server=prod;..."

Common Mistakes #

Mistake 1 — Adding NOT NULL column without default to existing table (migration fails) #

❌ Wrong — AddColumn<int>("WordCount", nullable: false) on a table with rows; SQL Server cannot add NOT NULL without a value for existing rows.

✅ Correct — add nullable, populate, then AlterColumn to NOT NULL with a default value.

Mistake 2 — Missing Down() implementation for custom SQL (rollback leaves database inconsistent) #

❌ Wrong — custom SQL in Up() with no Down(); rolling back the migration fails or leaves view/index orphaned.

✅ Correct — always implement Down() that exactly reverses the Up() custom SQL.