Recommended way to clean old Entity Framework Core migrations

Question

After developing our application for a while we've accumulated quite a bit of EFCore database migrations. Since EFCore adds a snapshot of the entire db model to every migration, this code adds up quite a bit. After analysis about 80% of our compile time is spend on the migrations (compiling + Roslyn analyzers).

So it's time to clean up some old migrations! But what's the best way to do this? There doesn't seem to be any official guidance on it...

We don't need any rollbacks (we only roll forward), so that makes things more simple. We do need to support creating a database from scratch, and updating a database from the last few migrations.

What I've tried:

1. The nuclear option seems to be to delete all migrations and the model snapshot, and creating a new initial migration. While this is fine, it seems a bit dangerous. With this approach we need to be very careful that every part of the database schema is part of the code model. One edge case we for example ran into is that EFCore doesn't support checked constraints yet. So we added a checked constraint in a migration, but not in the code model. So when creating a new initial migration, the checked constraint was not part of it.

2. As an experiment, I've tried to delete the model snapshot from all old migrations, since the snapshots are 90% of the code which cause the long compile time. I figured out, that EFCore only uses the snapshot as a compare tool to make a new migration. After deleting the snapshot, the old migrations were however no longer executed when they ran on a fresh database.

So is there any better way to accomplish what I want?

Answer 1

Okay, since asking this question I've experimented quite a bit with this.

It seems for now, the best way to accomplish this is option 1. Option 2 would be much better, but until this EFCore feature is implemented, it's not really doable for my use case (supporting existing dbs with migrations on them, and supporting empty dbs).

Option 1 also has a few pitfalls which I stumbled upon (maybe even more that I haven't stumbled upon). So this is how I did it:

Create a new initial migration:

1. Make sure all your existing migrations have been applied to your database. We'll create a new initial migration, so the migrations that haven't been applied will be lost.
2. Delete your old EFCore migration files, and the database snapshot file.
3. Create a new Initial migration from your database's current state. (For example via dotnet ef migrations add Initial-PostCleanup.)

This new migration is only compatible with new databases, since it will create all tables (and fail if any of the tables, constraints, etc. already exist). So now we're going to make this migration compatible with the existing database:

1. Create a SQL script for the new initial migration via dotnet ef migrations script -o script.sql.
2. Remove the first transaction (until the first GO), which creates the __EFMigrationsHistory table:

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;

GO

3. Remove the last transaction, that inserts the new entry in the __EFMigrationsHistory table:

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20190704144924_Initial-PostCleanup', N'2.2.4-servicing-10062');

GO

4. Remove GO commands, since we will put the create script in an IF statement:
   Replace GO\r\n\r\n with nothing.
5. Now open up your migration file (the C# file, not the sql file) and replace the Up method with the following:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
DECLARE @migrationsCount INT = (SELECT COUNT(*) FROM [dbo].[__EFMigrationsHistory])
IF @migrationsCount = 0
BEGIN
    % PASTE YOUR EDITED SQL SCRIPT HERE %
END
");
}

Done! Everything should work now!

Be sure to compare the database schema, and data before and after for the new database. Everything that's not part if your EF Code model is not part of the new database.