Using Entity Framework (code first) migrations in production

Question

I'm just looking into using EF migrations for our project, and in particular for performing schema changes in production between releases.

I have seen mentioned that there is an API to perform these migrations at run-time using the DbMigration class, but I can't find any specific examples.

Ideally, I would want one DbMigration file for every database change, and for those changes to be applied automatically on application start up from the current version up to the latest version.

Solution 1

There is a Database Initializer you can use to achieve the migration to latest version on startup (or better, the dbinitializer will kick in on first db access), the MigrateDatabaseToLatestVersion, you use it like that:

Database.SetInitializer<ObjectContext>(
    new MigrateDatabaseToLatestVersion<ObjectContext, Configuration>());

Regarding having one file per migration, if you enable automatic migrations you will find them in the Migrations folder (by default) in the root of your project.

Relevant info, with examples, here: http://weblogs.asp.net/fredriknormen/archive/2012/02/15/using-entity-framework-4-3-database-migration-for-any-project.aspx

Solution 2

This works too:

var configuration = new MyDbContextConfiguration();
configuration.TargetDatabase = new DbConnectionInfo(
    database.ConnectionString, database.ProviderName);

var migrator = new DbMigrator(configuration);
migrator.Update();

You can also call:

migrator.GetPendingMigrations();

to get a list of the migrations it needs to apply.

Solution 3

Since you didn't specify which Visual Studio version you are using, or Database, I will add an answer here to say that in VS2015 with Microsoft's SQL Server, this is now incredibly easy using the "Publish" tool.

You do not need to bother with the API you speak of. Simply do your work locally, changing your models, applying migrations etc, then when you want to push out to release/test servers, use the publish tool.

You can select to apply any migrations you have made locally, to the remote server the first time the application is started up.

Once you have all of your migrations and everything done locally (presumable in your Dev env) then you publish (right click the project, click "Publish..." Tick the "Execute Code First Migrations (runs on application startup)" checkbox under the "Settings" tab and then it will apply the migrations the first time the app is accessed (so there will be a short delay the first time).

Guide: https://msdn.microsoft.com/en-us/library/dd465337(v=vs.110).aspx

I learned all this because I had to do this to a Windows 2012 server: http://www.sherweb.com/blog/how-to-install-webdeploy-on-windows-server-2012/

Good luck!

Solution 4

I wanted to control which migrations run explicitly in code and after a lot of search i managed to develop the following technique without the need of a DbConfiguration class or automatic migrations enabled:

public static void RunMigration(this DbContext context, DbMigration migration)
{            
    var prop = migration.GetType().GetProperty("Operations", BindingFlags.NonPublic | BindingFlags.Instance);
    if (prop != null)
    {
        IEnumerable<MigrationOperation> operations = prop.GetValue(migration) as IEnumerable<MigrationOperation>;
        var generator = new SqlServerMigrationSqlGenerator();
        var statements = generator.Generate(operations, "2008");
        foreach (MigrationStatement item in statements)
            context.Database.ExecuteSqlCommand(item.Sql);
    }
}

And if we had a migration like this:

public class CreateIndexOnContactCodeMigration : DbMigration
{
    public override void Up()
    {
        this.CreateIndex("Contacts", "Code");
    }

    public override void Down()
    {
        base.Down();
        this.DropIndex("Contacts", "Code");
    }
}

We would use it like that:

using (var dbCrm = new CrmDbContext(connectionString))
{
    var migration = new CreateIndexOnContactCodeMigration();
    migration.Up();                
    dbCrm.RunMigration(migration);
}

Regards.

Solution 5

To add to all the answers already posted. Entity Framework uses a table: dbo.__MigrationHistory to keep track of all the migrations that have already been applied to the database to avoid running a migration that for example: inserts data or changes the database schema.

If you wish to run a script such as run adding data or changing the schema of the database you can create an empty migration using Package Manager Console and run the script via the the newly added migration. Ensure you use the initializer is used to prevent EF from dropping and recreating the database on every run.

     public override void Up()
    {
        string directoryToSearchScripts = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\..\\"));

        string scriptFilePath = Directory.GetFiles(directoryToSearchScripts, "dummy-script.sql", SearchOption.AllDirectories).FirstOrDefault();
        if (!string.IsNullOrEmpty(scriptFilePath))
        {
            string fundsSqlScript = File.ReadAllText(scriptFilePath);
            Sql(fundsSqlScript);
        }
    }

    public override void Down()
    {
    }

When you publish the application and check the "Execute Code First Migrations" option, EF will run the migrations that have not yet been applied to the database.

View more solutions

Comments

[Doug Chamberlain]

Where would this code be added? Could this be wrapped in a conditional statement to check an app setting from the web.config file? so that during development this code could be skipped? Then web.config transforms would enable it when publishing released version?

[WDRust]

If you need to set the initializer conditionally for the configuration properties then a better approach is to use a feature of EF4.3. Refer to this article msdn.microsoft.com/en-US/data/jj556606 ("Database Initializer" part), use that in conjunction with transformation and you're set

[d512]

What is the variable database here?

[mackie]

In this case just a DTO with ConnectionString and ProviderName properties. It's not part of the framework.

[John Shedletsky]

If I use this method, what initializer should I use on my dbContext create?

[mackie]

In your DbContext constructor just do: Database.SetInitializer<TypeOfMyDbContext>(null);

[Bron Davies]

More information on this here: msdn.microsoft.com/en-us/data/jj591621.aspx#initializer And for those of you who are wary of automatic migrations and database schema updates, and think that hand-editing a script is safer... verify you have a backup and test it out first for god's sake!!

[WDRust]

I expanded on the subject while updating this question, peer review is welcomed

[Alejandro B.]

This worked for me, even removing the new DbConnectionInfo part. Thanks!

[Assaf S.]

This solution implies the app has permissions to change the schema. I think this is unlikely in a prod environment where the app is limited to data manipulation only. I wonder if it possible to run migrations from the compiled DLL, using the dotnet CLI (from an admin account or by providing credentials)

[Worthy7]

Just an extended comment since I'm older and wiser now. Usually you don't want to execute the migrations automatically on a production server. Ideally you generate an SQL script to do the migration, verify it does what you want, then run the script on the production database whilst publishing/deploying your app. That's the "standard" way to do it.

[MohammadHossein R]

This is not available when publishing to File System