Efcore: 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 https://github.com/aspnet/Identity/issues/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.

https://github.com/aspnet/EntityFramework.Docs/issues/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.

[ToddThomson]

@HaoK Just to be sure: Is the PasswordHash compatible between Identity v2 and v3?

My goal is to migrate existing v2 Identity tables to v3 and to be able to login with the ASP.NET Core Identity.

EDIT: PasswordHasherCompatibilityMode.IdentityV2 is required.

[HaoK]

Correct you need to use the backwards compatible password hash mode.

[ToddThomson]

@HaoK Perfect. The last piece of the puzzle was that the NormalizedUserName needed to be set ( perhaps a bit inconsistent as the field NormalizedUserName is not required ) for the call to PasswordSignInAsync() to work. I'll need to either populate the "normalized" fields or figure out how to do this in the v2 to v3 migration.

[HaoK]

The normalized name is simply the toupperinvariant of the username by default.

[ToddThomson]

@HaoK Yes. I'm just trying to determine how to best generate and store the normalized fields in Roles and Users when I migrate the Identity tables from v2 to v3.

[bricelam]

Note, for SQLite I've added some guidance on how to handle this at runtime (i.e. using db.Database.Migrate()) in a comment on #6273.

[ajcvickers]

Putting this in 2.0 for now not because we will necessarily do it, but because it relates "update from database" feature.

[EdwardL75]

First I want to say how irritating it is to do searches on database-first issues and most every response is code-first. Also even the few you can find that answers questions about database-first talks about migration, that is not a solution at all. Code should revolve around data not the other way around.

My issue here is that with a database already setup for ASPNETUSER table and corresponding tables does not have the fields NormalizedUserName or NormalizedEmail. So my question is in the program code is there anyway to override the class ApplicationDbContext : IdentityDbContext<ApplicationUser> from needing these as parameters? I have already completely removed the Migrations folder that is preloaded with the template and removed protected override void OnModelCreating(ModelBuilder builder) table creation code is included.

I am creating a ASPNETCORE web app with C#. Thanks for any help in this.

[divega]

@EdwardL75 I remember @HaoK did an exercise to identify additional steps necessary to upgrade from a database that doesn't have the normalized columns (i.e. from an ASP.NET Identity 2.0 database to ASP.NET Core Identity). He should be able to provide some details.

[EdwardL75]

Thanks but I prefer NOT to change the database structure, but implement some override code that make use of EF without needing to have those "normalized" fields present.

[HaoK]

You can see what I did here for reference: https://github.com/aspnet/Identity/blob/dev/src/Microsoft.AspNet.Identity.AspNetCoreCompat/IdentityUser.cs

Basically we pointed the new columns at the old ones

[divega]

@EdwardL75 Understood. I think the approach that @HaoK is pointing to does that, but if I remember correctly, it had some disadvantages, e.g. lookups will be resolved in memory. Anyway, if you want to discuss it any further I suggest you create an issue in http://github.com/aspnet/identity/issues.

[EdwardL75]

So using that code in a data/model class with my project I have the
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
but I get error in the ApplicationDbContext class of:

The type 'Laier_It.Models.ApplicationUser' cannot be used as type parameter 'TUser' in the generic type or method 'IdentityDbContext'. There is no implicit reference conversion from 'Laier_It.Models.ApplicationUser' to 'Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityUser'. Laier-It..NETCoreApp,Version=v1.0

Besides that issue. I have another issue that maybe you have come across before. When I setup a generic ASPNETCORE web app with Individual user account (so EF references are pre-populated), after getting my Models populated from my database with the command Scaffold-DbContext. I try to scaffold a new controller and it errors out saying "There is no entity type of on DbContext"; even after building the solution.

Answer to my second question: When using Scaffold-DbContext the MyDbContext class does not generate the constructor taking the parameter DbContextOptions<MyDbContext>. Once I added this scaffolding no longer gave me errors. So another question remains why would this not be generated if it is needed?

[bricelam]

@mheilimo had a good idea to help with this scenario when using Scaffold-DbContext. We could add an additional -BootstrapMigrations flag that scaffolded an InitialCreate migration and marked it as already applied in the database.

[bricelam]

We closed #8645 today, but we'll need to re-consider it as part of this work.

[louislewis2]

Hi guys, is there any guidance on this topic yet?

My scenario is simply, that a product I am working on, always connects to an existing database.
The application is meant to simply add a single table. Migrations fail stating the database already exists.

I am using the latest EF Core bits.

Thanks

[ajcvickers]

@louislewis2 If you have an existing database and a model that maps to it, then scaffold an initial migration, but remove all the code in the Up and Down methods before applying it. This will get the model snapshot and history table in a state that is in-sync with the database. Now you can move forward with changing the model and applying migrations as normal.

[fizxmike]

This is a solved problem in Django ORM using --fake-initial flag when applying migrations. @bricelam is your -BootstrapMigrations idea equivalent?

[bricelam]

It’s similar except we’d scaffold the initial migration (and fake-apply it) for you.

[fizxmike]

@bricelam sounds cool. I have some IMHOs to add...

Lets say I have a legacy pre-production (clone of production) database that I dotnet ef dbcontext scaffold from. I create the initial migration with your proposed flag dotnet ef migrations add InitialCreate -BootstrapMigrations, update the model, then create a new migration and apply it no problem.

How would I move to production? I'd expect to just be able to run dotnet ef database update targeting the legacy production database in a continuous delivery task. This would seem to work fine using the current workaround (comment out Up and Down methods). But the workaround prohibits me from easily creating a DB from scratch (for integration testing).

I'd expect dotnet ef migrations add InitialCreate -BootstrapMigrations to not touch the database. And if it did, have a way to reproduce it's "fake-apply" behavior, perhaps with a new flag here. But, if fake-apply functionality were available, it seems -BootstrapMigrations becomes redundant and of limited use (case where I'm dev'ing in production?).

Just having a --fake-apply flag is sufficient for the desired behavior (which is why I think Django settled with the --fake-initial approach). Sure, your first deployment with the new ORM will require a special flag, but all future migrations will work fine (including creating test databases from scratch).

... of course, I could be misunderstanding something about EF or your idea. Please feel free to correct my thinking.

[marchy]

@bricelam is the '-BootstrapMigrations' idea being planned for any particular EFCore release yet?

The community needs SOME official answer for all this, and the current one-paragraph guidance in the docs is completely unsatisfactory for this.

We need something that supports both initial database creation and subsequent incremental migrations.

[rbluethl]

We're having the same problem with an existing database. When trying to perform context.Database.Migrate() on an existing database, the error

There is already an object named '...' in the database.

is thrown. In this issue I saw that the only option is to add a record to the __EFMigrationsHistory table manually. Is this still the case? It would be very cool to have a better option available. Thanks.

[daveaglick]

Another related scenario that I'm looking at now and that might be handled in docs is running EF5/6 and EF Core side-by-side for a while.

I have a large app that I'm looking to port bit by bit. The intent is to continue with the existing EF model as authoritative (I.e., the one with migrations) while using a duplicate EF Core model in a new app that points to the same database. Verifying model parity can be done with unit tests and reflection, but some guidance on how to make EF Core point to an existing database that matches it's data model without applying migrations (because those are handled and applied by the legacy app/EF instance for now) would be helpful.

[ajcvickers]

See also scenario in #15725

[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,