Efcore: 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:

``` C#
using (var context = new MyDbContext())
{
var services = ((IInfrastructure)context).Instance;
var codeHelper = new CSharpHelper();
var scaffolder = ActivatorUtilities.CreateInstance(
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.

[cigano]

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.

I strongly disagree with this. What happens in general is: every developer, after the Source Control Sync, has to delete all his/her migrations, delete the database and start over. Totally counter-productive.

Imagine this happening every beginning of the working day. With advanced seeding, with thousands of test records, it takes almost half hour to drop the yesterday's database, create an explicit migration and then seed the database again.

You should consider rebasing the migrations that you push to source control so that they to represent the right migration steps from the latest production version of the database to the current state of the model.

That's not my point. Automatic Migrations are not for production environment. My point is only the development, the team creating the Models quickly and the system being produced in an overwhelming speed. I can do this in EF6 with my team. With explicit migrations, this will not be possible.

It is only it is the general consensus in the EF team that overall the benefits did not compensate for their pitfalls.

Yes, they compensate. I would like to strengthen this point. Dropping this feature will be an evident kickback. The productivity in EF6 for starting systems is wonderful. If this feature shouldn't be part of the native set of features of EF Core, at least it would be nice to have the possibility to write an extension to have this feature in EF Core.

[divega]

I strongly disagree with this. What happens in general is: every developer, after the Source Control Sync, has to delete all his/her migrations, delete the database and start over. Totally counter-productive

I don't understand why creating and applying an explicit migration would be any different performance-wise from an automatic one.

[cigano]

I don't understand why creating and applying an explicit migration would be any different performance-wise from an automatic one.

The problem here is not the performance, but the rework.

Assuming again the 8 devs scenario. Each one open VS, make a pull and the migrations from all the 7 devs will be merged into the project. The dev runs the Update-Database command and the command breaks because the state that each database was at that point, for each dev machine, was different from each other.

Two options here: try to generate another migration to fix the database state (rarely works) or delete the database, generate another initial migration and then apply to the database. I didn't find the Seed workflow, but I believe that is the next step.

Now, suppose the system has a database with streets from an entire state of US or even Brazil, and you have to Seed it again, everyday, because you dropped the database with previously seeded information. It can take up to 5 minutes, okay, but there was - let's guess - 30 minutes spent to sync the code, delete the previous migrations, generate a new initial migration, update the database and seed the initial data, and I even didn't mention the custom test data from previous day is lost. If this data is important, the dev has to make a SQL dump from the database and then fix missing or added columns.

@divega Do you see my point now?

In EF6, this problem doesn't exist. Automatic Migrations, Update-Database and - magic - problem solved. Less than one minute. In this new approach, I spend 4 man-hours everyday because there's a general consensus in the EF team that overall the benefits did not compensate for their pitfalls.

There's no consensus between the Team Leaders - like me - that use this technology in the day to day.

[Jupakabra]

@cigano how did you now run into problems in EF6? On our team we do have a problem of different devs making migrations on different database states, always resulting in last migrations regeneration (on a branch merge).

[bricelam]

Keep in mind, we also have #3053 which would bring back some of the convenience that automatic migrations offered.

[cigano]

@Jupakabra Well, the migrations configuration is like this:

    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
        ContextKey = "MySystem";
    }

For each dev, EF6 will generate a custom migration to make the database state the same at the merge point of a branch or, in my case, in the beginning of the day. It's common to have 4 or more devs working on a branch. In the beginning of the day, all of them should sync the code and run

PM> Update-Database

EF6 does the dirty work. All the databases should be the same, except for situations like changing the column type or add not null columns in tables with data inserted. In these cases, dropping the database and start over is the only solution, but is about 10% of all cases.

@bricelam presented a good solution in #3053, and I wondered something like this. If I got the point, this feature will satisfy my needs.

[JasonRodman]

The lack of automatic migrations is the only thing stopping my team from upgrading to EF Core. I am stuck in EF6 until this is available. Code based migrations are a non-starter for my organization.

[ajcvickers]

@JasonRodman Current plan is to not implement automatic Migrations in EF Core--hence this issue is closed. That could change with enough feedback, so can you provide details of why code-based migrations are a "non-starter" and how automatic migrations would be better?

[JasonRodman]

@ajcvickers That is quite bad news. I had high hopes of using EF Core. We moved from L2S to EF6 for that very reason, it was easy to keep our database schema in sync with our domain models. We build our domains using DDD patterns and make hundreds if not thousands of edits to our domain as we build it out or change over time. Mix in a team all making changes on that same model, keeping track of those changes becomes a maintenance nightmare. Each developer would have to create migrations for each little change they made, which add up over time. And with the amount of experimentation that goes into building a DDD domain model coupled with TDD patterns that encourage tinkering, this is a burden we don't want. Our domain model drives the schema and EF6 allows us not to have to think about persistence, which is one of the main principles of domain driven design. So if our choice is to go to EF Core and lose that ability or stay with EF6 and keep it, the choice is easy. I would rather migrate to another data access technology that supports it like NHibernate or even a NoSql solution than have to manage schema. I would be willing to bet there are a large portion of developers out there that see this as one of the main roadblocks to adoption of EF Core if you were to put out a survey. I wish you would consider adding it at some point in the future since the rest of EF core looks amazing. Until then its unfortunately off the menu for us.

[ajcvickers]

@JasonRodman Thanks for the feedback; much appreciated.

[bricelam]

⚠️ Warning: This is unsupported.

So you got me thinking (always a dangerous thing) that we actually already have some (internal) components that could help with this scenario.

The basic workflow is:

1. Reverse engineer a model from the database
2. Compare that model to the current EF model
3. Apply migration operations to the database to bring it up-to-date with the EF model.

There is, of course, a lot of unknowns here so you'd need to test it thoroughly and put in the necessary safeguards before using it, but here's the code to get started:

using (var db = new MyDbContext())
{
    var reporter = new OperationReporter(handler: null);
    var designTimeServiceCollection = new ServiceCollection()
        .AddSingleton<IOperationReporter>(reporter)
        .AddScaffolding(reporter);
    new SqlServerDesignTimeServices().ConfigureDesignTimeServices(designTimeServiceCollection);

    var designTimeServices = designTimeServiceCollection.BuildServiceProvider();

    // TODO: Just use db.Database.EnsureCreated() if the database doesn't exist
    var databaseModelFactory = designTimeServices.GetService<IScaffoldingModelFactory>();
    var databaseModel = (Model)databaseModelFactory.Create(
        db.Database.GetDbConnection().ConnectionString,
        tables: new string[0],
        schemas: new string[0],
        useDatabaseNames: false);

    var currentModel = db.Model;

    // Fix up the database model. It was never intended to be used like this. ;-)
    foreach (var entityType in databaseModel.GetEntityTypes())
    {
        if (entityType.Relational().Schema == databaseModel.Relational().DefaultSchema)
        {
            entityType.Relational().Schema = null;
        }
    }
    databaseModel.Relational().DefaultSchema = null;
    databaseModel.SqlServer().ValueGenerationStrategy =
        currentModel.SqlServer().ValueGenerationStrategy;
    // TODO: ...more fix up as needed

    var differ = db.GetService<IMigrationsModelDiffer>();

    var operations = differ.GetDifferences(databaseModel, currentModel);
    if (operations.Any(o => o.IsDestructiveChange))
    {
        throw new InvalidOperationException(
            "Automatic migration was not applied because it would result in data loss.");
    }

    var sqlGenerator = db.GetService<IMigrationsSqlGenerator>();
    var commands = sqlGenerator.Generate(operations, currentModel);
    var executor = db.GetService<IMigrationCommandExecutor>();
    executor.ExecuteNonQuery(commands, db.GetService<IRelationalConnection>());
}

[jtheisen]

@ajcvickers I would also like to throw my support for automatic migrations out there.

In most cases migrations can be created correctly automatically. Currently, with EF6, all I have to do after such a model change is Update-Database and it's done.

With EF Core, I will have to precede that with an Add-Migration somename.

Much of the nuisance comes down to these two things:

• I need to give the migration a name. Which will be something like "attempt_number_9".
• When I'm satisfied enough with the model changes that I commit to them, then - if I'm already forced to have those migrations hang around - I'd really like to merge them and then give the merged one a sensible name.

So if the naming was optional and there was a convenient way to merge all latest migrations up to a certain one automatically, that would also be fine.

[cocowalla]

@bricelam I've tried your code, but I'm having a couple of issues when the database is identical to the model:

1. For mappings that use OnDelete, it always generates operations to drop and re-add the FK constraint
2. For mappings that use HasAlternateKey, it always generates operations to drop and re-add the constraint

Sample output:

ALTER TABLE [Activities] DROP CONSTRAINT [FK_Activities_Documents_DocumentId];
GO


ALTER TABLE [Attachments] DROP CONSTRAINT [FK_Attachments_Documents_DocumentId];
GO


DROP INDEX [AK_UserProfiles_AltId] ON [UserProfiles];
GO


ALTER TABLE [UserProfiles] ADD CONSTRAINT [AK_UserProfiles_AltId] UNIQUE ([AltId]);
GO


ALTER TABLE [Activities] ADD CONSTRAINT [FK_Activities_Documents_DocumentId] FOREIGN KEY ([DocumentId]) REFERENCES [Documents] ([Id]) ON DELETE NO ACTION;
GO


ALTER TABLE [Attachments] ADD CONSTRAINT [FK_Attachments_Documents_DocumentId] FOREIGN KEY ([DocumentId]) REFERENCES [Documents] ([Id]) ON DELETE NO ACTION;
GO

[bricelam]

These will likely be fixed while working on #831. Until then, you'll need to add some more fixup to make the models match more closely before diffing.

[cocowalla]

@bricelam problem is I can't see any more 'fixup' to add - I've had a good look while debugging, but can't see anything to set beyond your original snippet. Any ideas?

[bricelam]

Compare the value of .GetAnnotations() on the IForeignKey and IKey objects.

[cocowalla]

@bricelam so I can see that there are differences - for example, the reverse-engineered model doesn't have AlternateKey applied, which will be why it's always included in the diff.

I tried updating to 2.1.0-preview1-final, but get the same result. Do you know if more complete reverse engineering support will make it into preview 2 (or indeed if it's already in the nightlies)?

[bricelam]

You probably won't see any improvements in this area for the 2.1.0 release.

[JasonRodman]

I keep wishing one day this will get added, or someone builds an extension that does it. Until then I just can't use it. I keep holding out hope they will consider this a valuable feature to add. Defeats the purpose of an ORM if I have to manage every schema change manually, in my opinion.

[cocowalla]

@JasonRodman personally, I'm not bothered with automatic migrations - I only found them useful for dev environments, in which case I generally prefer to automate tearing down, recreating and seeding the whole database at startup. What I _do_ want is the ability to do a diff between my current model and a database, and use that to generate SQL scripts that can be executed manually, or be used with a good migrations tool (e.g. DbUp).

Problem is that anything the community comes up with will be tightly bound to EF internals, so it'll be a constantly moving target. For example, I put something together based on @bricelam's post to generate diff scripts, and I had to change quite a lot when I tried upgrading to 2.1.0-preview1-final. Also, regardless of how much 'fix-up' (manually changing stuff EF hasn't been able to figure out when reverse engineering the DB) I do, the diff always takes issue with some foreign and alternate keys - so I'm still left having to make manual changes to the scripts.

Quite frustrating there will be no improvements here in 2.1 :(

[JasonRodman]

@cocowalla To someone who follows domain driven design patterns, this is invaluable. We have been using automatic migrations it production environments since it first came out in EF many years ago with great success. The vast majority of our changes are additive, which this work great for. When we remove or move things around we have to intervene, but that is a very rare occasion. When I heard of the .net core initiative I had high hopes that it would have feature parity, and fix some of the pain points in the old EF. So far they have fixed the pain points but left the one thing out we need the most. It is indeed very frustrating because we want to move to .net core. We will have to stay on EF6 until it no longer works, at which point we will have to re-evaluate alternatives like Dapper or NHibernate.

[lakeman]

@cocowalla Can you combine the concept of https://github.com/aspnet/EntityFrameworkCore/issues/6214#issuecomment-239480293 with the implementation of https://github.com/aspnet/EntityFrameworkCore/issues/6214#issuecomment-332980580, so that after each migration you serialise the current model into the database and reload it next time?

[cocowalla]

@lakeman that's a pretty good idea! I'm not working with EF right now, but I'll definately give it a try at some point

[lakeman]

Looking into this a bit more, we're essentially replicating what MigrationsScaffolder.ScaffoldMigration does.

[lakeman]

Using a software lifecycle of only developers applying auto migrations for a single database, you could "serialise" the new model snapshot directly into your source code path [Updated to .Net Core 2.2 with some other fixes];

```c#
public class Reporter : IOperationReporter
{
private readonly ILogger logger;
public Reporter(ILogger logger)
{
this.logger = logger;
}

public void WriteError(string message) => logger.LogError(message);
public void WriteInformation(string message) => logger.LogInformation(message);
public void WriteVerbose(string message) => logger.LogTrace(message);
public void WriteWarning(string message) => logger.LogWarning(message);

}

public static async Task Migrate(DbContext db, bool force, string sourceFolder, string _namespace, ILogger logger, string classname = "DevModelSnapshot")
{
var builder = new DesignTimeServicesBuilder(db.GetType().Assembly, Assembly.GetEntryAssembly(), new Reporter(logger), null);
var services = builder.Build(db);
var dependencies = services.GetRequiredService();

bool ret = false;

var migrations = db.Database.GetMigrations();
var appliedMigrations = (await db.Database.GetAppliedMigrationsAsync()).ToList();
appliedMigrations.Sort();
var pendingMigrations = migrations.Except(appliedMigrations).Any();
var lastMigration = appliedMigrations.Except(migrations).LastOrDefault();

TypeInfo devModel = null;
var sqlFilename = Path.Combine(sourceFolder, $"{classname}.sql");
var clsFilename = Path.Combine(sourceFolder, $"{classname}.cs");

if (lastMigration!=null)
{
    if (pendingMigrations)
        throw new InvalidOperationException("An automatic migration has been run, but you've added new migration(s).\nYou probably want to restore the database.");
    var cls = $"{classname}_{lastMigration}";

    // find the matching snapshot that was compiled into this assembly from the previous run of this method
    devModel = db.GetType().Assembly.DefinedTypes.Where(t => {
        return t.Name == cls && t.Namespace == _namespace;
    }).FirstOrDefault();

    if (devModel == null)
        throw new InvalidOperationException("An automatic migration has been run, but no matching model snapshot was found.\nYou probably need to restore the database.");
}
else
{
    if (File.Exists(sqlFilename))
        File.Delete(sqlFilename);
    if (File.Exists(clsFilename))
        File.Delete(clsFilename);

    if (pendingMigrations)
    {
        await db.Database.MigrateAsync();
        ret = true;
    }
}

// Load either the last migration snapshot, or the last dev snapshot
var lastModel = (devModel == null) ? new DatabaseModelSnapshot() : (ModelSnapshot)Activator.CreateInstance(devModel);

var model = dependencies.SnapshotModelProcessor.Process(lastModel.Model);
var operations = dependencies.MigrationsModelDiffer.GetDifferences(model, db.Model);
if (operations.Any())
{
    if (!force && operations.Any(o => o.IsDestructiveChange))
        throw new InvalidOperationException(
            "Automatic migration was not applied because it could result in data loss.");
    var codeGen = dependencies.MigrationsCodeGeneratorSelector.Select(null);

    var sqlGenerator = db.GetService<IMigrationsSqlGenerator>();

    var name = $"{DateTime.Now:yyyyMMddHHmmss}_Auto";
    var insert = new InsertDataOperation()
    {
        Table = "__EFMigrationsHistory",
        Schema = "dbo",
        Columns = new[] { nameof(HistoryRow.MigrationId), nameof(HistoryRow.ProductVersion) },
        Values =  new[,] { { name, model.FindAnnotation("ProductVersion")?.Value ?? "Unknown version" } }
    };
    operations = operations.Concat(new[] { insert }).ToList();

    var commands = sqlGenerator.Generate(operations, db.Model);
    var executor = db.GetService<IMigrationCommandExecutor>();

    await executor.ExecuteNonQueryAsync(commands, db.GetService<IRelationalConnection>());

    using (var stream = File.AppendText(sqlFilename))
    {
        await stream.WriteAsync($"-- Migration {name}\n");
        foreach (var cmd in commands)
            await stream.WriteAsync(cmd.CommandText);
    }

    var newSnapshot = codeGen.GenerateSnapshot(_namespace, typeof(Database), $"{classname}_{name}", db.Model);

    // Write the new snapshot into the developers source folder so it will be available on the next run
    await File.WriteAllTextAsync(Path.Combine(sourceFolder, $"{classname}.cs"), newSnapshot);
    ret = true;
}
return ret;

}
```

Save / restore of the model snapshot to the database left as an exercise for the reader.

[jtheisen]

I found another reason why it's inconvenient not to have automatic migrations:

There are models that will be accepted by Entity Framework but you can't actually migrate to. For example, a circular foreign key relationship is an invalid model, at least with SqlServer, but Entity Framework doesn't know that.

So you create a migration with Add-Migration, but on Update-Database the migration fails. Then you can remove the migration again and try again with a different model. That's not ideal.

[ronsplinter]

I know this issue is closed but I want to leave my frustration about the missing automatic migrations feature somewere. Hopefully you will reconsider.

[milosloub]

We have small delelopment team and we did few startup projects. Everytime we are loosing time while droping and recreating local dev DB, because of everyday sync with other team member's model changes. With no automatic migration, we had to add condition on environment like:
1) Development - context.Database.EnsureCreated(); // And then call some DB init
2) Staging and Production - context.Database.Migrate();

While we are working in 14 days cycles, on the end of cycle we generate new migration file and deploy to Staging and Production. BUT during the development cycle we are forced to drop DB manually and recreate with all changes made by other team members every day. This leads to really non-productive development cycle.

We have experiences with EF6 and it's MigrateDatabaseToLatestVersion
We have experiences with other ORM like Doctrine from php Symfony framework - there is also "doctrine:schema:update"
Everytime it was really easy to keep sync models with real DB. With EF Core it's just more complicated.

I can say, that this missing feature automatically leads to slown down team during development. For one or two developer, it's OK, but with every other member it started to be really painfull time.

[ghost]

We have small delelopment team and we did few startup projects. Everytime we are loosing time while droping and recreating local dev DB, because of everyday sync with other team member's model changes. With no automatic migration, we had to add condition on environment like:

1. Development - context.Database.EnsureCreated(); // And then call some DB init
2. Staging and Production - context.Database.Migrate();

While we are working in 14 days cycles, on the end of cycle we generate new migration file and deploy to Staging and Production. BUT during the development cycle we are forced to drop DB manually and recreate with all changes made by other team members every day. This leads to really non-productive development cycle.

We have experiences with EF6 and it's MigrateDatabaseToLatestVersion
We have experiences with other ORM like Doctrine from php Symfony framework - there is also "doctrine:schema:update"
Everytime it was really easy to keep sync models with real DB. With EF Core it's just more complicated.

I can say, that this missing feature automatically leads to slown down team during development. For one or two developer, it's OK, but with every other member it started to be really painfull time.

Absolutely! I really miss the 'drop and create database if model changes' initializer in EF 6. It's one of the best features that make EF outstanding, compared to other ORM tools. After all, during development state, why do I want to maintain a Migration folder and a history list of all database schema changes?? There could be quite a few times of model changes during development stage in just one single day!!! I don't care about the specific changes, I just want to keep ignorant of the database recreation and always get the new and clean database in case of model changes.

[ghost]

One solution I'm thinking is that to check the current model hash during Main and compare it to a value in some other storage, e.g. local file, redis store. If they're different, call EnsureDeleted. This is not elegant, but it works though.

[lakeman]

Edited the auto-migration method I wrote in a comment above with the implementation I'm currently using. With the above, I only have to rebuild my database when I create a new migration for the release branch, or when I need to include additional steps to deal with existing data.

If anyone wants to demonstrate how to serialise the model into the database...

[ajcvickers]

@milosloub @virtualbiz The pattern we recommend for rapid development with an evolving model is to pair EnsureDeleted and EnsureCreated, with seeding as needed. Migrations don't need to exist for this.

It's also worth noting that It is generally bad practice to call context.Database.Migrate() to migrate the production database when the application starts. Database migration should be a deployment step run once in a controlled manner during deployment.

[JasonRodman]

I would highly disagree. We have been using automatic migrations for years in production applications and it saves us countless man hours of effort to manage database schema. It saves time during development and takes a mundane plumbing task out of the mix, not to mention removing human error. I think its wrong to assume that it is a bad practice in any way. Warming up a production app after deployment, which applies the schema changes is a controlled action. The very lack of this feature from EF Core is the single thing preventing many uses from adopting it. We do not want to go back to having to manage schema changes manually.

[milosloub]

@ajcvickers EnsureDeleted EnsureCreated-> this solution is much more time consuming then manually drop and only EnsureCreated. Yes, it's enough for really small applications like Blog post or something easy, but consider bigger project, 80 tables and 1 minute seeding data script - as Initializer usefull for development amount of data and also for integration tests. Then it is not effective always Drop and Create while nothing changes. I would say that DropCreateDatabaseIfModelChanges initializer, mentioned by @virtualbiz, is the most effective development technique - your team can forget about:
1) manually check model changes, drop db and EnsureCreated call or,
2) time consuming EnsureDeleted and EnsureCreated calls or,
3) complicated management of Migration folder across development team during development cycle

Btw I agree, that context.Database.Migrate() can be bad practice if you have only Production. I didn't want to go into details like mirrored Staging environment, which tests if everything is OK before containers deployment to production->then it's acceptable for us to use context.Database.Migrate() in separate DataMigrator project, which also holds about hundred migration files

[KSemenenko]

I do not see any problems with auto migration if it occurs explicitly.
Of course, if you have a larger application, and it has long been in production, then migrations would be necessary.
But I also do not see the point in having thousands of files with migrations, if I just need to update db with my model.
If I added a couple of fields and tables, then why should I migrate?
Maybe add some restrictions on auto migration?
for example it

• creating new tables
• add new fields
• delete non-key fields
  Although there would be such a small opportunity, it would simplify the development.

[rhyous]

Wow! I am so baffled that Automatic migrations isn't in the plans. This seems like someone really made a huge mistake. I read through this, and the blog post, and I still don't understand.

Let me share my point of view and my architectural reasons why Automatic Migrations should be a primary feature of Entity Framework Core.

Reason 1 - Big O
First, Big O is important. However, Big O can be used for evaluating work not just for a processor. It is used for memory space, and other things as well. Now, apply Big O to Automatic Migrations vs coding the migration.

Automatic Migrations: Big o(1)
Manual Migrations: Big O (n * k) where N is the number of entities and K is the number of changes. If I change 10 entities 10 times each, I have 100 migrations.

(insert sarcasm here) Why write code once, when you can write it 100 times. Oh, wait.

And in reality, we have made approximately 200 changes in our production application over the course of the past two years. When I move to .NET core, you are telling me that I have to hand code these 100 changes?

Automatic migrations has properly implemented all of them. Only 1 change had additional work beyond automatic migration's capabilities, and we still used Automatic Migrations, we just need two deploys. And yet you are condemning me to code migrations n * k times.

Reason 2 - Web Sites and Cloud
It used to be that an app was deployed to thousands of on-premise customers. Now apps are commonly just web apps, or and this applies to cloud too. That means 1 database (or cluster).

I read this quote: "Automatic migrations were awesome …for demos."

I find it offensive that Entity Framework didn't ask who was using it in production. I believe the mark was missed.

Let me requote this for you: "Automatic Migrations were awesome for demos, production web and cloud apps, for avoiding manually coding every migration, avoiding having 100 migrations and storing up painful history, and 90% of all migrations that weren't major data architecture changes.

Reason 3 - Coding
It is so easy to get your work done with Automatic Migrations. Instead, now I have to stop developing on my current story, create a new story for the migration, complete the migration, then go back to my story.

Conclusion
I feel like the decision makers didn't consider Big O, didn't survey who was using Automatic Migrations in production, didn't look at different environments or the future direction of the market (web and cloud and less onpremise installs), and instead just ran with a single use case as if it included all use cases.

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