EF Core Migrations: migrations add, database update, Scripts and Bundles

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: Does EnsureCreated Add the New Column?

So far we have created the database with EnsureCreated(). Now picture this: the app has been running for a few days and the Books table holds hundreds of rows. You add a new Isbn property to the Book class and restart the app. Does EnsureCreated() add an Isbn column to the table?

It does not. EnsureCreated() only does something when the database does not exist at all. If the database is there, it returns false without touching anything; it never checks whether the model has changed. So the first query fails with "no such column: b.Isbn". Dropping and recreating the database means throwing away production data.

Migrations solve exactly this problem. They record every change to the model as a small, ordered, reversible step, and they write into the database which steps have been applied. They do for the schema what Git does for code: each migration is a commit, and the __EFMigrationsHistory table is the marker that says which commit the database is on.

The model for this post is kept short:

using Microsoft.EntityFrameworkCore;

namespace Bookstore;

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = null!;
    public decimal Price { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public class BookstoreContext : DbContext
{
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=bookstore.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>().Property(b => b.Title).HasMaxLength(200);
    }
}

The First Migration: migrations add

Migration commands come from the dotnet-ef tool and the Microsoft.EntityFrameworkCore.Design package in the project. The tool version should match the package; I used dotnet-ef 6.0.36 for the EF Core 6 project and 10.0.14 for the EF Core 10 project. Installing the tool locally in the project folder avoids version clashes with other projects on the machine:

dotnet new tool-manifest
dotnet tool install dotnet-ef --version 6.0.36
dotnet ef migrations add InitialCreate

The output is short:

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

Note the first line: the tool builds the project first, then loads the compiled assembly, creates BookstoreContext and reads the model. If your project does not compile, no migration is generated either. The command leaves three files in the Migrations folder:

Migrations/20261103214436_InitialCreate.cs
Migrations/20261103214436_InitialCreate.Designer.cs
Migrations/BookstoreContextModelSnapshot.cs

Anatomy of a Migration File

The real work lives in 20261103214436_InitialCreate.cs. This is what EF Core 6 generated:

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace Bookstore.Migrations
{
    public partial class InitialCreate : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Authors",
                columns: table => new
                {
                    Id = table.Column<int>(type: "INTEGER", nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Name = table.Column<string>(type: "TEXT", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Authors", x => x.Id);
                });

            migrationBuilder.CreateTable(
                name: "Books",
                columns: table => new
                {
                    Id = table.Column<int>(type: "INTEGER", nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    Title = table.Column<string>(type: "TEXT", maxLength: 200, nullable: false),
                    Price = table.Column<decimal>(type: "TEXT", nullable: false),
                    AuthorId = table.Column<int>(type: "INTEGER", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Books", x => x.Id);
                    table.ForeignKey(
                        name: "FK_Books_Authors_AuthorId",
                        column: x => x.AuthorId,
                        principalTable: "Authors",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_Books_AuthorId",
                table: "Books",
                column: "AuthorId");
        }

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

            migrationBuilder.DropTable(
                name: "Authors");
        }
    }
}

Up describes how to move forward, Down how to go back. Notice that Down drops the tables in reverse order: first Books, which carries the foreign key, then Authors. This file is yours; you read it, review it and edit it by hand when needed. If you need a data transformation, you can put your own SQL in between with migrationBuilder.Sql("...").

The timestamp in the file name (UTC) defines the order, and migrations are applied in that order. The file EF Core 10 generates is identical; the only difference is the /// <inheritdoc /> comments added above the methods.

The other two files are not for you, they are for the tool. .Designer.cs holds a snapshot of the model at the moment of that migration. BookstoreContextModelSnapshot.cs is the latest state of the model. On the next migrations add, the tool does not look at the database; it compares the current model with this snapshot and writes the difference as a new migration. The modelBuilder.HasAnnotation("ProductVersion", "6.0.36") line inside the snapshot tells you which EF Core version produced it.

database update and __EFMigrationsHistory

A migration file alone does not change the database. To apply it:

dotnet ef database update

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

On EF Core 10 you see one more line at the start of this output: "Acquiring an exclusive lock for migration application." That is the migration lock introduced in EF Core 9; we will look at it in the version posts.

The tool created the tables and added one more. Let us check with the SQLite command-line tool:

sqlite3 bookstore.db "select * from __EFMigrationsHistory"

20261103214436_InitialCreate|6.0.36

EF Core writes every applied migration here with its name and its own version. Each time database update runs, it reads this table, compares it with the list of migrations in the project and applies only the missing ones. Run the command a second time and you get "No migrations were applied. The database is already up to date."

Changing the Model, Reverting, Removing

Back to the scenario from the start of the post. Add the property to Book:

public string? Isbn { get; set; }

Then generate a new migration and list the state:

dotnet ef migrations add AddBookIsbn
dotnet ef migrations list

20261103214436_InitialCreate
20261103214518_AddBookIsbn (Pending)

The (Pending) marker says the migration exists in the project but has not been applied to the database yet. The generated Up method contains a single operation:

migrationBuilder.AddColumn<string>(
    name: "Isbn",
    table: "Books",
    type: "TEXT",
    nullable: true);

Say you applied it with database update, then changed your mind and want to delete the migration. migrations remove refuses to delete an applied migration:

The migration '20261103214518_AddBookIsbn' has already been applied to the database. Revert it and try again. If the migration has been applied to other databases, consider reverting its changes using a new migration instead.

The message says two things. First revert the database to the previous migration, then remove it. But if this migration has reached other databases (a teammate's machine, the test environment, production), do not remove it; write a new migration that undoes the change. Not rewriting shared history is the same rule as not running push --force in Git.

To revert locally, you pass the name of the target migration. To revert everything, you pass 0 instead of a name:

dotnet ef database update InitialCreate
dotnet ef migrations remove

Reverting migration '20261103214518_AddBookIsbn'.
Done.
Removing migration '20261103214518_AddBookIsbn'.
Reverting the model snapshot.
Done.

migrations remove only removes the latest migration and also rolls the snapshot back one step. Deleting migration files by hand leaves the snapshot behind, and the next migrations add computes the wrong difference. Always let the tool do the removing.

Generating SQL Scripts and --idempotent

Many teams do not want dotnet ef database update running in production: the tool would need access to the production database, and the source code would have to be there. The cleaner way is to turn migrations into SQL and have a DBA or a pipeline apply it:

dotnet ef migrations script InitialCreate AddBookIsbn

The first argument is the starting point, the second the target. The starting migration itself is not in the script; the script covers what comes after it:

BEGIN TRANSACTION;

ALTER TABLE "Books" ADD "Isbn" TEXT NULL;

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20261103214518_AddBookIsbn', '6.0.36');

COMMIT;

Swap the arguments and you get a revert script, and here you hit a SQLite limitation. Because SQLite's ALTER TABLE support is limited, EF Core rebuilds the table instead of dropping the column:

CREATE TABLE "ef_temp_Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "AuthorId" INTEGER NOT NULL,
    "Price" TEXT NOT NULL,
    "Title" TEXT NOT NULL,
    CONSTRAINT "FK_Books_Authors_AuthorId" FOREIGN KEY ("AuthorId") REFERENCES "Authors" ("Id") ON DELETE CASCADE
);

INSERT INTO "ef_temp_Books" ("Id", "AuthorId", "Price", "Title")
SELECT "Id", "AuthorId", "Price", "Title"
FROM "Books";

PRAGMA foreign_keys = 0;
DROP TABLE "Books";
ALTER TABLE "ef_temp_Books" RENAME TO "Books";
PRAGMA foreign_keys = 1;

The block above is a shortened version of the real script; the tool splits these steps into separate transactions. On a large table this rebuild takes a long time, which is a good reason to read the script before sending a migration to production.

Scripts have one problem: you need to know which migration the database is on. The --idempotent option solves that; it wraps each migration in an "only run if this migration has not been applied" condition, so the script lands in the right place no matter which database it runs against. SQLite does not have this option:

Generating idempotent scripts for migrations is not currently supported for SQLite. See http://go.microsoft.com/fwlink/?LinkId=723262 for more information.

You do not need a database to see what the same model produces with the SQL Server provider; generating a script does not open a connection. Here is a part of the script EF Core 6 generates for SQL Server (EF Core 10 splits the same condition over several lines and does not put GO between every block):

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20261103214732_AddBookIsbn')
BEGIN
    ALTER TABLE [Books] ADD [Isbn] nvarchar(max) NULL;
END;
GO

For a project on SQL Server or PostgreSQL, this is the safest option for CI/CD.

Migration Bundles (EF Core 6)

The alternative to scripts is the migration bundle that came with EF Core 6. It packs the migrations into a single executable; the target machine needs neither the source code nor dotnet-ef. I re-added the AddBookIsbn migration in between, which is why the timestamp is different:

dotnet ef migrations bundle --force
./efbundle --connection "Data Source=prod.db"

Applying migration '20261103214436_InitialCreate'.
Applying migration '20261103214619_AddBookIsbn'.
Done.

Without --connection, the bundle uses the connection string from OnConfiguring or AddDbContext. The bundle also checks __EFMigrationsHistory and does nothing on a second run. For a bundle headed to a Linux server, --self-contained -r linux-x64 puts the .NET runtime inside as well. Compared with a script, the upside is that the logic stays inside EF Core itself; the downside is that you cannot read the SQL before running it.

Migrating at Application Startup

The shortest path is to apply migrations from code when the app starts:

using Bookstore;
using Microsoft.EntityFrameworkCore;

using var db = new BookstoreContext();

var pending = db.Database.GetPendingMigrations().ToList();
Console.WriteLine($"Pending: {string.Join(", ", pending)}");

db.Database.Migrate();
Console.WriteLine($"Applied: {db.Database.GetAppliedMigrations().Count()}");

Pending: 20261103214436_InitialCreate, 20261103214619_AddBookIsbn
Applied: 2

On a development machine or in a single-user desktop app, this is perfectly reasonable. In a web app it needs care. If your app starts with three instances, all three try to migrate at the same time. The database user the app runs as needs permission to create and drop tables, which you do not want from a security point of view. A long migration holds up startup and health checks time out. A migration that fails halfway leaves the app unable to start, and fixing it means changing the code and deploying again.

Microsoft's documentation also recommends scripts or bundles for production. We will use this distinction again when we talk about deployment in the ASP.NET Core Web API series.

Common Pitfalls

The first pitfall is mixing EnsureCreated() with Migrate(). EnsureCreated() creates the tables without migrations and does not write the __EFMigrationsHistory table. If you call Migrate() afterwards, EF Core thinks no migration has been applied and tries to create the tables again:

Unhandled exception. Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'table "Authors" already exists'.

Pick one; in a project that uses migrations, keep EnsureCreated() for tests only.

The second is applying a generated migration without reading it. When I renamed Author.Name to FullName, EF Core caught it and generated RenameColumn. But when I changed the type at the same time (long? IsbnNumber instead of string? Isbn), the tool did not recognize it as a rename:

An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.

migrationBuilder.DropColumn(
    name: "Isbn",
    table: "Books");

migrationBuilder.AddColumn<long>(
    name: "IsbnNumber",
    table: "Books",
    type: "INTEGER",
    nullable: true);

When you see this warning, open the migration. If you need to move the data, write the conversion yourself with migrationBuilder.Sql(...) before the DropColumn.

The third is changing the model and forgetting to add a migration. On EF Core 6, Migrate() does not notice; it applies the old migrations and carries on silently, and you see the error on the first query. EF Core 9 and later throw a PendingModelChangesWarning error in this situation; we will see that in the version posts.

Finally, a SQLite quirk: HasMaxLength(200) shows up as maxLength: 200 in the migration, but SQLite still creates the column as TEXT and does not enforce the length. On SQL Server the same setting becomes nvarchar(200). When you switch providers, you need to regenerate the migrations for that provider; migrations generated for SQLite do not run on SQL Server.

A Small Experiment

★☆☆ Add a string? Country property to Author, generate an AddAuthorCountry migration, see the Pending state with migrations list and apply it with database update. Look at the __EFMigrationsHistory table with sqlite3 and count the rows.

★★☆ Revert all migrations with dotnet ef database update 0. Check which tables remain. Then write the output of dotnet ef migrations script 0 AddAuthorCountry to a file and, reading through it, mark which lines come from which migration.

★★★ Change Book.Price into an int named PriceInCents. See the data loss warning in the generated migration, then edit the Up method by hand: add the new column first, copy the existing prices converted to cents with migrationBuilder.Sql, and drop the old column last. Write the Down method to match.

Migrations built the schema, but the tables are empty. Where does the data that must exist in every environment come from, such as a bookstore's genre list, the initial authors or a fixed category table? In the next post we look at model seeding with HasData, its limits, and seeding from the application side.