EF Core — Migrations

Key Points

• Migrations evolve schema over time. Add via dotnet ef migrations add Name; apply via Database.Migrate() or generate scripts for review.
• Generate idempotent SQL for production deployment via dotnet ef migrations script --idempotent — replay-safe.
• Don't call Database.EnsureCreated() in production — bypasses migrations.
• Coordinate schema + code deployment carefully: 3-phase deployment (expand → deploy → contract) for breaking schema changes.
• Pre/post-migration data fixes via custom SQL or migration Up overrides.
• Deploy migrations as a separate step (not at app startup) for production — surface failures early; avoid races.

Concepts (deep dive)

Adding a migration

dotnet ef migrations add InitialCreate                   # creates Migrations/{timestamp}_InitialCreate.cs
dotnet ef migrations add AddOrderStatusIndex            # next migration
dotnet ef database update                                # applies pending migrations to the configured DB
dotnet ef migrations remove                              # roll back unapplied migration in code

The migration file contains Up and Down methods with MigrationBuilder calls. Down is for rollback.

Idempotent SQL scripts

dotnet ef migrations script --idempotent --output migrate.sql

Idempotent means: each migration is wrapped in IF NOT EXISTS (...) BEGIN ... END. Running twice doesn't reapply. Production: always use idempotent scripts, applied via SqlPackage / sqlcmd / DbUp / Flyway.

# Range-specific:
dotnet ef migrations script FromMigration ToMigration --output 1to2.sql

Apply at startup (dev/non-prod only)

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDb>();
    await db.Database.MigrateAsync();
}

Database.MigrateAsync() reads __EFMigrationsHistory and applies what's missing. Avoid in production: - Race conditions across multiple replicas. - Long-running migrations block startup. - Migration failure crashes the app at the worst moment.

For production, deploy migrations as a CI step (or pre-deployment hook) and only deploy app code after migrations succeed.

Three-phase deployments (expand-contract)

For breaking schema changes, never deploy in one step:

Phase 1 — Expand: add new column/table; old code unaware. - Migration A: ADD COLUMN NewName NVARCHAR(100) NULL - Old app continues working (ignores new column).

Phase 2 — Deploy app: new code uses both old and new columns; backfills as needed. - Background job copies OldName → NewName.

Phase 3 — Contract: drop old column once migration complete. - Migration B: DROP COLUMN OldName

This pattern means never write a migration that breaks running code. Critical for zero-downtime deployments.

Custom SQL in migrations

public partial class BackfillStatus : Migration
{
    protected override void Up(MigrationBuilder b)
    {
        b.Sql("UPDATE Orders SET Status = 'Active' WHERE Status IS NULL;");
    }

    protected override void Down(MigrationBuilder b)
    {
        // Backfills are usually irreversible.
    }
}

For complex data migrations, custom SQL is the right tool. Prefer keeping data migrations separate from schema migrations — easier to retry/test.

Pre/post deployment scripts

For pre-production data fixes that aren't strict EF migrations, use: - DbUp — runs SQL scripts in order; tracks applied scripts in a table. - Flyway — same idea, JVM-rooted but works for SQL Server / Postgres. - Custom CI step.

Naming conventions

• AddXxx — additive change (column, table, index).
• UpdateXxx — modify column type, rename, etc.
• DropXxx — destructive.
• Prefix with timestamp (auto): 20260101120000_AddOrderStatusIndex.

Don't edit applied migrations

# WRONG
# Edit a migration that's already been applied to production

Once applied, the migration is part of history. Editing breaks downstream deployments. Add a new migration to fix the issue.

Multiple DbContexts

Each context has its own migrations history table. Specify the context:

dotnet ef migrations add AddX --context AppDb
dotnet ef database update --context AppDb

EF Bundle (.NET 7+)

dotnet ef migrations bundle --self-contained --runtime linux-x64
# Produces a single executable to apply migrations

Useful for CI: ship the bundle to deployment, run it without needing the .NET SDK on the target.

Conventions for production migrations

1. Review every migration in PR — diff Migrations/*.cs and --script output.
2. Test on a copy of production schema in CI.
3. Idempotent SQL for replay safety.
4. Backwards-compatible within a single deployment (3-phase for breaking).
5. Keep migrations small — easier to roll forward; failures less catastrophic.
6. Squash migrations periodically in dev/test by deleting all and re-adding InitialCreate.

Code: correct vs wrong

❌ Wrong: EnsureCreated in production

// Program.cs
app.Services.GetRequiredService<AppDb>().Database.EnsureCreated();
// Skips migrations; creates schema from current model. No history.

✅ Correct: migrations

// CI: dotnet ef migrations script --idempotent | sqlcmd
// Or: dotnet ef bundle, run as a deploy step

❌ Wrong: dropping a column the running app reads

b.DropColumn(name: "OldName", table: "Customers");   // ❌ rolling deploy → some replicas crash

✅ Correct: 3-phase

Phase 1: stop reading the old column in code (deploy app). Phase 2: drop column (migration).

❌ Wrong: long migration at startup

await db.Database.MigrateAsync();   // 5-minute migration; pod startup hangs; readiness fails

✅ Correct: separate deploy step

CI applies migration before rolling app deployment.

Design patterns for this topic

Pattern 1 — "Idempotent SQL scripts in CI"

• Intent: replay-safe; reviewable.

Pattern 2 — "Three-phase expand-contract"

• Intent: zero-downtime breaking changes.

Pattern 3 — "Migration bundle for SDK-less deploy"

• Intent: standalone executable.

Pattern 4 — "Separate data migrations from schema migrations"

• Intent: easier to retry, test, document.

Pattern 5 — "Review every migration in PR"

• Intent: catch destructive changes pre-merge.

Pros & cons / trade-offs

When to use / when to avoid

• Use idempotent scripts in production, applied as a CI step.
• Use 3-phase deployment for breaking schema changes.
• Avoid EnsureCreated outside test fixtures.
• Avoid Migrate() at startup in multi-replica production.
• Avoid editing applied migrations — add new ones.

Interview Q&A

Q1. Difference between Migrate() and EnsureCreated? Migrate applies pending migrations using history table. EnsureCreated creates the schema from the current model in one shot — no history, no incremental updates.

Q2. What's an idempotent migration script? A script wrapped in IF NOT EXISTS checks per migration. Running twice is safe.

Q3. What's the three-phase deployment? Expand (add new schema, additive only) → Deploy (new code uses both old and new) → Contract (drop old). Avoids breaking running code.

Q4. Why avoid Migrate() at startup in production? Race conditions across replicas; long-running migrations block startup; migration failure crashes the deploy.

Q5. What's a migration bundle? A standalone executable (dotnet ef migrations bundle) that applies migrations without requiring .NET SDK on the target.

Q6. How do you handle data migrations? Custom SQL in a migration's Up method, or separate tools (DbUp, Flyway). Prefer separating data from schema.

Q7. Multiple contexts in one project? Specify with --context Name in dotnet ef commands. Each has its own __EFMigrationsHistory table.

Q8. Should you edit a migration after applying? No — add a new migration. Editing breaks downstream deployments.

Q9. How do you roll back a bad migration? Apply previous migration: dotnet ef database update PreviousMigrationName. But destructive Down may lose data — usually you "roll forward" with a fix.

Q10. What's __EFMigrationsHistory? The table EF Core maintains in your database listing applied migrations.

Gotchas / common mistakes

• ⚠️ EnsureCreated — bypasses migrations.
• ⚠️ Migrate at startup multi-replica — race.
• ⚠️ Editing applied migration — breaks deploys.
• ⚠️ Dropping columns running code reads — crash.
• ⚠️ AsTracking on a tracked entity from another context — exception.
• ⚠️ Auto-rollback on Down — destroys data.

Further reading

• EF Core migrations
• Idempotent scripts
• Migration bundles
• DbUp