Automatic Migrations

[netcore-jroger]

Usually we use EF tool to migrate the changes. but I want to know how to the using C# code auto migrate the change, and do not use EF tool everytimes.

Is there any way to do it ?
Thanks!

[netcore-jroger]

@Muchiachio My code is like what you said above.
However, you still need to use the command dotnet ef migrate add migrate_name to generate snapshot files .
And I dot not want to use dotnet CLI tool. just want use C# code in my application start.
Is there any way to do it ?
Thanks !

[bjorn-ali-goransson]

I can imagine that the logic required to generate the migration procedure doesn't event exist inside EF Core itself, but inside that second NuGet package (so using it automatically may be hard?).

Maybe using Roslyn, one could compile it dynamically ... but then again, what would happen with the EfCore Migrations table ...

[bjorn-ali-goransson]

In my view, this feature request (if not already available!) should be directed to the Tools-package. Some method could be defined on the classes there. I read that the auto-migrate feature was not included by design, though, so don't count on it being implemented...

[bricelam]

@Oger-Me Are you trying to implement automatic migrations (like we had in EF6) where no Migration class is scaffolded and stored in the project? Or is your goal to scaffold the Migration class without using the Add-Migration/dotnet ef migrations add command?

[bricelam]

To generate a migrations file, the logic lives inside the Microsoft.EntityFrameworkCore.Design package:

using (var context = new MyDbContext())
{
    var services = ((IInfrastructure<IServiceProvider>)context).Instance;
    var codeHelper = new CSharpHelper();
    var scaffolder = ActivatorUtilities.CreateInstance<MigrationsScaffolder>(
        services,
        new CSharpMigrationsGenerator(
            codeHelper,
            new CSharpMigrationOperationGenerator(codeHelper),
            new CSharpSnapshotGenerator(codeHelper)));

    var migration = scaffolder.ScaffoldMigration(
        "MyMigration",
        "MyApp.Data");
    File.WriteAllText(
        migration.MigrationId + migration.FileExtension,
        migration.MigrationCode);
    File.WriteAllText(
        migration.MigrationId + ".Designer" + migration.FileExtension,
        migration.MetadataCode);
    File.WriteAllText(migration.SnapshotName + migration.FileExtension,
        migration.SnapshotCode);
}

[bricelam]

Implementing automatic migrations is a lot more involved, but I'd be happy to give you the high-level steps if your interested.

[netcore-jroger]

@bricelam Yes. I need. Thanks for your help.

[bricelam]

To implement automatic migrations, you need to:

1. Compare the current model to the previous model (see step 4) using the MigrationsModelDiffer
2. Convert the resulting operations into SQL using the MigrationsSqlGenerator
3. Execute the SQL using the MigrationCommandExecutor
4. Store a serialized version of the model in the database. In EF6, we stored a gziped copy of the EDMX in the [__MigrationHistory].[Model] column. Note, there is currently no serialized model format in EF Core.

This will give you forward-only automatic migrations. You will not be able to use this with explicit migrations, and you won't be able revert an automatic migration--this would require even more implementation.

For some details on why we decided not to implement automatic migrations, see my EF Core Migrations: Design-time post.

[bricelam]

We would, however, like to improve the workflow for local-only, iterative model changes. (See #3053) This is one area where automatic migrations really shined.

[divega]

Triage: closing as we believe the question is answered. Feel free to reactivate if it isn't.

From the perspective of automatic migrations as a feature, we are not planning to implement it in EF Core as experience has showed code-base migrations to be a more manageable approach.

[jemiller0]

Can you add a line to the feature comparison chart at https://docs.microsoft.com/en-us/ef/efcore-and-ef6/features stating that automatic migrations aren't supported in EF Core? The chart specifies that migrations are supported on both, but, doesn't specify that automatic ones aren't.

[bricelam]

@jemiller0 Can you submit a new issue on the aspnet/EntityFramework.Docs repo?

[cigano]

@divega What do you mean with "more manageable approach"?

Suppose I have a team with 8 devs, all of them creating models simultaneously in a fresh project. By your assertion, all of them will have to add migrations and then the sunny sunday will occur when this code is merged: The Update-Database will run succesfully in the machine of the 8 devs.

Also, I missed the AutomaticMigrationsDataLossAllowed from EF6 in this new effort. Again, in a development environment, the test data can be discarded with no problems. Is there something planned to replace this feature?

[divega]

@cigano it means that as a user there is less magic and more control, e.g. and you get a chance to decide whether the migration that EF (EF Core in this case) guessed was the right one is actually what you want to happen in the database. It also means simpler and more maintainable code in the EF Core codebase because we don't need to deal with corner cases in which there is a mix of explicit and automatic migrations.

In general when you have multiple developers working concurrently with intermediary versions of the model and the database it should be fine for them to work independently for a while creating their own (explicit) migrations. You should consider rebasing the migrations that you push to source control so that they represent the right migration steps from the latest production version of the database to the current state of the model. I don't mean this be the complete guidance for working with migrations in teams. @rowanmiller may be able to provide links to more complete guidance.

Also, I don't meant to say that automatic migrations were never a nice thing. There is a sweet spot for them. It is only it is the general consensus in the EF team that overall the benefits do not compensate for their pitfalls.

Feel free to create a new issue if there are specific aspects of automatic migrations that you miss.

[bricelam]

For more context, here's an excerpt from a blog post I wrote in 2014:

Automatic migrations worked by diffing the last-known model against the current model. The last-known model may be stored in either the last explicit migration (i.e. non-automatic) or in the database if it comes from another automatic migration. Because the last migration may not be an explicit migration, we always had to look in the database. Since we always looked in the database, we wouldn’t allow you to scaffold multiple migrations without first applying them. Automatic migrations also required us to store both the source and target models for each explicit migration that succeeded an automatic migration. Are you starting to see how these may have hindered the design-time experience?

By removing automatic migrations in EF Core, we’re able to…

• Stop storing model snapshots in the database
• Stop querying the database on Add-Migration
• Allow Add-Migration without applying the last migration
• Stop storing source model snapshots in the migrations

To see just how awesome all of this is, try the following.

Add-Migration M1
# (Tweak the model)
Add-Migration M2 # Would fail on EF6
# (Tweak the model)
Add-Migration M3
Update-Database # Applies M1, M2 & M3

[bricelam]

And I know I repeat this a lot, but please upvote and comment on #3053 for a more streamlined workflow that doesn't require you to add a migration with each model change. You'd think it would have way more than 4 votes with all the comments we get on this issue...

[Appelg]

I am an returning EF user. How am I supposed to do early development/prototyping where my model changes locally 50 times per day?

Run add migration and update database each time?

[roji]

@Appelg #3053 tracks improving the early development/prototype experience in EF Core, please upvote that. We're currently doing planning for EF Core 7.0, it's likely that this will make it in.

In the meantime, one way to to prototyping is to avoid Migrations altogether and simply use EnsureDeleted/EnsureCreated. When you reach a stable point, you can then start using Migrations.

[rhyous]

I'm pretty disappointed this didn't get resolved yet. Especially after I explained the Big O(1) vs Big (N) problem. I thought surely that would help expert developers understand and turn the light bulb on for them. Alas.

Here are some responses.

Automatic migrations worked by diffing the last-known model against the current model.

First, I don't see why this is a problem. Perhaps it is a problem for Managed Migrations and not for Automatic Migrations, but they were coupled, so, they affected each other?

Assuming it is a problem (I don't think it is), storing the code model state was a design decision that you didn't have to make. You don't have to diff the code model. It would be much easier to diff the database table state:

1. Get the database table configuration state as it exists in the database.
2. Generate the database table schema state for the new entity changes (as you have access to the new entity model).
3. If the database table states are the same, do nothing, if they are different, apply diff changes.
   No storing of the last-known code model would be needed.

(Note: Actually, this feature should be implemented by the database systems themselves. Database systems should have state-based schema migrations but they don't. If they did, all anyone would have to do is hand the SQL system a new state.)

Yes, database state comparisons would introduce limitations, but at the same time, we can address those limitations one at a time.
Limitations:

• Renaming a table (this was their with code state so it isn't new). We need an PreviousEnityNameAttribute("Table1") and a PreviousPropertyNameAttribute("Property1") so we can decorate our C# code class or property with it be free to rename an entity or property without dataloss.
• Handling dataloss. I had custom code to allow data loss migration or not. Half the time, there wasn't dataloss.
• There were bugs with I had a string property without a max length. I add a max length of 100 (the largest string no that db column is 75 characters, EF would say there was dataloss. This would be easy to fix, but we would have to query the column for the current max length.

The last-known model may be stored in either the last explicit migration (i.e. non-automatic) or in the database if it comes from another automatic migration. Because the last migration may not be an explicit migration,

What does "may not be an explicit migration" mean? With automatic migrations, none of the migrations are explicit. Are you saying you supported both Managed Migrations and Automatic migrations in the same project? I'm totally for eliminating that feature. Both should be separate features meeting their separate needs.

we always had to look in the database.

Always? Again, sounds like coupling of two features.
You always have to touch the database with a migration. So if we are using automatic migrations, and we are already touching the database, who cares if EF core queries it? This argument doesn't hold up against scrutiny.
You have to touch the database to change the database to do managed migrations.

Again, you can avoid this for Managed Migrations, just don't do it if Manual Migrations is used.

Making a choice to be Big O (N) in regards to coding time instead of Big O (1) regards to coding time is not a good architectural choice just because it means you don't have to read a database.

As for the Big O(N) vs Big O(1) in regards to coding time, this is the equivalent of saying that an algorithm shouldn't do the preprocessing needed to be Big O (1) because preprocessing is more work and more code than the algorithm without preprocssing.

Since we always looked in the database, we wouldn’t allow you to scaffold multiple migrations without first applying them.

What do you mean by scaffold?
That sounds like managed migrations.
Why would managed migrations be affected by automatic migrations? Again, I ask: Were they tightly coupled?

Automatic migrations also required us to store both the source and target models for each explicit migration that succeeded an automatic migration. Are you starting to see how these may have hindered the design-time experience?

Not at all. What design-time experience was hindered? I write code, it migrated. I was never hindered.
What hinders my design-time experience is having to stop writing to code to now work on managed migrations, including storing those migrations in my source, checking them in, making them part of code reviews taking other's time, and letting automatic migrations code build up until the migrations are about the same size as the rest of my source themselves, but no, I can solve that, by taking even more of my dev time, to do a cleanup every once in a while.

By removing automatic migrations in EF Core, we’re able to…

• Stop storing model snapshots in the database

If you diffed the table configuration state, you never would have had to do this in the first place.
Disliking a past architecture is not an explanation of killing a feature. Just of re-architecting.

• Stop querying the database on Add-Migration

Was add-migration used by both managed migrations and Automatic Migrations?
If so, sounds like the problem wasn't automatic migrations, but a coupling between the two.
I won't patronize you with why coupling is bad, but if my assumption is correct (and I could totally be wrong) you misdiagnosed the problem as being Automatic Migrations when tight coupling between managed migrations and automatic migrations was the actual problem.

Managed Migrations and Automatic Migrations shouldn't be coupled. Maybe they could share some code where it makes sense, but they should be mutually exclusive features. One or the other.

• Allow Add-Migration without applying the last migration

This is a managed migration thing.

• Stop storing source model snapshots in the migrations

Reminder, this was a design choice. It doesn't have to be done this way?

To see just how awesome all of this is, try the following.

Add-Migration M1
# (Tweak the model)
Add-Migration M2 # Would fail on EF6
# (Tweak the model)
Add-Migration M3
Update-Database # Applies M1, M2 & M3

Again, you are mixing Managed Migrations and Automatic Migrations together in most of your arguments, which really doesn't make sense unless the features were so tightly coupled that you can't separate them when you think about them.

I have never once called Add-Migration. Nope. Not once. That is something you would do for Managed Migrations.
With Automatic migrations, I have never, ever, needed it or used it.

I am migrating a project from WCF EF6 to WebApi EF Core and .Net Core. I am done with everything but the database layer. It is going to take longer to move to ef core than to move from WCF to WebApi because of this missing feature. Not to mention, it will significantly degrade my project's coding experience to go from Big O (1) coding time with automatic migrations to Big O (N) coding time with managed migrations.

I really think the problem was misdiagnosed. Because your post reads like "Automatic Migrations got in the way of Managed Migrations". So if you diagnose the problem as that, then I can see why you want to eliminate Automatic Migrations. However, if you diagnosed the problem as "Automatic Migrations was tightly coupled to Managed Migrations" it no longer makes sense to eliminate Automatic Migrations to solve the problem. It makes sense to decouple them.

[jzabroski]

Again, you are mixing Managed Migrations and Automatic Migrations together in most of your arguments, which really doesn't make sense unless the features were so tightly coupled that you can't separate them when you think about them.

He isn't. What he is actually trying to tell you is the "last known model" can become corrupt. The moment you have to write a migration that can't be done using automatic migrations, you risk corruption.

I just worked with a software vendor who learned this the hard way. I imagine Brice and the rest of the EF team have similar war stories where customers don't understand these footguns.

[rhyous]

I just worked with a software vendor who learned this the hard way. I imagine Brice and the rest of the EF team have similar war stories where customers don't understand these footguns.

I appreciate your comment. However, your argument is a logical fallacy. Anecdotal. Some else experienced a problem with a feature so that feature is bad. I've seen way more mistakes in managed migrations that have caused corruption because they are touched by a human, who is more subject to typos and human errors, than I've seen with Automatic migrations. So by your argument, we should remove managed migrations, too. Fortunately, we can recognize fallacies and understand why they weaken arguments.

Fortunately, anecdotal corruptions for both managed and automatic migrations are solved not by eliminating the feature, but by not going directly to prod, and instead, having the build pipeline tested first, and of course, a database backup on prod before the change deploys just in case your test environment missed something.

It seems EF has made its decision (which I find logically a poor decision, especially taking Big O in regards to code and coding time into account), which really means my framework is leaving EF or I have to write my own automatic migrations feature.

[nicholasyin]

Don't know why the old-school DropAndCreateWhenModelChanges mechanism was abandoned at all. During development I could change the model dozens of times every day. I need to manually create migrations every time?

[ajcvickers]

@nicholasyin You can get the same behavior with EnsureDeleted followed by EnsureCreated. Database initializers had a lot of complexity and overhead even if people didn't use them, so we opted to make it something the developer explicitly decided when to do.

[nicholasyin]

@nicholasyin You can get the same behavior with EnsureDeleted followed by EnsureCreated. Database initializers had a lot of complexity and overhead even if people didn't use them, so we opted to make it something the developer explicitly decided when to do.

Well, what you are suggesting is the old DropAndCreateAlways, while I was expecting the DropAndCreateIfModelChanges which I feel quite convenient in dev stage.

[ajcvickers]

@nicholasyin True.

[jzabroski]

@nicholasyin You can probably mimic it with a MSBuild-style "fast up to date check", by serializing fast up to date metadata somewhere. In that way, the logic is outside of your main code line and relegated to a development tool, so there is effectively zero chance it gets packaged with your production code. I think that is a lot cleaner.

In either case, DropAndCreateIfModelChanges is not really "automatic migrations" but a related concept.

[nicholasyin]

@nicholasyin You can probably mimic it with a MSBuild-style "fast up to date check", by serializing fast up to date metadata somewhere. In that way, the logic is outside of your main code line and relegated to a development tool, so there is effectively zero chance it gets packaged with your production code. I think that is a lot cleaner.

In either case, DropAndCreateIfModelChanges is not really "automatic migrations" but a related concept.

I don't understand what you meant. lol. Anyway, I've upvoted the #3053. But considering that thread was originally brought up in 2015 and we still don't have this feature, I won't have too much expectations on it. C'est la vie.

[jzabroski]

I don't understand what you meant. lol.

MSBuild tasks operate on out-of-date files. It's called a "fast up to date check". You can serialize the state of all your database changes by checking the particular folder where you put your entities, assuming you have semantic namespaces and a clean project structure. If you have a crazy object model where base types are in a different location, it might be a bit harder - you could also check if your entities csproj has changed and assuming your PackageReference statements reference a concrete version, a fast up to date check on your csproj will catch transitive reference changes via base types outside your Entities.csproj.

Then you just add a post-Build target that checks if any entities have changed (this is equivalent to detecting Model Changes). If they have, call drop and create via a custom target DropAndCreateIfModelChanges. It should be a post-Build event because you may be using the generated database for unit tests or debugging or both. Thus, you want it to come before running tests or debug.

I don't know exactly how much time dropping and creating each time costs you, but if it's significant, it may be worth automating this with MSBuild.

[nicholasyin]

Currently I'm using context.Model.ToDebugString to detect model changes. I got this workaround from some other thread which was also a couple of years ago. It's been working moderately fine.

[jzabroski]

Got it. Then you can use the nuget package Verify to create a snapshot of the debug string output, and package that up as a command line tool, and make it a MSBuild target. It would be a couple of lines of code and avoid having this logic in production code paths.

[roji]

We had an additional discussion around this in the team, dropping a summary here.

People sometimes mean different things when they say "automatic migrations". Three meanings we've seen are:

1. EF6 automatic migrations, which save the model in the database. It's been amply explained above why we're not going to do that.
2. The ability to automatically execute migrations e.g. when the application starts up. This is already possible with context.Database.Migrate() - see the docs on this.
3. Reverse-engineering the database to an EF model, comparing that to the latest code model and applying the differences; a variant of this (or way of thinking about it) is to do a schema diff in SQL. Either way, no model or state is saved either in the database or in code - we just sync the database to whatever your code looks like at a given point in time.

That last bit deserves a bit of explanation... We do believe that such a "schema diffing" approach makes sense in the early stages of application development, where the schema evolves very quickly and the full-blown Migrations approach is too heavy. This is tracked by #3053. One main reason why we don't currently support that, is that there's no way to evolve from this mode into the regular Migrations approach, once your application has reached some level of maturity. That is, it should be possible to take an existing database without Migrations, and start using Migrations with it.

The use of the "schema diffing" approach for production use is something we don't believe in. The main problem is that migrations are inherently dangerous operations, which can easily cause data loss. The prime example here (but there are others) is column or table renames: if you rename some property from Foo to Bar, EF cannot assume that this is a rename, will drop Foo and create Bar, causing all the data in Foo to be lost.

Migrations mitigate this to some extent by logging a "possible data loss" warning when the migration is created; if the change is applied automatically when the application is started, there's no place for that to happen. In general, developers are expected to inspect the migrations they created, to ensure that they correspond to the intent and don't cause data loss.

It's true that tools exist out there, which allow specifying that something is a rename; EF could allow that by having something like an [OldName] attribute on your property. However, this attribute needs to stick around forever, since you don't know when all database have been migrated; and things get particularly messy if the column is e.g. renamed back. At the end of the day, the scaffolded migration code itself is the place for you to tell EF about the rename - it's clean, doesn't pollute your model, works well if the column is renamed back, etc.

Hopefully this sheds more light on our decision-making in this area. If you're looking for a better startup/prototyping solution, follow #3053. If you're finding yourself wanting automatic migrations for production use, please consider seriously why it is that you're trying to avoid regular Migrations, and how you'd expect the above problems to be dealt with.

[mahop-net]

I'm still using the old EF6 because "EF6 automatic migrations" is not supported in the new version. I had never one of the problems described above... "EF6 automatic migrations" is just a big time saver!

[rhyous]

@mahop-net Same for me. We used automatic migrations in production for 8 years without a problem.

I tried to explain it above using Big O. There is Big O for processing time and memory time, but those aren't the only resources that matter. Big O for coding time, build time, etc.. Migrations are Big O(n) for coding time and build time. With n changes, you need n migrations. Automatic Migrations is Big O(1) for coding time, while migrations have to be hand-coded for every change. They hang around in your code base forever. Slowing every future build forever, etc. I'm sure there is a way to move these to a library that doesn't build every time, but then we are spending even more dev time working on something that just isn't needed in the first place.

I love their option 3 idea! I started looking at this a while back.

1. For each entity, get attributes.
2. Make sure table exists.
3. Make sure each column exists of the correct type, and size, etc.
   (Check for data loss on column type/size change)
   (support renames of columns)
4. Make sure each constraint exists
5. Other things exists ...

As for their concern: "One main reason why we don't currently support that, is that there's no way to evolve from this mode into the regular Migrations approach, once your application has reached some level of maturity."

I'm guess their concern is that checking the entire database schema when there are no changes is their concern. This is probably why they stored the cache in the database to eliminate this big startup hit.

However, code first from an existing database exists. And we can start migrations from that, so I don't know what they man that there is no way to do this. If it is cloud, there is usually only a few databases managed by the devs, so this isn't an issue. For on-prem software, usually, software has versions and we can easily tell a customer to upgrade to version X (last automatic migrations version) before going to version Y (first version using migrations), or even include the process to update to X before going Y in our installer. At this point it isn't a problem the EF team should be trying to solve, though have such instructions in the docs would be welcome.

[mahop-net]

@rhyous thanks for your answer! Speed of migration at startup is already a big problem in old EF6 if the database is bigger than tiny. We solved it with a table called DBVersion, with one single record and column. The code checks the DB Version with SQL and ADO an only if not matching, it runs throught auto migrations.

I will look in the Microsoft.EntityFrameworkCore.Design package during the next weeks. Maybe this package can be used to build migrations at runtime on the fly...