Migrations: Improve existing database support

[bricelam]

We need to create a good experience to bootstrap an existing database with Migrations.

Things to consider:

• The database may have been using Migrations in EF6
• The first migration should be able to re-create the starting database schema
• The user shouldn't have to jump through hoops like commenting out code or only running part of a script.

[divega]

This need came up while prototyping the solutions for aspnet/Identity#457. In that particular scenario a few new columns need to be added to the database as part of the upgrade, which may or may not pose an additional challenge.

In general EF Core Migrations cannot predict the current schema of a database regardless of how it was created and whether there are additional model changes in the upgrade. Even if the database was maintained using EF6 Migrations there are functional differences between the stacks that will make the database look different.

Here is a set of ideas we talked about that could be used to address this:

1. We add a way to point our reverse engineering engine to the existing database to obtain a model snapshot (and possibly an accompanying initial migration).
2. The user creates a migration based on the current EF Core model and the snapshot obtained in the first step.
3. The user looks at this migration and decides which adjustments should be made to the model (e.g. column and tables renames in OnModelCreating()) to better match the existing database.
4. After each adjustment to the model the user can go back and re-scaffold the migration created in step 2, iterating over steps 2 and 3 as many times as necessary until a migration representing the desired diff is obtained (the migration should be empty if the model was made match the database completely).
5. If there are outstanding changes and the database needs to be upgraded the user can make any necessary manual edits to the migration and apply it.
6. At this point the database in the model can be considered in sync and the user can proceed using Migrations normally.

As @bricelam mentioned above, the database could have been created using EF6 Migrations in which case we will need to do something with the migrations history table, i.e. we either make the EF Core table compatible with the EF6 one in which case we can simply add a new row or we may have to drop/move the table and existing migration history rows somewhere else.

[divega]

Noticed this issue has the pri0 but hasn't been assigned. Assigning to @bricelam since he owns our migrations story.

[divega]

Also, note that the proposal described in a previous comment should be much easier to implement if the IModel that is the output of the reverse engineering pipeline used the same annotations and other constructs that a regular model produced by code first would use (see #2714).

[rowanmiller]

Decided we will handle this with docs. Because there are so many nuances to how you might want it to work, we don't think we can build a high level feature that just works. We may drive improvements to the building blocks as we write the docs.

dotnet/EntityFramework.Docs#91

[ToddThomson]

Is there any current guidance for porting/migrating ASP.NET MVC Identity 2 sites with existing databases ( and migrations ) to use Identity 3?

[bricelam]

The EF6 documentation Code First Migrations with an existing database is still relevant. The -IgnoreChanges parameter on Add-Migration doesn't exist, but you can just delete the contents of the Up and Down methods if you go with that option.

[bricelam]

Although there are a few more steps involved since the ASP.NET Identity schema also changed. @HaoK, is there a script somewhere to migrate from v2 to v3?

[HaoK]

No script currently, @ToddThomson can you try out the prerelease compat package to migrate (assuming your app looks fairly similar to the Identity 2 template app)?

• Run Enable-Migrations and ensure that an initial migration is created
• Install AspNet.Identity.CoreCompat and include the compat namespace to rebase your Identity POCO
• Run migrations using EF6 to update schema to V3

[theArianit]

Will Visual Studio's reverse engineer (model from database) wizard come back (anytime soon) for Asp.Net Core web apps? Cuz we need it, we Visual Studio developers

[ToddThomson]

@HaoK I take it that you can start with the generic ASP.NET MVC Web Application with Nuget package Identity 2.2.1 installed. The AspNet.Identity.CoreCompat must then be a nuget package? I cannot locate this package name. Where can this package be found?

[HaoK]

https://www.nuget.org/packages/Microsoft.AspNet.Identity.AspNetCoreCompat

[ToddThomson]

@hoak I get the following error during the install of AspNetCoreCompat:

Failed to add reference. The package 'Microsoft.AspNet.Identity.AspNetCoreCompat' tried to add a framework reference to 'System.Collections' which was not found in the GAC. This is possibly a bug in the package. Please contact the package owners for assistance.

[HaoK]

Hrm package might not be updated correctly, in the mean time you can just try manually picking up the files it includes in your project and seeing if migrations works once you have your Identity POCOs derive from them (all these do is add the V3 schema changes basically)

https://github.com/aspnet/Identity/tree/dev/src/Microsoft.AspNet.Identity.AspNetCoreCompat

[ToddThomson]

@HaoK Since I couldn't install the AspNetCoreCompat package, I tried another approach. I created an ASP.NET generic web app with user accounts. I ran the web app which created the Identity v2 tables. I then created a generic ASP.NET Core web app. I used the same database connection string for both web apps. Using the ef dbcontext scaffold command I generated a dbcontext for the v2 schema and a v2 schema migration.

With a few changes to the v2 schema migration class I obtained the IdentityV2Schema migration within ASP.NET Core web app. By commenting out the up method I updated the database with this migration.

With the V2 schema migration I then added the IdentityV3Schema migration. The resulting migration needs to be changed slightly so that the alterations are done before the new tables are created. Updating the database now with the IdentityV3Schema migration yields a Identity V3 schema set of tables.

The IdentityV3Schema migration ( v2 to v3 really ) is everything that most people would need.

To login with the ASP.NET Core web app now, it appears that the dataprotection mechanism has changed. Would you please let me know how to use Identity v2 dataprotection with v3 Identity?

[ToddThomson]

@HaoK Sorry, I just notice that you posted a link to the AspNetCoreCompat source above. I see how the v2 data protection is used.

[seekingtheoptimal]

The EF6 documentation Code First Migrations with an existing database is still relevant. The -IgnoreChanges parameter on Add-Migration doesn't exist, but you can just delete the contents of the Up and Down methods if you go with that option.

Also worth dismissing any changes applied in the Model Snapshot just to be in sync I guess?

[danielmeza]

There is a major issue when using plugable architectures , now the model configuration can be detached from the DbContext class and the configuration can be in a diferent .dll, we need to provide the same experience to in order to had the migration in the rights place,

[PilotBob]

If I could +1000 this I would. Love to see something to support this soon.

Maybe a starting migration parameter or a skip 1st [or list] or something.

Thanks.

:)

[ericsampson]

It would be great to have some sort of equivalent functionality as the old IgnoreChanges, just for simplicity/ux
Cheers