Skip to main content

EF Core Migrations: A Detailed Guide

Milan Jovanović2024년 5월 18일About 7 minC#DotNetArticle(s)blogmilanjovanovic.techcsc#csharpdotnet

EF Core Migrations: A Detailed Guide 관련

C# > Article(s)
Article(s)
EF Core Migrations: A Detailed Guide
In this newsletter, we'll break down the essentials of EF Migrations. We'll explore creating migrations, SQL scripts, applying migrations, migration tooling, and more.

Managing database schemas as your applications grow can quickly become a headache. Manual changes are error-prone and time-consuming. This can easily lead to inconsistencies between development and production environments. I've seen these issues firsthand on countless projects, and it's not pretty. How can we do better?

Enter Entity Framework (EF) Migrations, a powerful tool that lets you version your database schemas.

Imagine this: Instead of writing SQL scripts, you define your changes in code. Need to add a column? Rename a table? No problem - EF Migrations has you covered. It tracks every modification to the data model. You can review, test, and apply changes confidently, even across different environments.

In this newsletter, we'll break down the essentials of EF Migrations:

We have many examples to cover, so let's dive in.

Creating Migrations

If you're completely new to EF migrations, I recommend checking out the EF migrations docs to grasp the fundamentals. Moving forward, I'll assume you have some prior knowledge of EF Core.

We'll need an entity and a database context before we can create migrations with EF.

Let's define a simple Product entity:

public class Product
{
    public int Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public string? Description { get; set; }

    public decimal Price { get; set; }
}

We will also need a DbContext implementation, so let's define the AppDbContext class. In the OnModelCreating method, we're going to configure the Product entity.

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(builder =>
        {
            builder.ToTable("Products", tableBuilder =>
            {
                tableBuilder.HasCheckConstraint(
                    "CK_Price_NotNegative",
                    sql: $"{nameof(Product.Price)} > 0");
            });

            builder.HasKey(p => p.Id);

            builder.Property(p => p.Name).HasMaxLength(100);

            builder.Property(p => p.Description).HasMaxLength(1000);

            builder.Property(p => p.Price).HasPrecision(18, 2);

            builder.HasIndex(p => p.Name).IsUnique();
        });
    }
}

Let's break down a few of the methods we're using:

We're now ready to create our first migration. I'm going to use the PowerShell syntax:

Add-Migration Create_Database

This will create the first database migration called Create_Database. The migration will apply the configuration we defined in the OnModelCreating method. It contains the Up and Down methods, allowing us to apply or revert changes to the database.

Note that some operations are destructive (like removing a column) and can't be easily reverted. It's up to you to examine the generated migration and prevent any possible data loss.

using Microsoft.EntityFrameworkCore.Migrations;
using Npgsql.EntityFrameworkCore.PostgreSQL.Metadata;

public partial class Create_Database : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Products",
            columns: table => new
            {
                Id = table.Column<int>(type: "integer", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                Name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
                Description = table.Column<string>(type: "character varying(1000)", maxLength: 1000, nullable: true),
                Price = table.Column<decimal>(type: "numeric(18,2)", precision: 18, scale: 2, nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
                table.CheckConstraint("CK_Price_NotNegative", "Price > 0");
            });

        migrationBuilder.CreateIndex(
            name: "IX_Products_Name",
            table: "Products",
            column: "Name",
            unique: true);
    }

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

Customizing Migrations

We can also modify the migration files if we need to apply some custom changes.

A notable example is renaming a column. Let's say we rename the Description property to ShortDescription. In some EF versions, this would result in the following migration:

migrationBuilder.DropColumn(
    name: "Description",
    table: "Customers");

migrationBuilder.AddColumn<string>(
    name: "ShortDescription",
    table: "Products",
    nullable: true);

What's the problem here? By calling DropColumn first, we will remove the column from the database and lose valuable data.

What we actually want to do is rename the existing column. So, we can modify the migration file to use the RenameColumn method:

migrationBuilder.RenameColumn(
    name: "Description",
    table: "Products",
    newName: "ShortDescription");

Another example is executing custom SQL commands from your migrations. Custom SQL commands are helpful when we can't express something through the EF fluent API. I've used it in the past to migrate data from one column to another or define a complex index.

public partial class Update_Products : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("<YOUR CUSTOM SQL HERE>");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // You are also responsible for reverthing any changes.
    }
}

Migration SQL Scripts

You can use the Script-Migration command to generate SQL scripts from your migrations. This is useful for reviewing changes before applying them to the database. SQL scripts allow us to execute migrations in environments without direct access to the EF tooling.

Remember, you are responsible for preventing any data loss when executing EF migrations. Review the migrations carefully before applying them to the database.

Here are a few ways you can execute the Script-Migration command:

Script-Migration

Script-Migration <FromMigration>

Script-Migration <FromMigration> <ToMigration>

The <FromMigration> argument should be the name of the last migration applied to the database. It's your responsibility to apply the script appropriately, and only to databases in the correct migration state.

Here's what the SQL script for the Create_Database migration looks like:

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 "Products" (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "Name" character varying(100) NOT NULL,
    "Description" character varying(1000),
    "Price" numeric(18,2) NOT NULL,
    CONSTRAINT "PK_Products" PRIMARY KEY ("Id"),
    CONSTRAINT "CK_Price_NotNegative" CHECK (Price > 0)
);

CREATE UNIQUE INDEX "IX_Products_Name" ON "Products" ("Name");

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240516095344_Create_Database', '8.0.5');

COMMIT;

You can also specify an -Idempotent argument to the Script-Migration command. The Script-Migration command will generate SQL scripts that only apply migrations that haven't been applied already. This is useful if you're not sure what the last migration applied to the database.

Script-Migration -Idempotent

Here's what the idempotent SQL script looks like:

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" = '20240516095344_Create_Database') THEN
    CREATE TABLE "Products" (
        "Id" integer GENERATED BY DEFAULT AS IDENTITY,
        "Name" character varying(100) NOT NULL,
        "Description" character varying(1000),
        "Price" numeric(18,2) NOT NULL,
        CONSTRAINT "PK_Products" PRIMARY KEY ("Id"),
        CONSTRAINT "CK_Price_NotNegative" CHECK (Price > 0)
    );
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN
    CREATE UNIQUE INDEX "IX_Products_Name" ON "Products" ("Name");
    END IF;
END $EF$;

DO $EF$
BEGIN
    IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '20240516095344_Create_Database') THEN
    INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20240516095344_Create_Database', '8.0.5');
    END IF;
END $EF$;
COMMIT;

Applying Migrations

How do we apply EF migrations to the database?

We have a few options:

We discussed SQL scripts in the previous section, so I won't mention them again.

Command-line Tools

The most common approach to applying database migrations is using the CLI. You can use either the dotnet ef tool or the PowerShell commands. For example, you can execute the Update-Database command from PowerShell to apply any pending migrations.

Update-Database -Migration <ToMigration> -Connection <ConnectionString>

Here are the documentation links if you want to learn more:

Applying Migrations through Code

Here's a helper method for applying database migrations. It uses an IServiceScope to resolve a DbContext instance and uses it to call the Migrate method.

public static void ApplyMigration<TDbContext>(IServiceScope scope)
    where TDbContext : DbContext
{
    using TDbContext context = scope.ServiceProvider
        .GetRequiredService<TDbContext>();

    context.Database.Migrate();
}

You can apply migrations when the application is starting. I don't recommend using this approach for production environments. Migration can fail, concurrency issues exist, and rolling back migrations is challenging. However, this approach can be helpful in local development and when scaffolding databases for integration testing.

var builder = WebApplication.CreateBuilder(args);

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    using IServiceScope scope = app.ApplicationServices.CreateScope();

    ApplyMigration<AppDbContext>(scope);
}

app.Run();

Migration Bundles

Migration bundles are executable files that you can use to apply database migrations. They're self-contained and can be executed from CI pipelines.

You can use the Bundle-Migration command to create a migration bundle:

Bundle-Migration -Connection <ConnectionString>

This will create an efbundle.exe file that we can run to apply any pending database migrations.

I recommend reading the migration bundles documentation to learn more.

Additional Database Migration Tools

What can you do if you don't want to use EF Core migrations?

I wanted to mention some additional tools you can use for database schema versioning and running migrations:

We won't do a deep dive on these tools, but I recommend you check out their documentation.

EF Core Migrations Best Practices

I want to wrap up this issue with a few tips from my experience of working with EF Core migrations over the years:

My preferred approach to applying migrations is using SQL scripts. Depending on the project scope and complexity, we could do this manually or through a tool that automates the process. This allows me to review the migration and identify any potential problems.

I hope this was helpful!

Thanks for reading, and I'll see you next week.