C# Migrations

Introduction to Database Migrations

When developing applications that use databases, one of the most challenging aspects is managing how your database schema evolves over time. As your application grows, you'll need to add tables, modify columns, create relationships, and more. Database migrations provide a structured way to make these changes while keeping track of the database's state.

In the C# ecosystem, migrations are primarily associated with Entity Framework (EF) Core and Entity Framework 6, Microsoft's object-relational mapping (ORM) frameworks. Migrations allow you to:

Apply database schema changes in a controlled manner
Version control your database schema alongside your code
Create a reproducible database setup process
Roll back changes if needed
Collaborate effectively in a team environment

Understanding Migrations in Entity Framework

Entity Framework Core and EF6 both provide powerful migration capabilities, but we'll focus primarily on EF Core in this guide since it's the modern approach.

What Are Migrations?

Migrations are a way to incrementally update your database schema to keep it in sync with your application's data model while preserving existing data. Each migration describes a set of changes to be applied to the database (like creating a table, adding a column, etc.).

Getting Started with Migrations

Prerequisites

To follow along with this tutorial, you'll need:

Visual Studio or Visual Studio Code
.NET Core SDK 3.1 or later
Entity Framework Core packages installed in your project

Setting Up Your Project for Migrations

First, let's set up a simple project with Entity Framework Core:

csharp
// Install these packages using NuGet:
// Microsoft.EntityFrameworkCore
// Microsoft.EntityFrameworkCore.SqlServer (or your preferred database provider)
// Microsoft.EntityFrameworkCore.Tools

// Sample model class
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime EnrollmentDate { get; set; }
}

// DbContext class
public class SchoolContext : DbContext
{
    public SchoolContext(DbContextOptions<SchoolContext> options)
        : base(options)
    {
    }

    public DbSet<Student> Students { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=SchoolDb;Trusted_Connection=True;");
        }
    }
}

Creating Your Initial Migration

To create your first migration, you can use the EF Core command-line tools or the Package Manager Console in Visual Studio:

Using Package Manager Console:

Add-Migration InitialCreate

Using .NET CLI:

dotnet ef migrations add InitialCreate

This command generates migration files that contain the code needed to create your database schema:

csharp
// Generated migration class (simplified)
public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Students",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(nullable: true),
                EnrollmentDate = table.Column<DateTime>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Students", x => x.Id);
            });
    }

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

Applying Migrations to the Database

After creating a migration, you need to apply it to actually update your database:

Using Package Manager Console:

Update-Database

Using .NET CLI:

dotnet ef database update

This command executes the Up method of your migration class, which creates the database tables according to your model.

Making Schema Changes with Migrations

Let's say we want to add a new field to our Student model to track the student's grade point average (GPA):

csharp
public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime EnrollmentDate { get; set; }
    public decimal GPA { get; set; } // New field
}

To reflect this change in the database, create a new migration:

Add-Migration AddStudentGPA

Or:

dotnet ef migrations add AddStudentGPA

This generates a new migration file:

csharp
public partial class AddStudentGPA : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<decimal>(
            name: "GPA",
            table: "Students",
            nullable: false,
            defaultValue: 0m);
    }

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

Apply this migration to update the database:

Update-Database

Or:

dotnet ef database update

Advanced Migration Concepts

Customizing Migrations

Sometimes you might need to customize the generated migration code to handle more complex scenarios:

csharp
public partial class CustomizeMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Auto-generated code
        migrationBuilder.AddColumn<string>(
            name: "Address",
            table: "Students",
            nullable: true);
            
        // Custom SQL for more complex operations
        migrationBuilder.Sql("UPDATE Students SET Address = 'Unknown' WHERE Address IS NULL");
        
        // Create a stored procedure
        migrationBuilder.Sql(@"
            CREATE PROCEDURE GetStudentsByEnrollmentDate
                @EnrollmentDate datetime
            AS
                SELECT * FROM Students WHERE EnrollmentDate >= @EnrollmentDate
            GO");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Revert custom SQL
        migrationBuilder.Sql("DROP PROCEDURE GetStudentsByEnrollmentDate");
        
        // Auto-generated code
        migrationBuilder.DropColumn(
            name: "Address",
            table: "Students");
    }
}

Managing Migrations in Different Environments

When working with different environments (development, staging, production), you'll need to handle migrations carefully:

csharp
// Program.cs or Startup.cs
public static void Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();
    
    // Apply migrations at application startup (good for development)
    using (var scope = host.Services.CreateScope())
    {
        var services = scope.ServiceProvider;
        try
        {
            var context = services.GetRequiredService<SchoolContext>();
            context.Database.Migrate(); // Applies any pending migrations
            
            // You could also seed data here if needed
            DbInitializer.Initialize(context);
        }
        catch (Exception ex)
        {
            var logger = services.GetRequiredService<ILogger<Program>>();
            logger.LogError(ex, "An error occurred while migrating or seeding the database.");
        }
    }
    
    host.Run();
}

Working with Multiple DbContexts

For larger applications, you might have multiple DbContexts. You need to specify which context to use when creating migrations:

Add-Migration InitialCreate -Context OrderContext

Or:

dotnet ef migrations add InitialCreate --context OrderContext

Real-World Example: Online Store Database Evolution

Let's consider a more complete example of how migrations would work in a real-world scenario for an e-commerce application:

Initial Setup

csharp
// Initial models
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public string CustomerName { get; set; }
    
    public List<OrderItem> Items { get; set; }
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    
    public Order Order { get; set; }
    public Product Product { get; set; }
}

// DbContext
public class StoreContext : DbContext
{
    public StoreContext(DbContextOptions<StoreContext> options) : base(options) { }
    
    public DbSet<Product> Products { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<OrderItem>()
            .HasOne(oi => oi.Order)
            .WithMany(o => o.Items)
            .HasForeignKey(oi => oi.OrderId);
            
        modelBuilder.Entity<OrderItem>()
            .HasOne(oi => oi.Product)
            .WithMany()
            .HasForeignKey(oi => oi.ProductId);
    }
}

Create initial migration:

Add-Migration InitialStoreSchema
Update-Database

First Evolution: Adding Product Categories

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

// Updated Product model
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int? CategoryId { get; set; } // New field, nullable for existing products
    
    public Category Category { get; set; }
}

// Updated DbContext
public class StoreContext : DbContext
{
    // Existing properties...
    public DbSet<Category> Categories { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Existing configuration...
        
        modelBuilder.Entity<Product>()
            .HasOne(p => p.Category)
            .WithMany(c => c.Products)
            .HasForeignKey(p => p.CategoryId);
    }
}

Create and apply the migration:

Add-Migration AddProductCategories
Update-Database

Second Evolution: Customer Accounts

csharp
// New model
public class Customer
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string Name { get; set; }
    
    public List<Order> Orders { get; set; }
}

// Updated Order model
public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public int CustomerId { get; set; }
    // Remove CustomerName as it's now in the Customer entity
    
    public Customer Customer { get; set; }
    public List<OrderItem> Items { get; set; }
}

For this change, we need a more complex migration that moves data from the old column to the new related table:

csharp
public partial class AddCustomerAccounts : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Create the new Customers table
        migrationBuilder.CreateTable(
            name: "Customers",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Email = table.Column<string>(nullable: false),
                Name = table.Column<string>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Customers", x => x.Id);
            });
            
        // Add CustomerId column to Orders
        migrationBuilder.AddColumn<int>(
            name: "CustomerId",
            table: "Orders",
            nullable: true);
            
        // Custom SQL to migrate data
        migrationBuilder.Sql(@"
            -- Insert customers based on distinct names in Orders
            INSERT INTO Customers (Name, Email)
            SELECT DISTINCT CustomerName, '[email protected]'
            FROM Orders;

            -- Update Orders with the corresponding CustomerId
            UPDATE o
            SET CustomerId = c.Id
            FROM Orders o
            JOIN Customers c ON o.CustomerName = c.Name;
        ");
        
        // Make CustomerId required after data migration
        migrationBuilder.AlterColumn<int>(
            name: "CustomerId",
            table: "Orders",
            nullable: false);
            
        // Drop the old CustomerName column
        migrationBuilder.DropColumn(
            name: "CustomerName",
            table: "Orders");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Add CustomerName column back
        migrationBuilder.AddColumn<string>(
            name: "CustomerName",
            table: "Orders",
            nullable: true);
            
        // Migrate data back
        migrationBuilder.Sql(@"
            UPDATE o
            SET CustomerName = c.Name
            FROM Orders o
            JOIN Customers c ON o.CustomerId = c.Id;
        ");
        
        // Make CustomerName required
        migrationBuilder.AlterColumn<string>(
            name: "CustomerName",
            table: "Orders",
            nullable: false);
            
        // Drop the CustomerId column
        migrationBuilder.DropColumn(
            name: "CustomerId",
            table: "Orders");
            
        // Drop the Customers table
        migrationBuilder.DropTable(
            name: "Customers");
    }
}

Apply this complex migration:

Add-Migration AddCustomerAccounts
Update-Database

Best Practices for Migrations

Create small, focused migrations: Smaller migrations are easier to understand, test, and roll back if needed.
Always test migrations: Before applying to production, test migrations in development and staging environments.
Include both Up and Down methods: Ensure you can roll back migrations if needed by implementing proper Down methods.
Version control your migrations: Keep migrations in source control along with your application code.

Use data seeding for reference data: Use migrations to seed reference data (like categories, statuses, etc.).

csharp
protected override void Up(MigrationBuilder migrationBuilder)
{
    // Create table
    migrationBuilder.CreateTable(/* ... */);
    
    // Seed data
    migrationBuilder.InsertData(
        table: "Categories",
        columns: new[] { "Name" },
        values: new object[,]
        {
            { "Electronics" },
            { "Books" },
            { "Clothing" }
        });
}

Be careful with production migrations: Consider the impact of migrations on production data and performance.
Document complex migrations: Add comments to explain complex migration logic, especially custom SQL.

Troubleshooting Common Migration Issues

Handling Migration Conflicts

If team members create migrations simultaneously, you might face conflicts. It's generally better to revert the latest migration and re-create it after incorporating the changes from both sides.

Remove-Migration

Resetting Migrations

In development, if migrations get too messy, you can reset them:

// Drop the database
Drop-Database

// Remove the last migration
Remove-Migration

// Create a fresh initial migration
Add-Migration InitialCreate

// Apply the migration to create a fresh database
Update-Database

Dealing with Model-Database Mismatches

If your model and database get out of sync, you can generate a migration based on the current state:

Script-Migration

This command generates SQL that you can examine to understand the differences.

Summary

Database migrations in C# with Entity Framework provide a powerful way to manage database schema evolution alongside your application code. They allow you to:

Track changes to your database schema over time
Apply changes in a controlled, repeatable manner
Work effectively in teams with shared databases
Roll back changes when needed
Handle complex data transformations during schema changes

By following the best practices outlined in this tutorial, you can maintain a clean, version-controlled database schema that evolves alongside your application.

Additional Resources

Exercises

Create a small blog application with Post and Comment models, then practice adding new features (like tags or categories) using migrations.
Practice writing a migration that includes data transformation (e.g., splitting a full name field into first and last name fields).
Set up a project that uses multiple DbContexts and practice creating migrations for each context.
Create a migration that adds a unique constraint or index to an existing table.
Write a custom migration that creates a database view or stored procedure.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction to Database Migrations​

Understanding Migrations in Entity Framework​

What Are Migrations?​

Getting Started with Migrations​

Prerequisites​

Setting Up Your Project for Migrations​

Creating Your Initial Migration​

Applying Migrations to the Database​

Making Schema Changes with Migrations​

Advanced Migration Concepts​

Customizing Migrations​

Managing Migrations in Different Environments​

Working with Multiple DbContexts​

Real-World Example: Online Store Database Evolution​

Initial Setup​

First Evolution: Adding Product Categories​

Second Evolution: Customer Accounts​

Best Practices for Migrations​

Troubleshooting Common Migration Issues​

Handling Migration Conflicts​

Resetting Migrations​

Dealing with Model-Database Mismatches​

Summary​

Additional Resources​

Exercises​