C# – Applying EF Migrations

Originally published on Medium · November 8, 2024 — examples target the tooling of that time.

Writing the migration is the easy part. Getting it applied safely to a real database, in a real environment, is where things get interesting. EF Core actually gives you several ways to do this, and the right one depends heavily on where you are in the project's lifecycle. Here's a rundown of the options I've used, with the trade-offs of each.

Note: No matter which strategy you choose, always review your generated migrations and test them in a staging environment. You don't want a migration accidentally dropping a column when it was supposed to rename it!

Setup

Technologies

For this guide, we'll use some of the most popular and user-friendly technologies out there:

Creating the Project

Step 1: Create a project with a template

Kick things off with the ASP.NET Web API template.

Step 2: Install necessary NuGet packages

Install the following packages to set up your ORM and database connection:

Install-Package Microsoft.EntityFrameworkCore
Install-Package Npgsql.EntityFrameworkCore.PostgreSQL

Step 3: Install .NET CLI tools

To create and apply migrations, you'll need the .NET CLI. Install it with:

dotnet tool install --global dotnet-ef

Step 4: Create the models and contexts

Create a simple entity, like a Blog:

public class Blog
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public DateTime CreatedTimestamp { get; set; }
}

Next, add this to your database context:

public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { }
    
    public DbSet<Blog> Blogs { get; set; }
}

Step 5: Add context to the application

Set up your connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DatabaseConnectionString": "Host=localhost;Port=5432;Username=postgres;Password=root;Database=blogs"
  }
}

Add the context to Program.cs:

var connectionString = builder.Configuration.GetConnectionString("DatabaseConnectionString");
builder.Services.AddDbContext<MyDbContext>(x => x.UseNpgsql(connectionString));

Step 6: Create a controller

Set up a controller to interact with the Blog entity:

[ApiController]
[Route("[controller]")]
public class BlogController(MyDbContext context) : ControllerBase
{
    [HttpGet]
    public Task<List<Blog>> GetAll()
    {
        return context.Blogs.ToListAsync();
    }

    [HttpPost]
    public Task<int> SaveBlog(string name)
    {
        context.Blogs.Add(new Blog()
        {
            Name = name,
            CreatedTimestamp = DateTime.UtcNow
        });
        return context.SaveChangesAsync();
    }
}

Step 7: Create a migration file

All strategies require an initial migration file. Create it with:

dotnet ef migrations add InitialCreate

Applying Migrations

Command-Line Tools (dotnet ef)

Advantages:

Disadvantages:

How to Perform:

Update to the latest migration:

dotnet ef database update

Update to a specific migration:

dotnet ef database update InitialCreate

SQL Scripts

Advantages:

Disadvantages:

How to Generate:

From a blank database to the latest migration:

dotnet ef migrations script -o initial_migration.sql

From one specific migration to another:

dotnet ef migrations script Initial AddedCreationProperty -o second_migration.sql

Example of generated script:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

START TRANSACTION;

CREATE TABLE "Blogs" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" text NOT NULL,
    "CreatedTimestamp" timestamp with time zone NOT NULL,
    CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id")
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240701140119_InitialCreate', '8.0.6');

COMMIT;

How to Apply:

psql -h localhost -U postgres -d blogs -f initial_migration.sql

Note: The generated migration file will be in UTF-8-BOM encoding. To run the migration, you may need to change it to UTF-8. You can do this by opening the file in Notepad++ and changing the encoding in the bottom right corner.

Idempotent SQL Scripts

Advantages:

Disadvantages:

How to Generate:

dotnet ef migrations script --idempotent

Example script:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" character varying(150) NOT NULL,
    "ProductVersion" character varying(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

START TRANSACTION;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240701140119_InitialCreate') THEN
    CREATE TABLE "Blogs" (
        "Id" integer GENERATED BY DEFAULT AS IDENTITY,
        "Name" text NOT NULL,
        "CreatedTimestamp" timestamp with time zone NOT NULL,
        CONSTRAINT "PK_Blogs" PRIMARY KEY ("Id")
    );
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240701140119_InitialCreate') THEN
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20240701140119_InitialCreate', '8.0.6');
    END IF;
END $EF$;

COMMIT;

How to Apply:

psql -h localhost -U postgres -d blogs -f initial_migration.sql

Bundles

Advantages:

Disadvantages:

How to Generate:

dotnet ef migrations bundle

Usage:

start efbundle.exe

Applying Migrations at Startup

Advantages:

Disadvantages:

Implementation: Add this to your Program.cs:

var app = builder.Build();

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

Conclusion

Choosing the right EF migration strategy depends on your needs, environment, and team:

By understanding and leveraging these strategies, you can manage your database schema changes effectively, ensuring your application's stability and scalability. Happy migrating!