Consolidating EF migrations into new InitialCreate

I have been using EF migrations for some time now and have more than 100 migration files in my project. I would like to consolidate these into a single migration before moving forward - ie I want to replace the existing InitialCreate migration with a new version that takes all my subsequent changes into account so I can then delete all the other migration files.

I do this very easily if I am not concerned with losing all the data in the DB, but I am.

How can I achieve this whilst keeping all data intact and also retaining the ability to recreate the database from scratch (without data) by just running Update-Database (which I believe is not possible using the approach outlined by Julie Lerman)?

Consider reading this nice article from Rick Strahl : https://weblog.west-wind.com/posts/2016/jan/13/resetting-entity-framework-migrations-to-a-clean-slate

Basically the solution is not trivial and needs more than just reseting all the migrations into one because you have two scenarios that needs to fit in ONE migration class:

• Create a new database => the migration class should contain every table creation
• My database is already up to date => I need an empty migration class

Solution: The idea of this process is basically this: The database and the EF schema are up to date and just the way you want it, so we are going to remove the existing migrations and create a new initial migration.

In summary, the steps to do this are:

• Remove the _MigrationHistory table from the Database
• Remove the individual migration files in your project's Migrations folder
• Enable-Migrations in Package Manager Console
• Add-migration Initial in PMC
• Comment out the code inside of the Up method in the Initial Migration
• Update-database in PMC (does nothing but creates Migration Entry) Remove comments in the Initial method You've now essentially reset the schema to the latest version.
• once the commented out migration has been executed on the desired database, uncomment the migration code

If you're not concerned with keeping this migrations, what I've done is delete everything in your migrations folder, and then target a new database in the connection string (or pass in a new one). After that, you can just run the add-migration command:

add-migration InitialCreate

And it should create the migration for you.

Below procedure has the benefit of working without doing anything with the DBs, __MigrationHistory can stay as-is. Also it will work if you have multiple different environments with different versions of the structure - provided you have the branches to match.

I turn the last migration into an initial migration. The trick is to use the oldest version of the code and DB that is in use, replace its last migration with a new initial migration and delete all previous migrations. Newer branches keep the more recent migrations so those will still work after merging to older branches.

So start in the OLDEST branch - PROD, normally - and do:

1. Remove all but the last migration
2. Remove the migration code in both the "Up" and "Down" methods in the last migration
3. Change build action of the last migration to "None" to let EF ignore it
4. Change active connection to point to a local DB database.
5. Make sure this local DB database does not exist
6. add-migration Initial
7. Copy Up and Down code from the created "Initial" migration to the last migration
8. Delete Initial migration
9. Change build action of the last migration back to "Compile"
10. Check in
11. Merge changes up
12. Test in DEV branch on LocalDB DB - it should do the new initial migration as well as the subsequent ones with no issues
13. Test in main branch on the latest DB - it shouldn't do anything

Note above only works if you don't add stuff to the migrations that EF doesn't do itself. E.g. if you add DB views etc. than the newly created migration won't get those, it only gets the scripts EF generates based on your code.

Removing all migrations or regenerating them has drawbacks so we took an approach we merged all older migrations.

It require a bit of scripting. You read about the details here https://www.bokio.se/engineering-blog/how-to-squash-ef-core-migrations/ and see the scripts here https://github.com/bokio/EFCoreTools/tree/main/MigrationSquasher

The basic is the following steps though (copied from the blog post):

Overview of our approach

1. Create a new fresh database from the old migrations (We will use this for comparison later)
2. Find a suitable target migration to be the new initial (We picked one about 3 months old)
3. Write a script to merge the Up() methods of all earlier migrations into the Up() method of a new migration. We ignored Down() because we don't use it for old migrations.
4. Generate this migration and add it to the project. In our case we called it 20200730130157_SquashedMigrations1.cs. We used the snapshot from the target migration we had picked.
5. Generate a 2nd prep migration that inserts into the migrations history that 20200730130157_SquashedMigrations1.cs has already run. We called this 20200730130156_SquashedMigrations1_prep.cs. Note the slightly smaller timestamp on that one to make sure it runs before the real migrations.
6. Delete the old migrations
7. Point our config to a new database and run the migrations.
8. Compare that the schema we generate is equal using the Sql Schema Compare in Visual Studio.
9. Work through the issues until we have equal schemas. This part is a bit complicated but I will get back to it.
10. Merge and 爛 (Ok, we did run more tests both on local and staging databases)

I hope this helps someone else. The EF team is looking at improving this story so if you have feedback on your requirements it probably help them to post that now https://github.com/dotnet/efcore/issues/2174.

We had the same problem. The general solution we found was to

• Archive the old migration / context on a Nuget Package (including dependancies and with a different namespace to avoid conflicts)
• Delete all migrations and create a new Init (InitV2) Migration from scratch.
• Change the startup sequence of our application:
  • If the database contains the first old Init migration then
    • Migrate the database using the Nuget Package to be sure it will be up to date
    • Then erase the content of the __EFMigrationHistory table and Insert the new migration in the table
  • After that use the standard Migrate method on the new context
• That's it !

This solution is relatively simple. It solve all cases

• New database (will be using the InitV2)
• Old Database (will be upgrading to the last V1 Migration and after that to the last version of the v2 Database)

Be aware, if you used custom scripts/sql(...) in the v1 migrations, you have to check if the v2 Init migrations needs it.

To be sure it was OK, we created an empty database from v1 migrations and another one from v2 init migration and did a schema and data diff (with Visual Studio SQL Server Tools)