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!

[bricelam]

But wouldn't #3053 also address these concerns? I agree these scenarios are super important. I just don't think automatic migrations was the right solution due to the significant negative impact it had on the workflow of customers not using it. Don't discount their productivity. Waiting on the database every time you add a migration, and being arbitrarily restricted to only having one single unapplied migration at a time was incredibly disruptive to the development of their story.

[lakeman]

In an ideal world, I would probably use EnsureCreated in development. Develop only against test / sample data. Add migrations before testing release builds. And have automated tests covering every issue every found in production. But that's not where I am now.

Code first migrations don't fit into the development lifecycle for multiple concurrent feature branches. Migrations only work as a single linear stack. Software development isn't always linear, it isn't always waterfall.

I don't actually want automatic migrations in production. I want to try a bunch of schema changes against production data before I commit & push to master. I want somewhere to put extra migration steps, like seeding new columns, before locking those steps into the migration history.

I had a method that worked well enough (above). But can't be used easily in 3.0 since all the pub-internal types it uses are now invisible.

Now I'm thinking about creating a custom build step to auto create a dev migration assembly as part of my build. That way the process only uses supported interfaces.

[rhyous]

I look through #3053 and check on what solutions it provides.

[JasonRodman]

It still baffles me why the developers are resisting adding this feature. I have had apps in production nearly 10 years now with code first migrations in EF6 with no problems. Never had to do manual migrations and with multi-developer teams and multiple concurrent feature branches and never ran into a problem. The seed feature allows us to add in custom code to handle the rare migration issue that may come up. It makes it truly code-first by making what is in the model the same as the database. Why would I want to make my development life more difficult? You never have to worry or think about the database 99% of the time. You have a group of people all telling you they use this feature and use it successfully despite all your reservations, yet its still gets no traction. I personally cannot use EF core without this, as I suppose many developers can't because they rely on this feature. At some point you have to ask yourself would you rather exclude a subset of EF users by refusing to add this feature, or would you rather remove one of the last remaining roadblocks to adopting it? I see no option but to stay on EF6 for the foreseeable future.

[lakeman]

I've revisited my implementation of automatic migrations; https://gist.github.com/lakeman/1509f790ead00a884961865b5c79b630

Now I'm saving the model snapshot into the database in a compressed blob, so there's no dependence on recompiling the snapshot from the same work folder. I haven't tested using this approach when no existing migrations have been created, so YMMV. Seems I was mistaken about pub-internal types disappearing, since I didn't notice the automated inclusion of PrivateAssets / IncludeAssets tags in my csproj.

[ansarizafar]

@bricelam I am also in favor of Automatic Migrations. Please reconsider adding this feature may be as an option for developers who want to use this.

[walisc]

Hi all. I have been currently working on a project were this was a key requirement. In formalizing it, I decided to separate this into it's own package for others to use if they want. It builds upon suggestions from @lakeman and that of others (thank you all!).

Github (and documentation) link - https://github.com/centridsol/EFCoreAutoMigrator

Nuget package link -https://www.nuget.org/packages/CentridNet.EFCoreAutoMigrator/

p.s As said, this was built as part of another ongoing project. Have not yet had time to extensively test and optimize it. Please use this library with caution.

[c0nd3v]

Please implement this feature, its absolutely needed.

[mehrbat]

To those who are against automatic migration, how do you manage running migration against your prod database that is not publicly accessible?

[roji]

@mehrbat if you're deploying a new version of your application to your prod server, you should generally be able to run a SQL migration script from there as well.

[cocowalla]

@mehrbat I'm not against automated migration per se, I've just come to the conclusion that it's more trouble than it's worth, and there are better options.

For all but the most trivial databases, I was always running into problems with automated migrations - indexes and constraints that were missing, for example. Furthermore, the possibility for seed data took a long time to materialise, which pushed me to other solutions. As well as schema migrations, sometimes you also need to perform data migrations to account for schema changes - not sure if EF Core supports that, but the point is that EF Core always felt a long way from doing what I wanted.

I switched to using DbUp instead, where you write SQL-based migrations yourself, and DbUp executes your scripts and keeps track of what has executed.

This gives me full control over the migrations process - I can do anything I want, and ensure the conventions I want are followed. I can also ensure the SQL scripts are small, readable and commented. IME this is far superior to automated migrations. I haven't looked back, and highly recommended it.

On deploying to Production, our CD tooling (Azure DevOps) has access to the DB server, and DbUp executes the scripts there.

[mehrbat]

@mehrbat if you're deploying a new version of your application to your prod server, you should generally be able to run a SQL migration script from there as well.

How do you do that in Azure App Service? There is no place to run EF CLI, unless Azure Shell provides it? The only way I have found so far is to add a few lines of Csharp code in Startup.cs to first check the env is Production, and then to run EF migration. Dev env is publicly accessible so I can do it even from within Visual studio.

[roji]

@mehrbat I'm not familiar with Azure App Service, but I'm assuming it provides some way of interacting with your database and executing SQL against it...

Regardless, as you've written it's possible to apply your migrations at program startup - this is very different from automatic migrations. Note that doing this on your production database is discouraged, executing SQL scripts has proven to be the better way for various reasons (see the docs on applying migrations).

[roji]

More generally, automatic migrations imply that somewhere, you have a server where your application is running, connecting to the database and executing operations against it. At the very least, that machine can be used to run EF CLI, or preferably, execute SQL migration scripts against your database.

[rhyous]

@mehrbat I'm not against automated migration per se, I've just come to the conclusion that it's more trouble than it's worth, and there are better options.

I just don't see this. 99% of my automatic migrations work flawlessly.

Manually migrations is Big O (N) in regards to coding time (which is as important a resource as cpu time, if not more important) where you have N changes, you have to code the migration for all N changes. It is costly and expensive, and the industry is wasting dev hours on what we should be spending on other features.

Automatic Migrations is Big O (1). Is it perfect? No, every now and then (maybe once or twice a year) I have nudge something failed migration along. But developing and improving Automatic Migrations is the right way to go because every feature added increases the ability to be the Big O (1) in regards to coding time, whereas no matter how many improvements you make to manual migrations, it will always be Big O (n).

[lakeman]

There's a big difference between an automatically prepared draft of a migration, that you review, then apply automatically;

$ Add-Migration name
$ git add .../Migrations/*
$ git commit
...
await db.Database.MigrateAsync();

And a migration that is automatically generated and applied immediately, without any user interaction, directly into a production database.

The alternative is not hand crafted migration scripts.

[ansarizafar]

I totally agree with @rhyous

[mehrbat]

More generally, automatic migrations imply that somewhere, you have a server where your application is running, connecting to the database and executing operations against it. At the very least, that machine can be used to run EF CLI, or preferably, execute SQL migration scripts against your database.

@roji No necessarily. Your app could be hosted in an AWS lambda function where you cannot connect to run any CLI.

[lakeman]

So you write another lambda function that just runs Migrate....

[roji]

There seems to be a bit of confusion as to what "automatic migrations" means.

• After making a model change, migrations need to be added (dotnet ef migrations add). This means that generated files are scaffolded into your project, and these go into source control (see managing migrations).
• Once a migration has been added, it can be applied in various ways: by generating a SQL script and running that, by executing dotnet ef database update, or by running Migrate() in your program startup, or elsewhere (see applying migrations).
• This last option basically applies migrations "automatically": any migration that has been added in the project will automatically get applied when you deploy a new version of your application. We discourage this method (see the docs) but it's available.
• The term "automatic migrations" refers to the capability that EF6 has, which means that migration files aren't generated at all and don't go into source control - that's very different. This feature has been intentionally dropped in EF Core, and we believe it's especially inappropriate for production databases. The main problem with automatic migrations is that there's no way to inspect and possibly modify them when needed, before applying them. For example, if you rename some property, EF will usually understand that you want to drop the old column and add a new one - the user has to edit the scaffolded migration to replace that with a column rename, but that's not possible with automatic migrations.

I hope this reduces some of the confusion in this issue. To summarize, with EF Core you do need to add migration code to your project (that's what EF6's automatic migrations obviate), but after that you're free to disregard what's inside it and have the migration be automatically applied.

[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 https://github.com/dotnet/efcore/issues/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...