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

How about this:

1. Choose those migrations of yours that have already been deployed to all environments.
2. Make entity framework produce an (idempotent) sql script for those migrations.
3. Delete the code files (.cs and .Designer.cs) related to those migrations, except one.
4. Update the migration code in the cs file you did not delete, so that it loads the sql script and runs it.

This way the code files delaying build are dropped, their migrations are preserved (squashed), and the 'sql-script' migration will only run in new environments.

Answer 2

I've done similar task today for my project and the following are the steps I used (updated from niranjan's answer):

0. Ensure that Program.cs file is not optimized for creating/ updating Database by Database.EnsureCreate command, as this command prevents Migrations.
1. Backup target production database (in case anything goes wrong, we can restore using the backups).
2. Delete all files (incl. migration scripts and snapshot) under Migrations folder.
3. Create a new database without any objects.
4. In local development environment, change database connection string to point to the newly created empty database/
5. Use dotnet ef migrations command to generate a new migratipn. For example, dotnet ef migrations add Rebase20230701 --startup-project StartupProjectName --project ProjectNameThatContainsMigrations
6. In step 5), a new migration will be generated, for example, 20230701012623_Rebase20230701.
7. Use the migration ID generated above to manually insert into local original database, for example insert into dbo.__EFMigrationsHistory(MigrationId, ProductVersion) VALUES ('20230701012623_Rebase20230701','7.0.8');. This will informs EF not to do any actual migrations. Please do keep your previous migrations records in the table.
8. Run your application locally to ensure everything works ok and no data is lost.
9. Run step 7) again in your target production environment.
10. Commit your code changes into your repo and deploy your applications into target production environment. The migrations folder should only have 20230701012623_Rebase20230701.cs and the snapshot C# files.

Answer 3

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.

Answer 4

To reset all migrations and updates from scratch (Assume no useful data on disk), the following steps may be useful.

---

(1) Ensure that program.cs file is not optimized for creating/ updating Database by Database.EnsureCreate command, as this command prevents Migrations.
(2) Delete folder Migrations.
(3) dotnet build
(4) dotnet ef database update 0 -c yourContextFile
(5) dotnet ef migrations add init -c yourContextFile
(6) dotnet ef database update -c yourContextFile

Answer 5

A bit late, but we had the same problem in our current project. Above 400 migraitons and 6m lines of code inside .Designer. Here is how we managed to resolve this problem:

MigrationProject.csproj

  <PropertyGroup>
     ...
     <DefaultItemExcludes Condition="'$(Configuration)' == 'Debug' ">$(DefaultItemExcludes);Migrations\**\*.Designer.cs</DefaultItemExcludes>
  </PropertyGroup>

This way you don't need to reset migrations to a clear state neither delete .Designer files. You can always change configuration to Release and use .Designer files by any means necessary.