Migrations Deep Dive
Overview
Code-First migrations allow you to evolve your database schema as your model changes, maintain version history, and deploy schema changes to production safely.
Migration Basics
Creating Migrations
# Add a new migration
dotnet ef migrations add InitialCreate
# Add migration with specific context
dotnet ef migrations add AddProducts --context AppDbContext
# Add migration to specific project
dotnet ef migrations add AddUsers --project Data --startup-project WebApi
Applying Migrations
# Apply all pending migrations
dotnet ef database update
# Apply specific migration
dotnet ef database update AddProducts
# Rollback to previous migration
dotnet ef database update PreviousMigration
# Rollback all migrations
dotnet ef database update 0
Removing Migrations
# Remove last migration (if not applied)
dotnet ef migrations remove
# Force remove (dangerous!)
dotnet ef migrations remove --force
Migration Files
// 20240315120000_InitialCreate.cs
public partial class InitialCreate : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Products",
columns: table => new
{
Id = table.Column<int>(nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(maxLength: 100, nullable: false),
Price = table.Column<decimal>(type: "decimal(18,2)", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Products", x => x.Id);
});
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(name: "Products");
}
}
Seed Data
// In DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Category>().HasData(
new Category { Id = 1, Name = "Electronics" },
new Category { Id = 2, Name = "Books" },
new Category { Id = 3, Name = "Clothing" }
);
modelBuilder.Entity<Product>().HasData(
new Product { Id = 1, Name = "Laptop", Price = 999.99m, CategoryId = 1 },
new Product { Id = 2, Name = "Mouse", Price = 29.99m, CategoryId = 1 }
);
}
// Generate migration
// dotnet ef migrations add SeedData
Custom SQL in Migrations
public partial class AddStoredProcedure : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"
CREATE PROCEDURE GetProductsByCategory
@CategoryId INT
AS
BEGIN
SELECT * FROM Products WHERE CategoryId = @CategoryId
END
");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("DROP PROCEDURE GetProductsByCategory");
}
}
Migration Scripts
# Generate SQL script for all migrations
dotnet ef migrations script
# Generate script from specific migration
dotnet ef migrations script AddProducts
# Generate script between two migrations
dotnet ef migrations script AddProducts AddOrders
# Generate idempotent script (can run multiple times)
dotnet ef migrations script --idempotent
# Output to file
dotnet ef migrations script --output migration.sql
Database Initialization
EnsureCreated vs Migrate
// ❌ EnsureCreated - Creates DB but no migrations history
await context.Database.EnsureCreatedAsync();
// Use only for: testing, prototyping, in-memory databases
// ✅ Migrate - Applies pending migrations
await context.Database.MigrateAsync();
// Use for: production, maintaining migration history
// Check if database exists
bool exists = await context.Database.CanConnectAsync();
// Delete database (testing only!)
await context.Database.EnsureDeletedAsync();
Migration History
// View applied migrations
var applied = await context.Database
.GetAppliedMigrationsAsync();
// View pending migrations
var pending = await context.Database
.GetPendingMigrationsAsync();
// Check if migrations table exists
bool hasTable = context.Database
.GetService<IHistoryRepository>()
.Exists();
Handling Migration Conflicts
Scenario: Two developers create migrations
# Developer A creates AddProductCategory
dotnet ef migrations add AddProductCategory
# Developer B creates AddSupplier (based on old code)
dotnet ef migrations add AddSupplier
# Conflict! Timestamps out of order
# Solution: Remove B's migration, merge A's changes, recreate
dotnet ef migrations remove
git pull
dotnet ef migrations add AddSupplier
Production Best Practices
Generate Scripts
// Startup.cs - DON'T auto-migrate in production
public void Configure(IApplicationBuilder app)
{
// ❌ NEVER in production
// using (var scope = app.ApplicationServices.CreateScope())
// {
// var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
// context.Database.Migrate(); // Dangerous!
// }
// ✅ Use SQL scripts instead
// Run: dotnet ef migrations script --idempotent --output deploy.sql
// Then execute deploy.sql in production
}
Rolling Updates
// 1. Ensure migrations are backward compatible
// Add column with default value
migrationBuilder.AddColumn<bool>(
name: "IsActive",
table: "Products",
nullable: false,
defaultValue: true); // Important!
// 2. Don't remove columns immediately
// Step 1: Stop using column in code
// Step 2: Deploy code
// Step 3: Remove column in next migration
// 3. Rename carefully
// Instead of renaming directly, do:
// a) Add new column
// b) Copy data
// c) Remove old column (later)
Interview Questions
Q: What's the difference between EnsureCreated and Migrate?
A: EnsureCreated creates the database from scratch without migration history. It's for testing only. Migrate applies migrations sequentially and maintains history. Always use Migrate in production.
Q: How do you handle production migrations?
A: Generate idempotent SQL scripts with dotnet ef migrations script --idempotent, review them, test in staging, then run in production. Never auto-migrate in production code.
Q: What if two developers create migrations simultaneously?
A: Migration timestamps will conflict. The second developer should remove their migration, pull the first developer's changes, then recreate their migration on top.
Q: How do you rollback a migration?
A: Use dotnet ef database update PreviousMigrationName. This runs the Down() method. For production, generate a rollback script first.