Code First Migration with Connection Strings

code-firstef-code-firstentity-framework-migrations

So I've managed to get Code First running and it works great.

Since I am still developing the application the structure of the Database isn't finalize and so I need to implement migrations.

I followed the Official Blog Post and got that Update-Database command working.

However, this only updates the SQLExpress version of the database. The production version of the database is on Azure and I specify the connection string at run time so the Update-Database command doesn't work on that.

So my final question is: how do I apply automatic migrations to the production database whose connection string is specified at runtime?

Best Answer

On the package manager console type:

Get-Help Update-Database

Relevant part:

    Update-Database [-SourceMigration <String>] [-TargetMigration <String>] [-Script] [-Force] [-ProjectName <String>] [-StartUpProjectName <String>] [-ConfigurationTypeName <String>] [-ConnectionStringName <String>] [<Com
monParameters>]

So you can do a Update-Database -ConnectionStringName "MyConnectionString" and it should work like a charm.

You also have a MigrateDatabaseToLatestVersion database initializer, if you set it (via Database.SetInitializer()), when you deploy your app on production with proper connection string, on first db access it should automagically migrate your db to the latest version.

I suggest caution though, always backup things.

Update

@Alexy Strakh recent comments spawned another argument worth putting in the answer.

Properly configuring a deployment system using Code First Migrations, given 2 ConnectionStrings.

Define your connection strings in web.config (prod and dev), with default passwords
Have the configuration system of your application know about the prod and dev connection configurations, optionally build unit tests to ensure the right one is picked*
Employ config file transformation and have it transform your web.config into the one with production values
Deploy your package to production (this should be the most cutting edge way)

You are not supposed to interact with the production environment from your development box, but if you really need to do that, then make it a temporary solution that needs to be reverted as soon as you're done.

Another option is to simply use the Web.Debug.config and Web.Release.config and have a central template for the main web.config (which would be the only one you check in in your source control).

Just make sure never to check in production or personal-development passwords (if any).

*You can use the DEBUG symbol to check how the application is running.

Related Solutions

Entity-framework – EF Migrations: Rollback last applied migration

I want to add some clarification to this thread:

Update-Database -TargetMigration:"name_of_migration"

What you are doing above is saying that you want to rollback all migrations UNTIL you're left with the migration specified. Thus, if you use GET-MIGRATIONS and you find that you have A, B, C, D, and E, then using this command will rollback E and D to get you to C:

Update-Database -TargetMigration:"C"

Also, unless anyone can comment to the contrary, I noticed that you can use an ordinal value and the short -Target switch (thus, -Target is the same as -TargetMigration). If you want to rollback all migrations and start over, you can use:

Update-Database -Target:0

0, above, would rollback even the FIRST migration (this is a destructive command--be sure you know what you're doing before you use it!)--something you cannot do if you use the syntax above that requires the name of the target migration (the name of the 0th migration doesn't exist before a migration is applied!). So in that case, you have to use the 0 (ordinal) value. Likewise, if you have applied migrations A, B, C, D, and E (in that order), then the ordinal 1 should refer to A, ordinal 2 should refer to B, and so on. So to rollback to B you could use either:

Update-Database -TargetMigration:"B"

Update-Database -TargetMigration:2

Edit October 2019:

According to this related answer on a similar question, correct command is -Target for EF Core 1.1 while it is -Migration for EF Core 2.0.

Entity-framework – EntityFramework code-first custom connection string and migrations

If your migration does not work correctly try to set Database.Initialize(true) in DbContext ctor.

public CustomContext(DbConnection connection)
: base(connection, true)    
{    
        Database.Initialize(true);    
}

I have similar problem with migrations. And in my solution I have to always set database initializer in ctor, like below

public CustomContext(DbConnection connection)
: base(connection, true)    
{    
        Database.SetInitializer(new CustomInitializer());
        Database.Initialize(true);    
}

In custom initializer you have to implement InitalizeDatabase(CustomContex context) method, eg.

class CustomInitializer : IDatabaseInitializer<CustomContext>
{
    public void InitializeDatabase(CustomContext context)
    {
        if (!context.Database.Exists || !context.Database.CompatibleWithModel(false))
        {
            var configuration = new Configuration();
            var migrator = new DbMigrator(configuration);
            migrator.Configuration.TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString, "System.Data.SqlClient");
            var migrations = migrator.GetPendingMigrations();
            if (migrations.Any())
            {
                var scriptor = new MigratorScriptingDecorator(migrator);
                string script = scriptor.ScriptUpdate(null, migrations.Last());
                if (!String.IsNullOrEmpty(script))
                {
                    context.Database.ExecuteSqlCommand(script);
                }
            }
        }
    }
}

UPDATED