Entity Framework Core Migrations Guide

How Migrations Work (Technical Overview)

Migration System Architecture:

• EF Core compares your current model (from DbContext) with the snapshot (*ModelSnapshot.cs)
• Differences generate migration operations via MigrationBuilder
• Each migration is a class inheriting from Migration with Up() and Down() methods
• Applied migrations are tracked in __EFMigrationsHistory table in your database
• The snapshot file is the cumulative result of all migrations applied in sequence

Migration History Table: EF Core creates __EFMigrationsHistory table automatically. It stores:

• MigrationId: Timestamp + name (e.g., 20251109125819_AddProductTable)
• ProductVersion: EF Core version that created the migration

Change Detection Process:

1. EF Core loads current model from DbContext.OnModelCreating()
2. Compares with *ModelSnapshot.cs using model metadata
3. Generates operations: CreateTable, AddColumn, AlterColumn, etc.
4. Creates new migration class with these operations
5. Updates snapshot to match new model state

Migration Execution:

• Up() method runs when applying: dotnet ef database update
• Down() method runs when rolling back: dotnet ef database update PreviousMigration
• Each migration runs in a transaction (database-dependent)
• If any step fails, entire migration rolls back

Quick Reference

Create Migration

dotnet ef migrations add MigrationName --project YourProject --startup-project YourProject

Apply Migrations

dotnet ef database update --project YourProject --startup-project YourProject

Remove Last Migration

dotnet ef migrations remove --project YourProject --startup-project YourProject

List Pending Migrations

dotnet ef migrations list --project YourProject --startup-project YourProject

---

Common Scenarios

1. Adding a New Table

Step 1: Create model

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Step 2: Add to DbContext

public DbSet<Product> Products { get; set; }

Step 3: Generate migration

dotnet ef migrations add AddProductTable

Step 4: Review generated migration

public partial class AddProductTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Products",
            columns: table => new
            {
                Id = table.Column<int>(type: "integer", nullable: false),
                Name = table.Column<string>(type: "text", nullable: false),
                Price = table.Column<decimal>(type: "numeric", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "Products");
    }
}

Technical Notes:

• MigrationBuilder is database-agnostic; provider-specific SQL is generated at runtime
• Column types ("integer", "text", "numeric") are provider-specific (PostgreSQL in this case)
• Primary key constraint name PK_Products follows EF Core naming convention: PK_{TableName}
• partial class allows EF Core to add generated code in .Designer.cs file

Step 5: Apply

dotnet ef database update

---

2. Adding a Column

Model change:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public string Description { get; set; } // New column
}

Generate migration:

dotnet ef migrations add AddDescriptionToProduct

Generated migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "Description",
        table: "Products",
        type: "text",
        nullable: true);
}

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

---

3. Seeding Data

In migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.InsertData(
        table: "Products",
        columns: new[] { "Id", "Name", "Price" },
        values: new object[] { 1, "Widget", 19.99m });
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DeleteData(
        table: "Products",
        keyColumn: "Id",
        keyValue: 1);
}

Or in DbContext OnModelCreating:

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Product>().HasData(
        new Product { Id = 1, Name = "Widget", Price = 19.99m }
    );
}

---

4. Updating Existing Data

Migration example:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.UpdateData(
        table: "Products",
        keyColumn: "Id",
        keyValue: 1,
        column: "Price",
        value: 24.99m);
}

⚠️ Common Pitfall – Connection String Format:

// ❌ WRONG - Missing semicolon
value: "Host=postgres Port=5432; Database=mydb; Username=user; Password=pass");

// ✅ CORRECT
value: "Host=postgres; Port=5432; Database=mydb; Username=user; Password=pass");

Why This Fails:

• Npgsql connection string parser uses semicolons (;) as delimiters
• Without semicolon, parser treats "postgres Port=5432" as single hostname value
• DNS resolution fails: System.Net.Sockets.SocketException: Name or service not known
• Connection string format: Key=Value; Key=Value; ... (semicolon-separated key-value pairs)
• PostgreSQL format also supports spaces, but semicolon is required delimiter

---

5. Renaming Column

Option 1: Use migration

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.RenameColumn(
        name: "OldName",
        table: "Products",
        newName: "NewName");
}

Option 2: Use attribute (EF Core 5+)

[Column("NewName")]
public string OldName { get; set; }

---

6. Adding Index

In model:

[Index(nameof(Name))]
public class Product { }

Or in migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateIndex(
        name: "IX_Products_Name",
        table: "Products",
        column: "Name");
}

---

Snapshot Management

What is a Snapshot?

The *ModelSnapshot.cs file represents the current state of your model. EF Core uses it to detect changes.

Location: Migrations/YourContextModelSnapshot.cs

Technical Details:

• Snapshot is a C# class containing IModel metadata serialized as code
• Contains entity configurations, relationships, indexes, constraints
• Generated by IMigrationsModelDiffer comparing model to previous snapshot
• Uses IMigrationsCodeGenerator to produce C# code representation
• Acts as “source of truth” for what database should look like after all migrations

When Snapshot Updates

• Automatically updated when you create a migration
• Represents the “target state” after all migrations
• Updated by dotnet ef migrations add command
• Never manually edit – EF Core regenerates it

Snapshot Structure:

[DbContext(typeof(AppDbContext))]
partial class AppDbContextModelSnapshot : ModelSnapshot
{
    protected override void BuildModel(ModelBuilder modelBuilder)
    {
        // Model configuration as code
        modelBuilder.Entity<Product>(entity =>
        {
            entity.ToTable("Products");
            entity.Property(e => e.Id).ValueGeneratedOnAdd();
            // ... all entity configurations
        });
    }
}

How Change Detection Works:

1. EF Core builds current model from DbContext
2. Loads previous snapshot (if exists)
3. IMigrationsModelDiffer.Diff() compares both models
4. Returns MigrationOperation[] for differences
5. Generates migration class with these operations
6. Updates snapshot to match current model

Manual Snapshot Fix

If snapshot gets out of sync:

# Remove last migration
dotnet ef migrations remove

# Recreate it
dotnet ef migrations add MigrationName

---

Multiple DbContexts

Separate Migration Folders

# Context 1
dotnet ef migrations add MigrationName \
  --context AppDbContext \
  --output-dir Migrations/App

# Context 2
dotnet ef migrations add MigrationName \
  --context AuthDbContext \
  --output-dir Migrations/Auth

Apply Specific Context

dotnet ef database update --context AppDbContext

---

Troubleshooting

Migration Already Applied

Error: Migration already applied

Technical Explanation:

• EF Core checks __EFMigrationsHistory table before applying
• If migration ID exists, it skips execution
• This prevents duplicate application but can cause issues if history is out of sync

Fix:

# Check applied migrations
dotnet ef migrations list

# Mark as applied without running
dotnet ef database update MigrationName --connection "your-connection-string"

Manual History Fix (Advanced):

-- Check migration history
SELECT * FROM "__EFMigrationsHistory";

-- Remove specific migration from history (if needed)
DELETE FROM "__EFMigrationsHistory" 
WHERE "MigrationId" = '20251109125819_AddProductTable';

-- Re-apply migration
dotnet ef database update

Model Snapshot Out of Sync

Symptoms: EF thinks model changed but it hasn’t

Root Causes:

• Snapshot file manually edited or corrupted
• Model changes reverted but snapshot not updated
• Merge conflicts in snapshot file
• Multiple developers working on migrations simultaneously

Technical Details:

• Snapshot stores model as IModel metadata
• EF Core compares DbContext model vs snapshot model
• Any difference triggers new migration generation
• Snapshot must exactly match last applied migration’s target state

Fix:

# Remove problematic migration
dotnet ef migrations remove

# Recreate
dotnet ef migrations add MigrationName

Verify Snapshot Integrity:

# Check if snapshot matches current model
dotnet ef migrations add TestMigration --dry-run

# If no changes detected, snapshot is in sync

Connection String in Migration

Problem: Hardcoded connection strings in migrations

Solution: Use configuration, not hardcoded values:

// ❌ BAD
value: "Host=localhost; Database=mydb; Username=user; Password=secret");

// ✅ GOOD - Use configuration
var connectionString = _configuration.GetConnectionString("DefaultConnection");

Rollback Migration

# Rollback to specific migration
dotnet ef database update PreviousMigrationName

# Rollback all
dotnet ef database update 0

---

Best Practices

1. Review Generated Migrations – Always check before applying
2. Test Down() Method – Ensure rollback works
3. Use Meaningful Names – AddUserTable not Migration1
4. One Change Per Migration – Easier to debug and rollback
5. Never Edit Applied Migrations – Create new one instead
6. Backup Before Major Migrations – Especially data migrations
7. Use Transactions – Wrap data migrations in transactions

Migration Naming Convention

Add{Entity}Table
Add{Property}To{Entity}
Remove{Property}From{Entity}
Update{Entity}Data
Rename{Entity}To{NewName}

---

Programmatic Migration Application

In Startup/Program.cs

using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    context.Database.Migrate();
}

Technical Notes:

• Migrate() is synchronous and blocks thread – use async version in production
• Automatically checks __EFMigrationsHistory for applied migrations
• Applies only pending migrations in order
• Each migration runs in separate transaction (provider-dependent)
• Throws exception if any migration fails – application won’t start

Check Pending Migrations

var pendingMigrations = await context.Database.GetPendingMigrationsAsync();
if (pendingMigrations.Any())
{
    await context.Database.MigrateAsync();
}

How Pending Detection Works:

1. GetPendingMigrationsAsync() queries __EFMigrationsHistory table
2. Compares with migration files in Migrations/ folder
3. Returns migration IDs not in history table
4. Migrations applied in chronological order (by timestamp prefix)

Production Considerations:

• Migrations run during application startup – adds startup time
• Consider running migrations separately via CI/CD pipeline
• Use GetPendingMigrationsAsync() to log which migrations will be applied
• Wrap in try-catch to handle migration failures gracefully

---

Common Migration Patterns

Conditional Migration

protected override void Up(MigrationBuilder migrationBuilder)
{
    if (migrationBuilder.IsNpgsql())
    {
        migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\"");
    }
}

Technical Details:

• MigrationBuilder provides provider-specific extension methods
• IsNpgsql(), IsSqlServer(), IsSqlite() check active provider
• Allows database-specific SQL in migrations
• Provider detection happens at runtime, not compile time
• Use for provider-specific features: extensions, functions, data types

Data Migration with SQL

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        UPDATE Products 
        SET Price = Price * 1.1 
        WHERE Category = 'Electronics'
    ");
}

Technical Considerations:

• Sql() executes raw SQL directly against database
• Not provider-agnostic – SQL must match your database type
• Runs within migration transaction (if supported by provider)
• For large data migrations, consider batching:

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Batch processing for large datasets
    migrationBuilder.Sql(@"
        UPDATE Products 
        SET Price = Price * 1.1 
        WHERE Category = 'Electronics' 
        AND Id IN (
            SELECT Id FROM Products 
            WHERE Category = 'Electronics' 
            LIMIT 1000
        )
    ");
}

• Use suppressTransaction: true parameter if SQL doesn’t support transactions

Add Column with Default

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<bool>(
        name: "IsActive",
        table: "Products",
        type: "boolean",
        nullable: false,
        defaultValue: true);
}

Technical Notes:

• defaultValue sets database-level default constraint
• Existing rows get default value automatically
• New rows without explicit value use default
• For nullable columns, use defaultValue: null or omit parameter
• Default values are database-specific (e.g., true in PostgreSQL, 1 in SQL Server)
• Use defaultValueSql for SQL expressions: defaultValueSql: "NOW()"

---

Quick Checklist

Before applying migration:

•  Review generated code
•  Test on development database
•  Backup production database
•  Verify Down() method works
•  Check connection strings are correct
•  Test rollback procedure

---

Useful Commands Reference

# Create migration
dotnet ef migrations add Name

# Apply migrations
dotnet ef database update

# Remove last migration
dotnet ef migrations remove

# List migrations
dotnet ef migrations list

# Generate SQL script
dotnet ef migrations script

# Generate SQL for specific range
dotnet ef migrations script FromMigration ToMigration

# Drop database
dotnet ef database drop

# Scaffold from existing database
dotnet ef dbcontext scaffold "connection-string" Npgsql.EntityFrameworkCore.PostgreSQL

---

Example: Complete Workflow

# 1. Make model changes
# Edit Product.cs

# 2. Create migration
dotnet ef migrations add AddCategoryToProduct

# 3. Review generated file
# Check Migrations/YYYYMMDDHHMMSS_AddCategoryToProduct.cs

# 4. Apply to dev
dotnet ef database update

# 5. Test application

# 6. Apply to production
dotnet ef database update --connection "prod-connection-string"

---

Advanced Technical Topics

Migration Ordering and Dependencies

How Ordering Works:

• Migrations ordered by timestamp prefix: YYYYMMDDHHMMSS_MigrationName
• EF Core applies migrations sequentially in chronological order
• Each migration depends on previous migration’s state
• Cannot skip migrations – must apply in sequence

Dependency Chain:

Migration1 (20240101000000) → Migration2 (20240102000000) → Migration3 (20240103000000)

Each migration assumes previous migrations have been applied.

Transaction Handling

Provider Behavior:

• SQL Server: Each migration runs in separate transaction
• PostgreSQL: Each migration runs in separate transaction
• SQLite: Limited transaction support – some operations auto-commit

Manual Transaction Control:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql("BEGIN TRANSACTION");
    try
    {
        migrationBuilder.AddColumn<string>(name: "NewColumn", table: "Products");
        migrationBuilder.Sql("COMMIT");
    }
    catch
    {
        migrationBuilder.Sql("ROLLBACK");
        throw;
    }
}

Performance Considerations

Large Table Migrations:

• Adding NOT NULL column to large table requires full table scan
• Consider: Add nullable column → Update data → Make NOT NULL (separate migrations)
• Index creation on large tables can lock table
• Use CONCURRENTLY in PostgreSQL for non-blocking index creation

Migration Execution Time:

• Migrations run synchronously during application startup
• Long-running migrations block application start
• Consider running migrations via external script for production

Migration Builder API

Core Operations:

• CreateTable() / DropTable() – Table operations
• AddColumn() / DropColumn() / AlterColumn() – Column operations
• CreateIndex() / DropIndex() – Index operations
• AddForeignKey() / DropForeignKey() – Relationship operations
• Sql() – Raw SQL execution
• InsertData() / UpdateData() / DeleteData() – Data operations

Operation Ordering:

• Operations execute in order they’re added to builder
• Dependencies must be created before use (e.g., table before column)
• EF Core validates operation order during migration generation

Connection String Parsing

Npgsql Format:

Host=hostname; Port=5432; Database=dbname; Username=user; Password=pass

• Semicolon (;) is required delimiter
• Space after semicolon is optional
• Keys are case-insensitive
• Values with special characters should be quoted

Common Parsing Issues:

• Missing semicolon: Host=postgres Port=5432 → Treated as single key-value
• Special characters in values: Use Password='p@ssw0rd' or escape
• Connection pooling: Pooling=true; MinPoolSize=5; MaxPoolSize=20

Notes

• Migrations are immutable once applied to production
• Always test Down() method before deploying
• Use meaningful migration names for easier debugging
• Keep migrations small and focused
• Document complex data migrations in comments
• Migration files are source code – commit to version control
• Never delete migration files that have been applied to production