We are in the process of upgrading a large multi-tenant application (single web app with multiple databases that have identical schema) from .Net Framework 4.8 to .Net 6, this includes moving from Entity Framework 6 to Entity Framework Core 7 which also means moving from Asp.Net Identity to Microsoft.AspNetCore.Identity.
The original project used code first Automatic Migrations for database updates which always worked great but this isn't available in EF Core so we need to setup manual migrations, therefore, the issue is we need to use the original databases and the same schema as must as possible, creating new databases and copying over the data from one to another isn't really viable due to the amount of databases and data involved and there cannot be any downtime.
The issue is that to get it to the point where the solution will currently build means we have changed from Asp.Net Identity to Microsoft.AspNetCore.Identity, this has resulted in changes to some database tables such as the adding of the "NormalisedUsername" etc fields in the AspNetUsers table as well as other changes.
We therefore need to potentially create an initial migration of the database in Entity Framework that will only include the changes that have been made by the upgrade to Microsoft.AspNetCore.Identity so that this can be run against the databases initially then going forward we can create new "migrations" for actual changes to the model that we make.
As well as the database changes that have been forced upon us by the move to AspNetCore.Identity it seems that EF Core makes various other changes to the schema of an old EF6 database such as changing Date columns from "datetime" to "datetime2", changes to foreign key and index names etc etc, I am not sure how much of an effect these would have on the running of the application if they are ignored (i.e. not rolled out to existing databases).
There is the other issue of creating new databases going forward but I assume that once we have migrated the existing databases we can possible create a new initial migration that will create all tables in an empty database (however this problem is something that can be looked at later on once we have got past the current issue).
Does anyone have any suggestions as the best way to tackle this problem, basically I think we need to get an initial Entity Framework migration setup that only performs the changes to the current database that have happened due to the framework upgrades and then we can go from there with other model changes that we make but what is the best way to do this, do we have to create a manual migration script by comparing the schemas from an old database and a fresh one created with updated application or is there some tool or trick for generating the initial migration script?
Thanks to the great help from @SvyatoslavDanyliv in the comments, I was able to put together the following steps to get this working:
Setup a dummy project and generate models and database context based on your current (i.e. old) database schema.
Generate migration file and snapshot in the new dummy project based on your old database schema:
Run the following in the Package Manager Console:
Generate a migration file in your live project:
Go back to your live project and run the following in the Package Manager console:
Overwrite the migration files in your live project with the ones from your dummy project
**Make a copy of the updated snapshot file in your live project, you may need this later to save having to keep copying from the dummy project and overwriting the namespaces each time.
Review your OnModelCreating in both projects and then create a blank database from your migration file in the live project
Review the "OnModelCreating" part of the DbContext in the dummy project and make necessary adjustments to your live project to ensure consistency with anything you might need to copy over. One example was I needed to copy over the following to stop it trying to change the collation:
Create another migration to include changes for EF Core and AspNetCore.Identity.
Now that you have a migration file that will create a perfect copy of your original database, you can run: Add-Migration -Name UpdateToEFCore
This should create a new migration file that just has the differences between your old schema and the new schema (i.e. changes since upgrading to EF Core etc).
Create an __EFMigrationsHistory table in your live database (well ideally a copy of your live database, you don't want to be running this on your live database yet), you can see the schema for this table from the test database you updated above, add an entry for "Initial" matching what was added to your test database above. This will make your "live" database think that the "Intial" migration has already been run on it.
Change the connection string in your project to point to your "live" database and run Update-Database against your live database. It should only apply the "UpdateToEFCore" changes.
If you have any problems and need to make some further changes to your "OnModelCreating" etc to get the last migration to work properly you can delete the "UpdateToEFCore" migration file and overwrite the snapshot file with the saved copy I mentioned above. Make any changes to OnModelCreating and recreate the "UpdateToEFCore" migration then run through the steps to run the "UpdateToEFCore" migration again.