Exercise 2: Migration Management

Objective

Practice creating, modifying, and managing migrations in a realistic development workflow.

Initial Setup

Start with a simple Product entity:

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

Tasks

Task 1: Initial Migration

1. Create the initial migration:

dotnet ef migrations add InitialCreate

2. Apply to database:

dotnet ef database update

3. Verify the database was created

Task 2: Add Category Relationship

Modify entities:

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

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Product> Products { get; set; }
}

1. Create migration:

dotnet ef migrations add AddCategory

2. Review the generated migration file
3. Apply migration
4. Rollback to previous migration:

dotnet ef database update InitialCreate

5. Reapply:

dotnet ef database update

Task 3: Add Seed Data

Add seed data in OnModelCreating:

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 },
    new Product { Id = 3, Name = "C# Book", Price = 49.99m, CategoryId = 2 }
);

1. Create migration:

dotnet ef migrations add SeedData

2. Apply and verify seed data exists

Task 4: Add Stored Procedure

Create a migration that adds a stored procedure:

public partial class AddGetProductsByCategory : 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 IF EXISTS GetProductsByCategory");
    }
}

Task 5: Rename Column (Safely)

Rename Price to UnitPrice without losing data:

1. Add new column with migration
2. Copy data from old to new column with SQL
3. Remove old column in separate migration

Create the migrations to accomplish this.

Task 6: Generate SQL Scripts

# Generate script for all migrations
dotnet ef migrations script --output migrations.sql

# Generate idempotent script (safe to run multiple times)
dotnet ef migrations script --idempotent --output deploy.sql

# Generate script between two migrations
dotnet ef migrations script AddCategory AddGetProductsByCategory --output partial.sql

Review the generated SQL files.

Task 7: Handle Migration Conflict

Simulate a merge conflict:

1. Create branch A and add migration AddDescription
2. Switch to branch B and add migration AddStock
3. Merge and resolve the migration timestamp conflict

Task 8: Production Deployment

1. Generate production deployment script
2. Review script for:
   • Idempotent operations
   • Transaction handling
   • Rollback capability
3. Test in staging environment

Validation Checklist

• All migrations created successfully
• Can rollback and reapply migrations
• Seed data inserted correctly
• Stored procedure works
• Column rename preserved data
• SQL scripts are idempotent
• Migration conflicts resolved

Common Issues

Issue: "The model backing the context has changed" Solution: Create a new migration

Issue: Migration already applied Solution: Use dotnet ef migrations remove if not applied, or create a new reverting migration

Issue: Timestamp conflicts Solution: Remove one migration, pull changes, recreate

Best Practices Learned

1. Never modify applied migrations
2. Always review generated SQL before applying
3. Use idempotent scripts for production
4. Test rollback capability
5. Keep migrations small and focused
6. Don't mix schema and data changes
7. Use transactions for complex changes

Bonus Challenges

1. Create a migration that adds an index only if it doesn't exist
2. Implement a migration that migrates data between tables
3. Create a custom migration operation
4. Set up automatic migration on application startup (development only)
5. Create a migration that modifies existing data based on business rules