New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Automatic Migrations #6214

Closed
netcore-jroger opened this Issue Jul 31, 2016 · 41 comments

Comments

Projects
None yet
@netcore-jroger

netcore-jroger commented Jul 31, 2016

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

This comment has been minimized.

Show comment
Hide comment
@netcore-jroger

netcore-jroger Aug 2, 2016

@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 !

netcore-jroger commented Aug 2, 2016

@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

This comment has been minimized.

Show comment
Hide comment
@bjorn-ali-goransson

bjorn-ali-goransson Aug 2, 2016

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 commented Aug 2, 2016

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

This comment has been minimized.

Show comment
Hide comment
@bjorn-ali-goransson

bjorn-ali-goransson Aug 2, 2016

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

bjorn-ali-goransson commented Aug 2, 2016

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

@divega divega added this to the 1.1.0 milestone Aug 2, 2016

@bricelam

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Aug 9, 2016

Member

@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?

Member

bricelam commented Aug 9, 2016

@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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Aug 9, 2016

Member

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);
}
Member

bricelam commented Aug 9, 2016

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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Aug 9, 2016

Member

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

Member

bricelam commented Aug 9, 2016

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

@bricelam bricelam removed this from the 1.1.0 milestone Aug 9, 2016

@divega divega added this to the Discussions milestone Aug 10, 2016

@divega divega removed the closed-question label Aug 10, 2016

@divega divega removed this from the Discussions milestone Aug 10, 2016

@netcore-jroger

This comment has been minimized.

Show comment
Hide comment
@netcore-jroger

netcore-jroger Aug 12, 2016

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

netcore-jroger commented Aug 12, 2016

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

@bricelam

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Aug 12, 2016

Member

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.

Member

bricelam commented Aug 12, 2016

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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Aug 12, 2016

Member

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.

Member

bricelam commented Aug 12, 2016

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.

@bricelam bricelam changed the title from how to the using C# code auto migrate the change to Automatic Migrations Aug 12, 2016

@divega divega added this to the Discussions milestone Aug 12, 2016

@divega

This comment has been minimized.

Show comment
Hide comment
@divega

divega Aug 12, 2016

Member

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.

Member

divega commented Aug 12, 2016

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.

@divega divega closed this Aug 12, 2016

@divega divega removed this from the Discussions milestone Aug 12, 2016

@bricelam bricelam removed their assignment Aug 12, 2016

@jemiller0

This comment has been minimized.

Show comment
Hide comment
@jemiller0

jemiller0 Jan 4, 2017

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.

jemiller0 commented Jan 4, 2017

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

This comment has been minimized.

Show comment
Hide comment
@bricelam
Member

bricelam commented Jan 5, 2017

@cigano

This comment has been minimized.

Show comment
Hide comment
@cigano

cigano Jan 12, 2017

@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?

cigano commented Jan 12, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@divega

divega Jan 12, 2017

Member

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

Member

divega commented Jan 12, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@cigano

cigano Jan 13, 2017

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.

cigano commented Jan 13, 2017

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

This comment has been minimized.

Show comment
Hide comment
@Jupakabra

Jupakabra Jan 13, 2017

@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).

Jupakabra commented Jan 13, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Jan 13, 2017

Member

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

Member

bricelam commented Jan 13, 2017

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

@cigano

This comment has been minimized.

Show comment
Hide comment
@cigano

cigano Jan 13, 2017

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

cigano commented Jan 13, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@JasonRodman

JasonRodman Sep 27, 2017

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.

JasonRodman commented Sep 27, 2017

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

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers Sep 27, 2017

Member

@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?

Member

ajcvickers commented Sep 27, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@JasonRodman

JasonRodman Sep 27, 2017

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

JasonRodman commented Sep 27, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers Sep 27, 2017

Member

@JasonRodman Thanks for the feedback; much appreciated.

Member

ajcvickers commented Sep 27, 2017

@JasonRodman Thanks for the feedback; much appreciated.

@bricelam

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Sep 28, 2017

Member

⚠️ 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>());
}
Member

bricelam commented Sep 28, 2017

⚠️ 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

This comment has been minimized.

Show comment
Hide comment
@jtheisen

jtheisen Feb 2, 2018

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

jtheisen commented Feb 2, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@cocowalla

cocowalla Mar 1, 2018

@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

cocowalla commented Mar 1, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Mar 1, 2018

Member

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.

Member

bricelam commented Mar 1, 2018

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

This comment has been minimized.

Show comment
Hide comment
@cocowalla

cocowalla Mar 1, 2018

@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?

cocowalla commented Mar 1, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Mar 1, 2018

Member

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

Member

bricelam commented Mar 1, 2018

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

@cocowalla

This comment has been minimized.

Show comment
Hide comment
@cocowalla

cocowalla Mar 7, 2018

@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)?

cocowalla commented Mar 7, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@bricelam

bricelam Mar 9, 2018

Member

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

Member

bricelam commented Mar 9, 2018

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

@JasonRodman

This comment has been minimized.

Show comment
Hide comment
@JasonRodman

JasonRodman Mar 9, 2018

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.

JasonRodman commented Mar 9, 2018

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

This comment has been minimized.

Show comment
Hide comment
@cocowalla

cocowalla Mar 9, 2018

@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 :(

cocowalla commented Mar 9, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@JasonRodman

JasonRodman Mar 9, 2018

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

JasonRodman commented Mar 9, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@lakeman

lakeman Apr 26, 2018

@cocowalla Can you combine the concept of #6214 (comment) with the implementation of #6214 (comment), so that after each migration you serialise the current model into the database and reload it next time?

lakeman commented Apr 26, 2018

@cocowalla Can you combine the concept of #6214 (comment) with the implementation of #6214 (comment), so that after each migration you serialise the current model into the database and reload it next time?

@cocowalla

This comment has been minimized.

Show comment
Hide comment
@cocowalla

cocowalla Apr 26, 2018

@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

cocowalla commented Apr 26, 2018

@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

This comment has been minimized.

Show comment
Hide comment
@lakeman

lakeman Apr 27, 2018

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

lakeman commented Apr 27, 2018

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

@lakeman

This comment has been minimized.

Show comment
Hide comment
@lakeman

lakeman Apr 27, 2018

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;

public static async Task<bool> Migrate(DbContext db, bool force, string sourceFolder, string _namespace, string classname = "DevModelSnapshot")
{
	bool ret = false;
	// core 2.0; in 2.1 this will change to servicecollection.AddEntityFrameworkDesignTimeServices?
	var designTimeServices = new DesignTimeServicesBuilder(typeof(Database).Assembly, new OperationReporter(handler: null)).Build(db);
	var pendingMigrations = (await db.Database.GetPendingMigrationsAsync()).Any();
	var devModel = typeof(Database).Assembly.ExportedTypes.Where(t => t.Name == classname && t.Namespace == _namespace).FirstOrDefault();
	if (devModel == null)
	{
		// make sure the database has been migrated first
		if (pendingMigrations){
			await db.Database.MigrateAsync();
			ret = true;
		}
	} else if (!force && pendingMigrations) {
		throw new InvalidOperationException(
			"Automatic migration was not applied because of unapplied migrations.");
	}
	// Load either the last migration snapshot, or the last dev snapshot
	var lastModel = (devModel == null) ? new DatabaseModelSnapshot() : (ModelSnapshot)Activator.CreateInstance(devModel);
	var differ = db.GetService<IMigrationsModelDiffer>();
	var operations = differ.GetDifferences(lastModel.Model, db.Model);
	if (operations.Any())
	{
		if (!force && operations.Any(o => o.IsDestructiveChange))
			throw new InvalidOperationException(
				"Automatic migration was not applied because it would result in data loss.");

		var codeGen = designTimeServices.GetRequiredService<IMigrationsCodeGenerator>();

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

		var newSnapshot = codeGen.GenerateSnapshot(_namespace, typeof(Database), classname, db.Model);
		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.

lakeman commented Apr 27, 2018

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;

public static async Task<bool> Migrate(DbContext db, bool force, string sourceFolder, string _namespace, string classname = "DevModelSnapshot")
{
	bool ret = false;
	// core 2.0; in 2.1 this will change to servicecollection.AddEntityFrameworkDesignTimeServices?
	var designTimeServices = new DesignTimeServicesBuilder(typeof(Database).Assembly, new OperationReporter(handler: null)).Build(db);
	var pendingMigrations = (await db.Database.GetPendingMigrationsAsync()).Any();
	var devModel = typeof(Database).Assembly.ExportedTypes.Where(t => t.Name == classname && t.Namespace == _namespace).FirstOrDefault();
	if (devModel == null)
	{
		// make sure the database has been migrated first
		if (pendingMigrations){
			await db.Database.MigrateAsync();
			ret = true;
		}
	} else if (!force && pendingMigrations) {
		throw new InvalidOperationException(
			"Automatic migration was not applied because of unapplied migrations.");
	}
	// Load either the last migration snapshot, or the last dev snapshot
	var lastModel = (devModel == null) ? new DatabaseModelSnapshot() : (ModelSnapshot)Activator.CreateInstance(devModel);
	var differ = db.GetService<IMigrationsModelDiffer>();
	var operations = differ.GetDifferences(lastModel.Model, db.Model);
	if (operations.Any())
	{
		if (!force && operations.Any(o => o.IsDestructiveChange))
			throw new InvalidOperationException(
				"Automatic migration was not applied because it would result in data loss.");

		var codeGen = designTimeServices.GetRequiredService<IMigrationsCodeGenerator>();

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

		var newSnapshot = codeGen.GenerateSnapshot(_namespace, typeof(Database), classname, db.Model);
		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

This comment has been minimized.

Show comment
Hide comment
@jtheisen

jtheisen Jun 13, 2018

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.

jtheisen commented Jun 13, 2018

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

This comment has been minimized.

Show comment
Hide comment
@ronsplinter

ronsplinter Sep 5, 2018

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

ronsplinter commented Sep 5, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment