.NET Migrations

Introduction

Managing database schemas is one of the most challenging aspects of application development. As your application evolves, your data model changes too - you might need to add new tables, modify columns, or create new relationships. Migrations in .NET provide a way to incrementally update your database schema to keep it in sync with your application's data model while preserving existing data.

In this guide, you'll learn how to use Entity Framework Core migrations to:

• Track database schema changes
• Apply changes to your database in a controlled manner
• Roll back changes when needed
• Deploy database changes across different environments

What Are Migrations?

Migrations are a set of tools in Entity Framework Core that allow you to:

1. Create a script that can update a database schema from one version to another
2. Apply these scripts to a database
3. Revert changes when needed

Think of migrations as version control for your database schema - each migration represents a specific set of changes that can be applied or rolled back.

Prerequisites

Before working with migrations, you'll need:

• A .NET project that uses Entity Framework Core
• Entity Framework Core Tools installed
• A basic understanding of database concepts
• A DbContext class that maps to your database

Setting Up Tools

To use migrations, you'll need the EF Core tools. You can install them as a global .NET CLI tool:

bash

dotnet tool install --global dotnet-ef

You'll also need to add the following package to your project:

bash

dotnet add package Microsoft.EntityFrameworkCore.Design

Creating Your First Migration

Let's walk through a practical example. Imagine we're building a simple library management system:

1. First, define your data model in C#:

csharp

// Book.cs
public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public int PublicationYear { get; set; }
}

// LibraryContext.cs
public class LibraryContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            "Server=(localdb)\\mssqllocaldb;Database=LibraryDb;Trusted_Connection=True;");
    }
}

2. Create your first migration:

bash

dotnet ef migrations add InitialCreate

This command will:

• Look at your current model (the Book class)
• Generate a migration script to create the necessary database tables
• Create migration files in a "Migrations" folder in your project

The output will look something like this:

Build started...
Build succeeded.
Done. To undo this action, use 'ef migrations remove'

If you look in your project, you'll see new files like:

• YYYYMMDDHHMMSS_InitialCreate.cs
• YYYYMMDDHHMMSS_InitialCreate.Designer.cs
• LibraryContextModelSnapshot.cs

The main migration file contains Up() and Down() methods:

csharp

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Books",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Title = table.Column<string>(nullable: true),
                Author = table.Column<string>(nullable: true),
                PublicationYear = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Books", x => x.Id);
            });
    }

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

Applying Migrations

After creating migrations, you need to apply them to update the actual database:

bash

dotnet ef database update

This command will create the database (if it doesn't exist) and apply all pending migrations. The output will look something like:

Build started...
Build succeeded.
Applying migration '20230806123456_InitialCreate'.
Done.

After running this command, your database will have a Books table that matches your Book class.

Making Model Changes

Let's say we want to add a new property to track the ISBN number for each book. We modify our model:

csharp

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public int PublicationYear { get; set; }
    public string ISBN { get; set; } // New property
}

To update the database with this change, create a new migration:

bash

dotnet ef migrations add AddBookIsbn

Then apply it:

bash

dotnet ef database update

EF Core will generate a migration that adds the new column to the Books table:

csharp

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "ISBN",
        table: "Books",
        nullable: true);
}

Rolling Back Migrations

Sometimes you need to revert changes. You can roll back to a specific migration:

bash

dotnet ef database update InitialCreate

This rolls back to the "InitialCreate" migration, undoing the "AddBookIsbn" migration.

To completely revert all migrations:

bash

dotnet ef database update 0

Migration Best Practices

1. Create small, focused migrations: Each migration should represent a specific change to your model.

2. Test migrations thoroughly: Apply and roll back migrations in a test environment before deploying to production.

3. Don't modify existing migrations: Once a migration is applied to a shared database, create new migrations for further changes.

4. Use meaningful migration names: Names like "AddBookIsbn" or "CreateUserTable" are more helpful than "Migration1".

5. Include data migrations when needed: Sometimes you need to migrate data along with schema changes:

csharp

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Add a column
    migrationBuilder.AddColumn<string>(
        name: "FullName",
        table: "Authors",
        nullable: true);
        
    // Migrate data from old columns to new
    migrationBuilder.Sql(@"
        UPDATE Authors
        SET FullName = FirstName + ' ' + LastName
    ");
    
    // Remove old columns
    migrationBuilder.DropColumn(
        name: "FirstName",
        table: "Authors");
        
    migrationBuilder.DropColumn(
        name: "LastName",
        table: "Authors");
}

Real-world Example: Library Management System

Let's extend our library example with a more complete data model and migration workflow:

1. First, define a more complex model:

csharp

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string ISBN { get; set; }
    public int PublicationYear { get; set; }
    
    public int AuthorId { get; set; }
    public Author Author { get; set; }
    
    public ICollection<CheckoutRecord> CheckoutRecords { get; set; }
}

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    public ICollection<Book> Books { get; set; }
}

public class CheckoutRecord
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; }
    
    public int PatronId { get; set; }
    public Patron Patron { get; set; }
    
    public DateTime CheckoutDate { get; set; }
    public DateTime? ReturnDate { get; set; }
}

public class Patron
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    
    public ICollection<CheckoutRecord> CheckoutRecords { get; set; }
}

public class LibraryContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
    public DbSet<Patron> Patrons { get; set; }
    public DbSet<CheckoutRecord> CheckoutRecords { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            "Server=(localdb)\\mssqllocaldb;Database=LibraryDb;Trusted_Connection=True;");
    }
}

2. Create initial migration:

bash

dotnet ef migrations add InitialLibrarySchema

3. Apply it:

bash

dotnet ef database update

4. Later, you realize you need to track multiple copies of the same book:

csharp

public class Book
{
    // Existing properties...
    public int CopiesAvailable { get; set; }
}

5. Create a new migration:

bash

dotnet ef migrations add AddBookCopiesAvailable

6. Apply it:

bash

dotnet ef database update

Deployment Considerations

When deploying your application to production, you have several approaches:

1. Apply migrations at application startup

csharp

public class Program
{
    public static void Main(string[] args)
    {
        var host = CreateHostBuilder(args).Build();
        
        // Apply migrations at startup
        using (var scope = host.Services.CreateScope())
        {
            var services = scope.ServiceProvider;
            var context = services.GetRequiredService<LibraryContext>();
            context.Database.Migrate();
        }
        
        host.Run();
    }
}

2. Generate SQL scripts for manual deployment

bash

dotnet ef migrations script --output migration.sql

This generates a SQL script containing all migration operations, which DBAs can review and execute manually.

Common Migration Issues and Solutions

Issue: Migrations Won't Apply

Possible causes:

• The migration has already been applied
• The model snapshot is out of sync

Solution:

bash

# If you need to start from scratch (development only!)
dotnet ef database drop --force
dotnet ef database update

Issue: Migrations Apply But Data is Lost

Possible cause:

• The migration includes a table drop and recreation when a column change was intended

Solution:

• Use migrationBuilder.AlterColumn() instead of drop and recreate operations
• Consider manual migrations for complex data preserving operations

Issue: Migration Conflicts in Team Development

Possible cause:

• Multiple developers created migrations from the same base but with different changes

Solution:

• Coordinate migrations within the team
• Consider using a shared development database
• Merge migrations carefully when conflicts occur

Summary

Entity Framework Core migrations provide a powerful way to manage database schema changes in your .NET applications. They allow you to:

• Track schema changes in version control
• Apply incremental updates to your database
• Roll back changes when needed
• Deploy database changes across environments

In this guide, we covered:

• Creating and applying migrations
• Making and tracking model changes
• Rolling back migrations when needed
• Best practices for managing migrations
• Real-world examples and scenarios

With migrations, you can maintain a synchronization between your C# model classes and your database schema, making database development in .NET much more manageable.

Further Learning Resources

1. Entity Framework Core Documentation
2. EF Core Migrations in Depth
3. Advanced EF Core Migrations Patterns

Exercises

1. Create a new .NET application with a simple Product class that includes Id, Name, and Price properties. Set up EF Core and create a migration.

2. Add a new property Description to your Product class. Create and apply a migration to update the database.

3. Create a more complex model with at least three related entities. Set up appropriate relationships and create migrations.

4. Experiment with data migrations: Add a migration that populates a new column with derived values from existing data.

5. Advanced: Create a migration that needs to rename a table without losing data. Hint: You'll need to use raw SQL operations.