EF Core migrations and production migration safety

Overview

Entity Framework Core migrations are a way to evolve a relational database schema as the application model changes. Instead of manually updating the database every time an entity, relationship, index, constraint, or column changes, EF Core can compare the current model with the previous model snapshot and generate migration files that describe the schema change.

This topic matters because database changes are among the riskiest parts of software delivery. A bad migration can drop data, lock large tables, break old application versions, fail halfway through deployment, or make rollback difficult. In production systems, migrations are not just a developer convenience. They are part of release management, operational safety, data protection, and system design.

In real projects, EF Core migrations are commonly used for:

• Creating and evolving tables, columns, indexes, relationships, and constraints.
• Keeping development, staging, and production schemas aligned.
• Reviewing generated SQL before deployment.
• Applying schema changes through CI/CD pipelines.
• Coordinating application deployments with database deployments.
• Preparing rollback and recovery plans.
• Managing data migrations when schema changes require moving or transforming data.

Interviewers ask about this topic because it reveals whether a developer understands both EF Core mechanics and production database safety. A strong answer should show that you can generate migrations, inspect generated files, produce SQL scripts, review risk, test migrations, plan rollback, and avoid unsafe deployment habits such as blindly running schema changes from application startup.

Core Concepts

What EF Core migrations are

An EF Core migration is a generated code file that describes how to change the database schema from one version to another.

A typical migration contains:

• An Up method that applies the change.
• A Down method that reverses the change.
• Metadata that identifies the migration.
• A model snapshot update that represents the latest EF Core model.

Example migration:

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

public partial class AddCustomerEmail : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Email",
            table: "Customers",
            type: "nvarchar(320)",
            maxLength: 320,
            nullable: true);

        migrationBuilder.CreateIndex(
            name: "IX_Customers_Email",
            table: "Customers",
            column: "Email");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_Customers_Email",
            table: "Customers");

        migrationBuilder.DropColumn(
            name: "Email",
            table: "Customers");
    }
}

The migration is source code. It should be reviewed like any other code change.

Why migrations exist

Applications change over time. Entities gain new properties, indexes are added for performance, relationships change, columns become required, and old fields are removed. The database schema must evolve with the application model.

EF Core migrations help by:

• Recording incremental schema changes.
• Making schema changes repeatable.
• Allowing teams to apply the same changes across environments.
• Keeping changes in source control.
• Generating SQL scripts from migration code.
• Supporting rollback script generation.

Migrations do not remove the need for database review. EF Core can generate useful code, but the developer is still responsible for validating whether the generated migration is safe.

Key terminology

How EF Core generates a migration

When a developer runs a command such as:

dotnet ef migrations add AddCustomerEmail

EF Core compares:

1. The current model built from the DbContext.
2. The previous model stored in the migration snapshot.

It then generates migration code that represents the difference.

Example workflow:

dotnet ef migrations add AddCustomerEmail
dotnet ef migrations script
dotnet ef database update

In Visual Studio Package Manager Console, the equivalent commands are commonly:

Add-Migration AddCustomerEmail
Script-Migration
Update-Database

The migration name should describe the change, similar to a commit message.

Good names:

AddCustomerEmail
CreateOrderAuditTable
AddIndexToInvoiceNumber
MakeProductSkuRequired

Poor names:

Update1
FixStuff
NewMigration
Changes

Migration files and model snapshot

When a migration is added, EF Core usually generates files similar to:

Migrations/
  20260516101523_AddCustomerEmail.cs
  20260516101523_AddCustomerEmail.Designer.cs
  ApplicationDbContextModelSnapshot.cs

The main migration file contains the Up and Down methods.

The designer file contains migration metadata.

The snapshot file represents the current model state after all migrations.

The snapshot is important because EF Core uses it to determine what changed when creating the next migration. If the snapshot is corrupted or incorrectly merged, future migrations can become wrong.

In team environments, snapshot merge conflicts should be handled carefully. Do not blindly accept one side of a merge conflict. Build the project, add a test migration if needed, inspect the generated operations, and confirm that the final snapshot accurately represents the intended model.

__EFMigrationsHistory

EF Core creates a table named __EFMigrationsHistory by default. This table records which migrations have been applied to a database.

A simplified example:

SELECT MigrationId, ProductVersion
FROM __EFMigrationsHistory
ORDER BY MigrationId;

Example result:

20260510120000_InitialCreate
20260512143000_AddCustomerEmail
20260515100000_CreateOrderAuditTable

EF Core uses this table to know which migrations are pending.

Important points:

• The table tracks applied migrations, not every possible model state.
• Do not manually edit this table unless you fully understand the consequences.
• If the table is missing, incorrect, or out of sync, EF Core may try to apply the wrong migrations.
• Idempotent scripts use this table to skip already-applied migrations.

Applying migrations during development

For local development, it is common to run:

dotnet ef database update

This applies pending migrations to the configured database.

You can update to a specific migration:

dotnet ef database update AddCustomerEmail

You can also roll back locally by updating to an earlier migration:

dotnet ef database update InitialCreate

For local development, this workflow is productive and usually acceptable.

For production, direct use of dotnet ef database update is usually not the safest choice because it applies changes directly without a separate SQL review step.

SQL scripts for production deployment

A common production-safe approach is to generate SQL from EF Core migrations and review it before deployment.

Generate a script from an empty database to the latest migration:

dotnet ef migrations script

Generate a script from a specific migration to the latest migration:

dotnet ef migrations script AddCustomerEmail

Generate a script between two migrations:

dotnet ef migrations script AddCustomerEmail CreateOrderAuditTable

Generate an idempotent script:

dotnet ef migrations script --idempotent

A reviewed SQL script is often preferred in production because:

• The team can inspect exactly what will run.
• A DBA can review performance and locking impact.
• Dangerous operations such as column drops can be detected.
• The script can be archived with the release.
• The script can be tested against a restored production-like database.
• Deployment tooling can apply the script consistently.

Idempotent SQL scripts

An idempotent script checks the migration history table and applies only missing migrations.

This is useful when:

• Different environments may be at different migration levels.
• Multiple tenant databases need the same migration package.
• The deployment pipeline does not know the exact current migration of every database.

Example command:

dotnet ef migrations script --idempotent -o migrations.sql

Idempotent scripts are convenient, but they should still be reviewed and tested. Custom SQL inside old migrations can sometimes cause provider-specific validation issues, especially if it references columns or tables that no longer exist. In important systems, some teams prefer generating targeted scripts from a known source migration to a known target migration.

Migration bundles

EF Core migration bundles package migrations into an executable.

Example:

dotnet ef migrations bundle

A bundle can be useful because:

• It can be generated during CI.
• It can be executed during deployment.
• It does not require the project source code on the production server.
• It can avoid installing the full EF toolchain on the target machine.

A bundle still needs operational controls. It should be versioned, tested, reviewed, and executed with appropriate database permissions.

Runtime migrations with MigrateAsync

EF Core allows applying migrations from application code:

using var scope = app.Services.CreateScope();

var db = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

await db.Database.MigrateAsync();

This can be useful for small internal tools, local demos, development environments, or simple single-instance deployments.

However, it is usually a poor production default because:

• The application needs schema modification permissions.
• Multiple application instances may attempt migration at startup.
• Users may access the database while schema changes are running.
• SQL is applied without a separate human review step.
• Failure during startup can take the whole application offline.
• Rollback control is weaker than a deliberate deployment script.

Newer EF Core versions provide more protection against concurrent migration execution, but that does not remove the broader production concerns around permissions, review, deployment timing, data safety, and rollback planning.

In interviews, a strong answer is usually:

MigrateAsync can be useful in development or controlled environments, but for production I prefer reviewed scripts or migration bundles executed as part of the deployment process, with backups, testing, monitoring, and rollback planning.

EnsureCreated vs migrations

EnsureCreated creates a database schema directly from the model without using migrations.

Example:

await db.Database.EnsureCreatedAsync();

This is not the same as migrations.

Important differences:

Do not mix EnsureCreated and Migrate for the same relational database lifecycle. If a database was created with EnsureCreated, migrations may not work correctly because the migration history table and migration metadata are missing.

Reviewing generated migrations

Every generated migration should be reviewed.

Look for:

• Dropped columns or tables.
• Renames incorrectly detected as drop-and-add.
• Unintended nullable changes.
• New required columns without defaults.
• Indexes that may be expensive to create.
• Foreign keys that may fail due to existing data.
• Seed data updates caused by dynamic values.
• Large data updates inside a migration.
• Provider-specific SQL that may not run in all environments.
• Missing Down logic where rollback is expected.

Example problem: EF Core may detect a property rename as dropping one column and adding another.

Generated migration:

migrationBuilder.DropColumn(
    name: "FullName",
    table: "Customers");

migrationBuilder.AddColumn<string>(
    name: "DisplayName",
    table: "Customers",
    type: "nvarchar(200)",
    maxLength: 200,
    nullable: false,
    defaultValue: "");

This could lose data.

A safer version may use a rename operation:

migrationBuilder.RenameColumn(
    name: "FullName",
    table: "Customers",
    newName: "DisplayName");

Interview point: EF Core cannot always know developer intent. Developers must review migrations before applying them.

Reviewing generated SQL scripts

Migration code is not enough. In production, the generated SQL should also be reviewed.

Generate SQL:

dotnet ef migrations script PreviousMigration NewMigration -o migration.sql

Review for:

• Table scans on large tables.
• Long-running locks.
• ALTER TABLE operations that rewrite data.
• Index creation impact.
• Foreign key validation impact.
• Data loss operations.
• Transaction behavior.
• Commands that cannot run inside a transaction.
• Provider-specific behavior.
• Whether the script matches the target environment's current schema.

A migration can look simple in C# but produce expensive SQL. Production review should focus on the actual SQL that will run.

Rollback strategy

Rollback is not just the Down method. A real rollback strategy answers:

• Can the schema change be reversed safely?
• Is data lost during rollback?
• Does the old application version still work with the new schema?
• Is a backup available?
• Has the rollback script been tested?
• How long will rollback take?
• Who is authorized to execute it?
• What monitoring will confirm success or failure?

EF Core can generate a rollback script by reversing the migration range:

dotnet ef migrations script NewMigration PreviousMigration -o rollback.sql

However, rollback scripts have limits.

If a migration drops a column, rollback may recreate the column but cannot restore lost data unless the data was preserved somewhere.

Example dangerous migration:

migrationBuilder.DropColumn(
    name: "LegacyCode",
    table: "Products");

The Down method can add the column back:

migrationBuilder.AddColumn<string>(
    name: "LegacyCode",
    table: "Products",
    type: "nvarchar(50)",
    nullable: true);

But the original values are gone unless they were backed up or copied elsewhere.

Backward-compatible migrations

A safe production migration should often be backward-compatible with both the old and new application versions.

This matters when using:

• Rolling deployments.
• Blue/green deployments.
• Canary releases.
• Multiple application instances.
• Microservices sharing a database.
• Long-running background workers.

A backward-compatible migration avoids breaking the currently running app.

Example safer approach for changing a required column:

1. Add the new column as nullable.
2. Deploy application code that writes both old and new columns.
3. Backfill existing rows.
4. Deploy code that reads from the new column.
5. Make the new column required.
6. Remove the old column in a later release.

This is often called the expand-and-contract pattern.

Expand-and-contract pattern

The expand-and-contract pattern separates risky schema changes into safe stages.

Example: Rename FullName to DisplayName.

Unsafe one-step migration:

migrationBuilder.DropColumn("FullName", "Customers");
migrationBuilder.AddColumn<string>("DisplayName", "Customers", nullable: false);

Safer staged approach:

Stage 1: Expand schema.

migrationBuilder.AddColumn<string>(
    name: "DisplayName",
    table: "Customers",
    type: "nvarchar(200)",
    maxLength: 200,
    nullable: true);

Stage 2: Backfill data.

UPDATE Customers
SET DisplayName = FullName
WHERE DisplayName IS NULL;

Stage 3: Deploy code that writes both fields or writes the new field.

Stage 4: Make the new field required after data is complete.

migrationBuilder.AlterColumn<string>(
    name: "DisplayName",
    table: "Customers",
    type: "nvarchar(200)",
    maxLength: 200,
    nullable: false,
    oldClrType: typeof(string),
    oldType: "nvarchar(200)",
    oldNullable: true);

Stage 5: Later, remove the old field after all code no longer depends on it.

This approach reduces deployment risk and makes rollback easier.

Data migrations

A data migration changes existing data. It may be required when a schema change cannot work without transforming old rows.

Example:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "NormalizedEmail",
        table: "Users",
        type: "nvarchar(320)",
        nullable: true);

    migrationBuilder.Sql("""
        UPDATE Users
        SET NormalizedEmail = UPPER(Email)
        WHERE Email IS NOT NULL AND NormalizedEmail IS NULL;
    """);
}

Data migrations need extra care because:

• They can be slow on large tables.
• They may lock rows or tables.
• They may fail due to unexpected data.
• They may need batching.
• They may need to be re-runnable.
• They may need monitoring.

For large systems, data backfills are often handled outside the schema migration using background jobs, ETL tasks, or manually reviewed scripts.

Large table migration risks

Schema changes on large tables can be expensive.

Risky operations include:

• Adding a non-nullable column with a default to a large table.
• Creating a large index during peak traffic.
• Altering column type or length.
• Dropping or recreating indexes.
• Adding foreign keys that validate existing data.
• Updating every row in a table.
• Rebuilding a clustered index.
• Changing primary keys.

Safer approaches include:

• Add nullable columns first.
• Backfill in batches.
• Create indexes during maintenance windows when needed.
• Use online index operations if the database supports them.
• Test against production-like data volume.
• Measure execution time and lock behavior.
• Monitor blocking and deadlocks during deployment.

Handling column renames

EF Core may not always infer a rename. It may generate a drop and add operation.

Unsafe generated code:

migrationBuilder.DropColumn(
    name: "Name",
    table: "Products");

migrationBuilder.AddColumn<string>(
    name: "ProductName",
    table: "Products",
    type: "nvarchar(200)",
    nullable: false,
    defaultValue: "");

Safer rename:

migrationBuilder.RenameColumn(
    name: "Name",
    table: "Products",
    newName: "ProductName");

If an index or constraint is tied to the old name, those may also need to be renamed or recreated carefully.

Handling required columns

Adding a required column to a table with existing rows can fail unless a default is provided.

Example:

migrationBuilder.AddColumn<DateTime>(
    name: "CreatedAtUtc",
    table: "Orders",
    type: "datetime2",
    nullable: false,
    defaultValueSql: "SYSUTCDATETIME()");

This may work, but it can still be risky on large tables depending on the database and operation.

A safer staged approach:

1. Add the column as nullable.
2. Backfill values.
3. Update application code to write the field.
4. Change the column to non-nullable.

Seed data and migration noise

EF Core seed data using HasData can generate migration updates when seed values change.

Example issue:

modelBuilder.Entity<Role>().HasData(new Role
{
    Id = 1,
    Name = "Admin",
    ConcurrencyStamp = Guid.NewGuid().ToString()
});

Because Guid.NewGuid() changes each time the model is built, EF Core may generate unnecessary UpdateData operations.

Better:

modelBuilder.Entity<Role>().HasData(new Role
{
    Id = 1,
    Name = "Admin",
    ConcurrencyStamp = "2e69d8f4-7c2a-4d91-9c4f-000000000001"
});

Use stable seed values in migrations. Avoid dynamic values such as DateTime.UtcNow or Guid.NewGuid() in model seed data.

Raw SQL in migrations

Sometimes EF Core migration operations are not enough. You can run raw SQL:

migrationBuilder.Sql("""
    CREATE VIEW dbo.ActiveCustomers AS
    SELECT Id, Name, Email
    FROM dbo.Customers
    WHERE IsActive = 1;
""");

Raw SQL is useful for:

• Views.
• Stored procedures.
• Triggers.
• Complex data transformations.
• Provider-specific features.
• Index options not directly supported by EF Core.

But raw SQL should be reviewed carefully because:

• It may be provider-specific.
• It may not be validated at compile time.
• It can break idempotent scripts if not written carefully.
• It may require separate Down logic.
• Some statements need to run in a separate batch.

Transaction behavior

EF Core generally attempts to apply migrations in transactions where possible. This helps avoid leaving the database partially migrated if something fails.

However, not all database operations can run inside a transaction on every provider.

Some operations may require transaction suppression:

migrationBuilder.Sql(
    sql: "CREATE INDEX IX_Orders_CreatedAtUtc ON Orders (CreatedAtUtc);",
    suppressTransaction: true);

Use this carefully. Suppressing transactions can leave the database partially changed if a later operation fails.

Production scripts should be tested against the target database engine because transaction behavior differs by provider.

Migration ordering and team workflow

In a team, multiple developers may create migrations at the same time.

Potential problems:

• Snapshot merge conflicts.
• Migration order conflicts.
• Duplicate schema changes.
• One branch modifies an entity that another branch also modifies.
• Migrations generated against stale model snapshots.
• A migration that works locally but fails after merging.

Good team habits:

• Keep migrations small and focused.
• Rebase or merge latest changes before generating migration.
• Resolve snapshot conflicts carefully.
• Build and run tests after merging migrations.
• Generate and inspect SQL after merge.
• Avoid editing old applied migrations.
• Create a new corrective migration instead of changing history after deployment.
• Test applying all migrations from a clean database.
• Test applying pending migrations from the previous released database.

Editing migrations

It is acceptable to edit a newly generated migration before it is applied anywhere.

Common edits include:

• Replacing drop/add with rename.
• Adding custom SQL for data backfill.
• Fixing default values.
• Improving index definitions.
• Adjusting constraint names.
• Adding safer rollback logic.

However, once a migration has been applied to shared environments or production, editing it is dangerous. Other databases may already have recorded the migration in __EFMigrationsHistory.

In most cases, after a migration has been shared or applied, create a new migration to correct the issue.

Removing migrations

If the latest migration has not been applied to a database, it can be removed:

dotnet ef migrations remove

This removes the last migration and reverts the snapshot.

If the migration has already been applied, first roll back the database to the previous migration, then remove it locally.

Example local rollback:

dotnet ef database update PreviousMigration
dotnet ef migrations remove

Do not remove or rewrite applied production migrations casually.

Production migration checklist

Before production deployment, check:

• The migration builds successfully.
• The generated migration code was reviewed.
• The generated SQL script was reviewed.
• The migration was tested on a production-like database.
• The current production migration state is known.
• Backup or restore strategy is ready.
• Rollback script or recovery process is ready.
• Long-running operations were identified.
• Locking and blocking impact was considered.
• Data loss operations were explicitly approved.
• Backward compatibility was considered.
• Application deployment order is clear.
• Monitoring and alerts are ready.
• Database permissions are appropriate.
• Deployment owner and rollback owner are known.

Deployment strategies

Common migration deployment strategies include:

Least privilege for migrations

The application account should usually not have permission to change production schema.

A safer separation:

• Application user: read/write application data only.
• Migration/deployment user: schema modification permissions.
• DBA/admin user: emergency and administrative operations.

This supports least privilege. If the application is compromised, the attacker should not automatically be able to drop tables or alter schema.

Migrations and CI/CD

A mature CI/CD workflow may include:

1. Build application.
2. Run unit tests.
3. Run integration tests.
4. Create a test database from scratch and apply all migrations.
5. Restore a copy of the previous release database and apply pending migrations.
6. Generate SQL script or migration bundle.
7. Publish migration artifact.
8. Require review or approval.
9. Apply migration in staging.
10. Run smoke tests.
11. Apply migration in production.
12. Monitor application and database health.

Example script generation in a build step:

dotnet ef migrations script PreviousReleaseMigration LatestMigration \
  --project src/MyApp.Infrastructure \
  --startup-project src/MyApp.Api \
  --output artifacts/database/migration.sql

Example idempotent script:

dotnet ef migrations script \
  --idempotent \
  --project src/MyApp.Infrastructure \
  --startup-project src/MyApp.Api \
  --output artifacts/database/migration-idempotent.sql

Testing migrations

Migration tests can catch issues before production.

Useful tests include:

• Create a blank database and apply all migrations.
• Apply pending migrations to a database at the previous release version.
• Verify expected tables, columns, indexes, and constraints exist.
• Verify data backfills work.
• Verify old and new application versions can tolerate staged changes.
• Verify rollback script where rollback is part of the release plan.

Example integration test idea:

[Fact]
public async Task AllMigrations_ShouldApplySuccessfully()
{
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseSqlServer(TestConnectionString)
        .Options;

    await using var db = new AppDbContext(options);

    await db.Database.EnsureDeletedAsync();
    await db.Database.MigrateAsync();

    var pending = await db.Database.GetPendingMigrationsAsync();

    Assert.Empty(pending);
}

For realistic migration testing, prefer the same database provider used in production. EF Core InMemory is not appropriate for testing relational migration behavior.

Common mistakes

Common mistakes include:

• Running unreviewed migrations in production.
• Applying migrations automatically at app startup without considering concurrency and permissions.
• Adding non-nullable columns to large tables without a safe plan.
• Letting EF Core convert a rename into drop-and-add.
• Dropping columns in the same release where old code may still need them.
• Using dynamic values in HasData.
• Ignoring the Down method.
• Assuming rollback can restore deleted data.
• Not testing against production-like data volume.
• Giving the application user schema modification permissions.
• Mixing EnsureCreated and migrations.
• Editing migrations after they have been applied to shared databases.
• Treating migrations as purely application code instead of deployment artifacts.

Best practices

Good EF Core migration habits:

• Use descriptive migration names.
• Keep migrations small and focused.
• Review migration code.
• Review generated SQL.
• Test migrations before production.
• Generate SQL scripts for production review.
• Use idempotent scripts when target database state may vary.
• Use migration bundles when they fit your deployment model.
• Avoid runtime migrations for important production systems.
• Use expand-and-contract for breaking changes.
• Back up production before risky migrations.
• Plan rollback before deployment.
• Separate application permissions from migration permissions.
• Avoid destructive changes until a later release.
• Monitor database health during migration.
• Document migration assumptions in the release notes.
• Prefer provider-realistic integration tests.