Squashing EF Core Migrations Safely

Projects can evolve over time, and the history of granular migrations within Entity Framework can result in a bloated change history. Additionally, changes can occur within Entity Framework tooling that may result in code warnings or other similar concerns. Resetting your migrations to squash the history can be a very helpful process. Microsoft has published guidance that discusses the process as a whole, but in this post, I'll dive into the actual operations.

High Level Overview

• TAKE BACKUPS
• Delete the migrations folder
• Create a new Initial Migration
• Create a Migration SQL Script
• Purge the migration history table
• Insert your new history item

Special Considerations for Manual Migration Changes

Assuming that you are following best practices and NOT manually editing Migration files from their auto-generated state, this process is relatively safe. However, if you find yourself manually editing migrations regularly, this operation can be hazardous. When resetting migrations, your squashed migration history may not result in the same database model being created, and future migrations may be incorrect.

If you are unsure if this applies to you, I would suggest utilizing a test database and using a tool such as SQL Compare or otherwise to compare the schema of your real database to that of a blank database created using the new "initial" migration that is created from this process. Using comparison tools such as these can help spot structural deficiencies.

What to Backup

I strongly recommend backing up everything, including ensuring this is done with a clean, single commit within your source control system. Doing so will ensure that you can quickly revert to the old state without impact if any abnormality occurs.

Executing the Process

Once your backups are fully in place, the steps to migrate are actually pretty simple.

Clear Existing Migrations

Within your EntityFrameworkCore project, find the "migrations" folder and simply delete the folder. This will remove all historical migrations from the code base. DO NOT make any changes to the actual database yet!

Create a New Initial Migration

Now that your project has been cleaned, it is time to create the new migration. You can do this using the command line, or package manager console just as you would with any other migration. The below is an example of the standard command that I use.

dotnet ef migrations add Reset-Structure

This will create your new migration but will NOT do any database updates, which is important!

Script Migration & Extract History Entry

Now that we have created the new migration, we need to script the migration, so we can do any needed validation against a blank database, and also to be able to obtain the needed history entry for the final deployment steps. You can do this using the following command.

dotnet ef migrations script -o MigrationScript.sql

This will place a file "MigrationScript.sql" in the root of your project; open this file and scroll to the VERY bottom. Just before the "COMMIT" statement, you should see a script similar to the following, with the current date.

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20230108060418_Reset-Structure', N'6.0.0');
GO

Copy this value for the next step, as we will remove the CURRENT migration history and insert this single entry to our database.

Purge & Update History Table

The final step is to clear the existing historical records and insert our single entry to indicate that the current database version is, in fact, the same. We can do this by utilzing the following two statements, BE SURE TO UPDATE TO MATCH YOUR SCRIPT before executing as your values WILL be different than these.

TRUNCATE TABLE __EFMigrationsHistory
GO
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20230108060418_Reset-Structure', N'6.0.0');
GO

This completes the process of resetting. If you are deploying to multiple environments, be sure to complete this final Purge/Insert process to the history table BEFORE trying to execute any migrations against the target database, as otherwise you will cause major problems.

Final Thoughts

Squashing migration histories can be somewhat scary; however, they can substantially reduce the side of your code, as well as improve the overall execution time and performance of your application. Done safely and using migrations with care, this can be done with limited risk. Please validate your environments and take regular backups before doing ANY of these operations.