Migrations Interview Questions & Answers

EF Core migrations are versioned C# classes that translate model changes into database schema operations. Each migration has an Up (apply) and a Down (revert) method, making schema evolution repeatable and reversible.

// 1. Add a migration after changing the model:
// dotnet ef migrations add AddOrderStatusColumn

// Generated migration file:
public partial class AddOrderStatusColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Status",
            table: "Orders",
            type: "nvarchar(50)",
            nullable: false,
            defaultValue: "Pending");

        migrationBuilder.CreateIndex(
            name: "IX_Orders_Status",
            table: "Orders",
            column: "Status");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex("IX_Orders_Status", "Orders");
        migrationBuilder.DropColumn("Status", "Orders");
    }
}

// 2. Apply to the database:
// dotnet ef database update
// Or programmatically at startup:
await db.Database.MigrateAsync(); // applies all pending migrations

Benefits over manual scripts: migrations are source-controlled, tied to the model, reversible with Down, and apply in order via the __EFMigrationsHistory table.

Rule of thumb: One migration per logical schema change. Never edit a migration once it's been applied to any shared environment — add a new migration instead.

The migration workflow moves schema changes through dev → CI → staging → prod in a controlled, repeatable way.

# 1. Change the model (add property, new entity, new relationship)

# 2. Generate the migration:
dotnet ef migrations add <MigrationName> --project MyApp.Data --startup-project MyApp.Api

# 3. Review the generated migration file — always inspect Up/Down before committing:
#    migrations/<timestamp>_<MigrationName>.cs

# 4. Apply to local dev DB:
dotnet ef database update

# 5. Commit migration files alongside the model change — one PR, both together.

# 6. Apply in CI (integration tests run against a real migrated DB):
#    services.AddDbContext<AppDbContext>(...);
#    db.Database.Migrate(); // in test fixture setup

# 7. Apply to staging and production — never dotnet ef in prod:
#    Option A: db.Database.MigrateAsync() in app startup (simple, single-server)
#    Option B: migration bundle (preferred for containerised deployments):
dotnet ef migrations bundle --self-contained -o migrate
# Then run: ./migrate --connection "..."

// Programmatic startup migration (option A):
var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync(); // idempotent — skips applied migrations
}

await app.RunAsync();

Rule of thumb: Always commit migration files with the model change. Never apply dotnet ef database update directly to production — use MigrateAsync at startup or a migration bundle in a deploy step.

__EFMigrationsHistory is a bookkeeping table EF Core creates and maintains. It records every applied migration by name and the EF Core product version, giving MigrateAsync its idempotency.

-- Created automatically by EF Core; looks like:
CREATE TABLE __EFMigrationsHistory (
    MigrationId     nvarchar(150) NOT NULL,
    ProductVersion  nvarchar(32)  NOT NULL,
    CONSTRAINT PK___EFMigrationsHistory PRIMARY KEY (MigrationId)
);

-- After applying two migrations, the table contains:
-- | MigrationId                            | ProductVersion |
-- | 20240101000000_InitialCreate            | 8.0.0          |
-- | 20240615120000_AddOrderStatusColumn     | 8.0.0          |

// Check pending migrations programmatically:
var pending = await db.Database.GetPendingMigrationsAsync();
Console.WriteLine($"{pending.Count()} migration(s) pending");

// List applied migrations:
var applied = await db.Database.GetAppliedMigrationsAsync();

// Remove a migration that hasn't been applied yet (resets the snapshot):
// dotnet ef migrations remove

// "Fake" applying a migration (schema already exists but EF doesn't know):
// dotnet ef database update --target MigrationName
// Then manually insert a row into __EFMigrationsHistory.

Rule of thumb: Never edit __EFMigrationsHistory manually in production unless you are recovering from a partial failed migration. If a migration fails mid-run, fix the migration and rerun — don't delete the history row.

EF Core provides model-based seeding via HasData in OnModelCreating for static reference data, and custom seeding code for dynamic or environment-specific data.

// Model-based seeding — becomes part of the migration:
protected override void OnModelCreating(ModelBuilder mb)
{
    mb.Entity<Category>().HasData(
        new Category { Id = 1, Name = "Electronics", Slug = "electronics" },
        new Category { Id = 2, Name = "Clothing",    Slug = "clothing"    },
        new Category { Id = 3, Name = "Books",       Slug = "books"       }
    );
}
// dotnet ef migrations add SeedCategories generates an InsertData migration.
// EF Core tracks these rows — updating HasData generates an UpdateData migration.
// Primary keys must be set explicitly (no DB-generated keys for seed data).

// Custom startup seeding — for dynamic data not tracked by migrations:
public static class DatabaseSeeder
{
    public static async Task SeedAsync(AppDbContext db)
    {
        if (await db.Users.AnyAsync(u => u.Email == "admin@example.com"))
            return; // idempotent — skip if already seeded

        db.Users.Add(new User
        {
            Email    = "admin@example.com",
            Role     = "Admin",
            Password = PasswordHasher.Hash("change-me-on-first-login")
        });

        await db.SaveChangesAsync();
    }
}

// Call after MigrateAsync at startup:
await db.Database.MigrateAsync();
await DatabaseSeeder.SeedAsync(db);

Rule of thumb: Use HasData only for static reference data with stable IDs (lookup tables, role definitions). Use custom seeder code for admin users, test data, or anything that varies by environment.

A design-time factory (IDesignTimeDbContextFactory<T>) tells EF migration tooling how to construct the DbContext when the startup project can't be used as-is — for example when the context is in a separate class library.

// Required when:
// - AppDbContext lives in a class library (no Program.cs to discover)
// - Context constructor needs non-DI arguments at design time
// - Connection string differs between design time and runtime

public class AppDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    public AppDbContext CreateDbContext(string[] args)
    {
        // Read config from appsettings.json at design time:
        var config = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .AddEnvironmentVariables()
            .Build();

        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(config.GetConnectionString("Default"))
            .Options;

        return new AppDbContext(options);
    }
}

EF tooling (dotnet ef migrations add, dotnet ef database update) automatically discovers IDesignTimeDbContextFactory<T> implementations at design time.

# Specify both projects when context is in a library:
dotnet ef migrations add InitialCreate \
    --project MyApp.Data \          # where DbContext lives
    --startup-project MyApp.Api      # where appsettings.json lives

Rule of thumb: Always create a design-time factory when AppDbContext is in a class library. Without it, dotnet ef either fails or picks the wrong connection string.

Migration bundles are standalone executables that apply pending migrations — no .NET SDK or EF CLI required at deploy time. They are the production-safe alternative to running dotnet ef database update in a pipeline.

# Build a self-contained migration bundle:
dotnet ef migrations bundle \
    --self-contained \
    --runtime linux-x64 \
    -o ./migrate \
    --project MyApp.Data \
    --startup-project MyApp.Api

# This produces a single binary: ./migrate

# Run in CI/CD or Kubernetes init container:
./migrate --connection "Server=prod;Database=App;User=sa;Password=..."

# Flags:
# --target <MigrationName>   — migrate to a specific version (rollback)
# --no-transactions          — disable wrapping each migration in a transaction
# --verbose                  — show individual SQL statements

# Kubernetes — init container applies migrations before the app starts:
initContainers:
  - name: db-migrate
    image: myapp-migrate:latest  # contains the bundle
    command: ["./migrate"]
    env:
      - name: ConnectionStrings__Default
        valueFrom:
          secretKeyRef:
            name: db-secret
            key: connection-string

Rule of thumb: Build a migration bundle as part of CI and run it in an init container or deploy step — never auto-migrate inside the application process in a multi-replica deployment (races between replicas).

EF Core supports targeted rollback via dotnet ef database update <target>, which calls each migration's Down method in reverse order.

# List all migrations (applied and pending):
dotnet ef migrations list

# Roll back to a specific migration (exclusive — that migration stays applied):
dotnet ef database update AddOrderStatusColumn

# Roll back ALL migrations (empty database — __EFMigrationsHistory stays):
dotnet ef database update 0

# Remove the last migration that hasn't been applied to any shared DB:
dotnet ef migrations remove
# Rewrites the model snapshot — only safe if the migration hasn't been shared.

// Programmatic rollback (useful in integration tests):
await db.Database.MigrateAsync();  // apply up to latest

// Or target a specific migration in a test fixture:
await db.GetService<IMigrator>()
        .MigrateAsync("20240101000000_InitialCreate"); // migrate to this point

Production rollback considerations:

• Down methods must be written carefully — data loss is possible (e.g., DropColumn).
• For zero-downtime deploys, prefer forward-only migrations (never auto-populate Down).
• Always back up the database before rolling back in production.

Rule of thumb: Write Down methods when the migration is purely structural (indexes, column renames). For migrations that delete columns or tables, leave Down empty and add a forward migration to recover — never roll back data loss in production.

As migrations accumulate, startup time and migration history grow. Squashing replaces many small migrations with a single baseline migration.

# 1. Ensure all team environments are up to date with latest migration.

# 2. Delete all existing migration files:
rm Migrations/*.cs   # keep only the Designer snapshot

# 3. Create a new baseline migration from the current model snapshot:
dotnet ef migrations add Baseline --ignore-changes
# --ignore-changes: generates a migration without diffing (uses the current model)

# 4. Mark the new migration as already applied on existing databases:
INSERT INTO __EFMigrationsHistory (MigrationId, ProductVersion)
VALUES ('20260101000000_Baseline', '8.0.0');
# Then delete all previous rows from the table.

# 5. Fresh databases get the Baseline migration applied normally.

// Alternative: EnsureCreated for dev/test (no migrations at all):
await db.Database.EnsureCreatedAsync();
// Creates schema from the current model snapshot — no migration history.
// Useful in tests; NOT a replacement for migrations in production.

Rule of thumb: Squash migrations when the history is unwieldy (50+ migrations) OR when you want a clean baseline after a major refactor. Coordinate with all environments before squashing — every DB must be at the latest migration first.

EF Core provides several ways to preview SQL before applying it — critical for catching unexpected schema changes in production.

# Script the migration instead of applying it:
dotnet ef migrations script                    # all migrations → SQL file
dotnet ef migrations script FromMigration ToMigration  # range

# Idempotent script (safe to run multiple times):
dotnet ef migrations script --idempotent
# Wraps each migration in IF NOT EXISTS checks against __EFMigrationsHistory

# Preview what update would do (no DB changes):
dotnet ef database update --dry-run           # prints SQL, doesn't execute

// Log all generated SQL at runtime via EF Core logging:
builder.Services.AddDbContext<AppDbContext>(options =>
    options
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)    // all EF logs
        .EnableSensitiveDataLogging());                    // include param values

// Or capture in a StringBuilder for tests:
var log = new StringBuilder();
options.LogTo(msg => log.AppendLine(msg), LogLevel.Information);

Rule of thumb: Always run dotnet ef migrations script --idempotent in CI and review the output before applying to staging or production. Never apply a migration you haven't read.

Some applications split concerns across multiple DbContext types — for example, separate contexts for the main domain and for identity management. Each needs its own migration history table.

// Two separate contexts — each with its own schema area:
public class AppDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<Product> Products { get; set; }
}

public class IdentityDbContext : IdentityDbContext<ApplicationUser>
{
    // ASP.NET Core Identity tables (AspNetUsers, AspNetRoles, etc.)
}

// Register both:
builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseSqlServer(connectionString));

builder.Services.AddDbContext<IdentityDbContext>(o =>
    o.UseSqlServer(connectionString));

// Separate migration history tables to avoid conflicts:
protected override void OnModelCreating(ModelBuilder mb)
{
    mb.HasDefaultSchema("identity"); // or use MigrationsHistoryTable
}

// Or configure explicitly:
options.UseSqlServer(conn, sql =>
    sql.MigrationsHistoryTable("__IdentityMigrationsHistory", "identity"));

# Run migrations per context:
dotnet ef migrations add InitialCreate --context AppDbContext
dotnet ef migrations add IdentityInit   --context IdentityDbContext

dotnet ef database update --context AppDbContext
dotnet ef database update --context IdentityDbContext

Rule of thumb: Use separate DbContext types for bounded contexts or separate infrastructure concerns (identity, tenancy, auditing). Always configure distinct MigrationsHistoryTable names to prevent migration conflicts.

EnsureCreated creates the database schema from the current EF model snapshot in one step — no migration files involved. MigrateAsync applies versioned migration files in order and records them in __EFMigrationsHistory.

// EnsureCreated — creates schema if the DB doesn't exist; no-op if it does:
await db.Database.EnsureCreatedAsync();
// Good for: unit/integration tests, quick prototyping, in-memory database
// Bad for: production — bypasses migrations; EF can't diff or evolve the schema later

// MigrateAsync — applies all pending migrations in order; idempotent:
await db.Database.MigrateAsync();
// Good for: production, staging, CI — schema is versioned and repeatable
// Requires migration files to exist first (dotnet ef migrations add)

// Critical incompatibility:
// If you call EnsureCreated on a database, then later switch to migrations,
// MigrateAsync fails — the schema exists but __EFMigrationsHistory is empty.
// EF thinks migrations haven't been applied and tries to re-create tables → error.

// Test setup pattern — EnsureCreated is fine because tests always start fresh:
public class TestFixture : IAsyncLifetime
{
    private readonly AppDbContext _db;

    public async Task InitializeAsync()
    {
        await _db.Database.EnsureDeletedAsync(); // clean slate
        await _db.Database.EnsureCreatedAsync(); // schema from model snapshot
    }

    public async Task DisposeAsync()
        => await _db.Database.EnsureDeletedAsync();
}

Rule of thumb: Use EnsureCreated only in tests or demos with an in-memory or SQLite database. Use MigrateAsync everywhere else — it's the only approach that supports schema evolution without data loss.

Model/migration drift occurs when the model changes but no migration is added, meaning the deployed schema won't match the running code. Detecting this in CI prevents production failures.

# EF Core 8+ — dotnet ef migrations has-pending-model-changes exits non-zero
# if the model snapshot differs from the current model (no migration was generated):
dotnet ef migrations has-pending-model-changes \
    --project MyApp.Data \
    --startup-project MyApp.Api

# CI step (GitHub Actions / bash):
if ! dotnet ef migrations has-pending-model-changes ...; then
    echo "ERROR: Model changed without a migration. Run 'dotnet ef migrations add'."
    exit 1
fi

// At startup — warn if any migration is pending (alternative CI check):
var pending = await db.Database.GetPendingMigrationsAsync();
if (pending.Any())
    throw new InvalidOperationException(
        $"Database has {pending.Count()} pending migration(s). Run MigrateAsync or apply the bundle.");

// Integration test guard:
[Fact]
public void NoUnappliedMigrations()
{
    using var db = CreateTestContext();
    db.Database.Migrate();
    var pending = db.Database.GetPendingMigrations();
    Assert.Empty(pending);
}

Rule of thumb: Add dotnet ef migrations has-pending-model-changes as a CI step. Gate merges on a clean result — a model change without a migration is a production outage waiting to happen.

By default, each migration's Up method runs inside its own database transaction, so a failing migration is rolled back cleanly. However, some DDL statements are non-transactional on certain databases (SQL Server: CREATE DATABASE, ALTER DATABASE; PostgreSQL: certain index operations).

// Default behaviour — each migration is wrapped automatically:
public partial class AddOrderStatusColumn : Migration
{
    // EF Core wraps this Up() in a transaction unless you suppress it:
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Status",
            table: "Orders",
            type: "nvarchar(50)",
            nullable: false,
            defaultValue: "Pending");
    }

    // Opt out of the transaction for this migration (e.g., CREATE INDEX CONCURRENTLY on Postgres):
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            "CREATE INDEX CONCURRENTLY IX_Orders_Status ON Orders(Status);",
            suppressTransaction: true); // run outside a transaction
    }
}

// Apply without wrapping migrations in transactions (bundle flag):
// ./migrate --no-transactions

// Manual transaction via MigrationBuilder.BeginTransaction is not available —
// the framework controls the transaction boundary. Use suppressTransaction: true
// only for statements that cannot run inside a transaction.

Partial migration failure in production:

• If the migration throws after some statements but inside the EF transaction, the DB is rolled back.
• If suppressTransaction: true statements fail, the DB is left in a partial state — you must fix and rerun manually. Always script --idempotent SQL before running these.

Rule of thumb: Rely on EF's default transaction wrapping. Use suppressTransaction: true only for database-specific statements that explicitly forbid transactions. Test those migrations on a staging clone before production.

EF Core's RenameColumn migrates column names without dropping and recreating them, preserving existing data. Without it, EF would generate a DropColumn + AddColumn pair, which deletes the data in that column.

// Step 1 — rename the property in the entity class:
public class Customer
{
    public int Id { get; set; }
    // Before: public string FullName { get; set; } = "";
    public string DisplayName { get; set; } = "";  // renamed property
}

// Step 2 — generate the migration:
// dotnet ef migrations add RenameCustomerDisplayName

// The auto-generated migration MAY produce DropColumn + AddColumn (data loss!):
// Fix it manually to use RenameColumn instead:
public partial class RenameCustomerDisplayName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Safe — preserves column data:
        migrationBuilder.RenameColumn(
            name: "FullName",
            table: "Customers",
            newName: "DisplayName");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "DisplayName",
            table: "Customers",
            newName: "FullName");
    }
}

// Step 3 — map the new column name to the property if needed:
modelBuilder.Entity<Customer>()
    .Property(c => c.DisplayName)
    .HasColumnName("DisplayName"); // explicit; EF derives it from property name by default

Rule of thumb: Always inspect auto-generated migrations before applying them. When renaming a column, replace any DropColumn/AddColumn pair with a single RenameColumn call to avoid silent data loss.

Placing migrations in a dedicated assembly (e.g., MyApp.Migrations) keeps the data layer clean and allows independent versioning. It requires configuring both the MigrationsAssembly option and a design-time factory.

// Project layout:
// MyApp.Data        — contains AppDbContext, entity configs
// MyApp.Migrations  — contains only migration files
// MyApp.Api         — startup project

// In MyApp.Api Program.cs — tell EF where migrations live:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        connectionString,
        sql => sql.MigrationsAssembly("MyApp.Migrations"))); // assembly name

// In MyApp.Migrations — design-time factory so dotnet ef can build the context:
public class AppDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    public AppDbContext CreateDbContext(string[] args)
    {
        var config = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json", optional: true)
            .AddEnvironmentVariables()
            .Build();

        var opts = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlServer(
                config.GetConnectionString("Default"),
                sql => sql.MigrationsAssembly("MyApp.Migrations"))
            .Options;

        return new AppDbContext(opts);
    }
}

# Generate a migration — target MyApp.Migrations, startup project MyApp.Api:
dotnet ef migrations add InitialCreate \
    --project MyApp.Migrations \
    --startup-project MyApp.Api \
    --context AppDbContext

# Apply:
dotnet ef database update \
    --project MyApp.Migrations \
    --startup-project MyApp.Api

Rule of thumb: Move migrations to a separate assembly for large solutions where the data layer is shared across multiple apps (e.g., web API + background worker). Always keep MigrationsAssembly in sync between runtime registration and the design-time factory.