EntityFramework code-first custom connection strin

Question

When I create a context with a default connection string (as read from the app.config) the database is created and the migrations work - basically everything is in order. Whereas when the connection string is created programatically (using SqlConnectionStringBuilder):

• database isn't created when the database is not present (scenario A);
• CreateDbIfNotExists() creates the newest version of database model but the migration mechanisms are not invoked (scenario B).

In A an exception is thrown when I wish to access the database, as - obviously - it isn't there. In B database is created properly migration mechanisms are not called, as is the case in standard connection string.

app.config: "Data Source=localhost\\SQLEXPRESS;Initial Catalog=Db13;User ID=xxx;Password=xxx"

builder:

sqlBuilder.DataSource = x.DbHost;
sqlBuilder.InitialCatalog = x.DbName;
sqlBuilder.UserID = x.DbUser;
sqlBuilder.Password = x.DbPassword;

initializer:

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

Specs: Entity Framework: 5.0, DB: SQL Server Express 2008

Answer 1

For migrations you can either (1) use MigrateDatabaseToLatestVersion which will kick in automatically when you start using any of the entities in your context or (2) use DbMigrator to explicitly tell EF to kick off the migration. The advantage of (2) is that you don't have to perform a dummy operation (like AddJunk in @philsoady's example), and you could even use MigratorScriptingDecorator if you wanted to extract the migration SQL (see Example 2 in the code)

The trick with (2) seems to be in ensuring that the same connection string is used consistently by your DbMigrationsConfiguration and DbContext classes. Note that multiple contexts are instantiated during the course of DbMigration.Update - all of which call the context's default constructor (so watch out if you have more than one constructor). You also have 2 options here - you can use a connection string name in the app.config (but then you can't programmatically define the connection string) or build\hardcode\load etc... a complete connection string. See the comments in the code below.

Tested in EF 6.0.1 & 6.0.2

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations;
using System.Data.Entity.Migrations.Infrastructure;

namespace ConsoleApplication1
{
    // Models
    public class Foo
    {
        [Key]
        public int Id { get; set; }
        public string Column1 { get; set; }
        public string Column2 { get; set; }
    }

    // Configuration
    public class Configuration : DbMigrationsConfiguration<Context>
    {
        public static string StaticConnectionString; // use connection string

        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
            AutomaticMigrationDataLossAllowed = true;
            TargetDatabase = new DbConnectionInfo(StaticConnectionString, "System.Data.SqlClient"); // use connection string
            //TargetDatabase = new DbConnectionInfo("ConnectionStringName"); // use connection string name in app.config
        }

        protected override void Seed(Context context)
        {
        }
    }

    // Context
    public class Context : DbContext
    {
        public Context()
            //: base("ConnectionStringName") // use connection string name in app.config
            : base(ConsoleApplication1.Configuration.StaticConnectionString) // use connection string
        {
        }

        public IDbSet<Foo> Foos { get; set; }
    }

    // App
    class Program
    {
        static void Main(string[] args)
        {
            // Example 1 - migrate to test1 DB
            Configuration.StaticConnectionString = "Data Source=localhost;Initial Catalog=test1;Integrated Security=True;MultipleActiveResultSets=True";
            var configuration = new Configuration();
            var migrator = new DbMigrator(configuration);
            migrator.Update();
            Console.WriteLine("Migration 1 complete");

            // Example 2 - create migrate SQL and migrate to test2 DB
            // NOTE: You can't do this if you use a connection string name in app.config
            // Generate migrate sql script for migration to test2 DB
            Configuration.StaticConnectionString = "Data Source=localhost;Initial Catalog=test2;Integrated Security=True;MultipleActiveResultSets=True";
            configuration = new Configuration();
            migrator = new DbMigrator(configuration);
            var scriptor = new MigratorScriptingDecorator(migrator);
            string sql = scriptor.ScriptUpdate(null, null);
            Console.WriteLine("Migration 2 SQL:\n" + sql);

            // Perform migration to test2 DB
            configuration = new Configuration();
            migrator = new DbMigrator(configuration);
            migrator.Update();
            Console.WriteLine("Migration 2 complete");
        }
    }
}

Answer 2

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

Answer 3

He is a solution, with NO Connection strings in app.config. Uses automatic migrations and 2 databases using the same context. The real runtime supplied Connection. Approach.

APP.CONFIG (Uses EF 6)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework,     Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
 </configSections>
 <startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
 <entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
  <parameters>
    <parameter value="Data Source=localhost; Integrated Security=True; MultipleActiveResultSets=True" />
  </parameters>
</defaultConnectionFactory>
 </entityFramework>
</configuration>

I rewrote the code to make as small as possible for Demo:

using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Migrations;

namespace Ef6Test {
    public class Program {
    public static void Main(string[] args) {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<Ef6Ctx, Ef6MigConf>());
        WhichDb.DbName = "HACKDB1";
        var sqlConn = GetSqlConn4DBName(WhichDb.DbName);
        var context = new Ef6Ctx(sqlConn);
        context.Database.Initialize(true);
        AddJunk(context);
        //sqlConn.Close();  //?? whatever other considerations, dispose of context etc...

        Database.SetInitializer(new MigrateDatabaseToLatestVersion<Ef6Ctx, Ef6MigConf>()); // yes its default again reset this !!!!
        WhichDb.DbName = "HACKDB2";
        var sqlConn2 = GetSqlConn4DBName(WhichDb.DbName);
        var context2 = new Ef6Ctx(sqlConn2);
        context2.Database.Initialize(true);
        AddJunk(context2);
    }
    public static class WhichDb { // used during migration to know which connection to build
        public static string DbName { get; set; }
    }
    private static void AddJunk(DbContext context) {
        var poco = new pocotest();
        poco.f1 = DateTime.Now.ToString();
      //  poco.f2 = "Did somebody step on a duck?";  //comment in for second run
        context.Set<pocotest>().Add(poco);
        context.SaveChanges();
    }
    public static DbConnection GetSqlConn4DBName(string dbName) {
        var sqlConnFact =
            new SqlConnectionFactory(
                "Data Source=localhost; Integrated Security=True; MultipleActiveResultSets=True");
        var sqlConn = sqlConnFact.CreateConnection(dbName);
        return sqlConn;
    }
}
public class MigrationsContextFactory : IDbContextFactory<Ef6Ctx> {
    public Ef6Ctx Create() {
        var sqlConn = Program.GetSqlConn4DBName(Program.WhichDb.DbName); // NASTY but it works
        return new Ef6Ctx(sqlConn);
    }
}
public class Ef6MigConf : DbMigrationsConfiguration<Ef6Ctx> {
    public Ef6MigConf() {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
    }
}
public class pocotest {
    public int Id { get; set; }
    public string f1 { get; set; }
 //   public string f2 { get; set; } // comment in for second run
}
public class Ef6Ctx : DbContext {
    public DbSet<pocotest> poco1s { get; set; }
    public Ef6Ctx(DbConnection dbConn) : base(dbConn, true) { }
}
}

Answer 4

I wanted to automatically migrate when running in DEBUG to make it easy for the devs (the production installer does the migrations normally) but had the same problem, a code-specified connection string is ignored when migrating.

My approach was to derive the migrating contexts from this generic which handles "saving" the connection string:

public class MigrateInitializeContext<TDbContext, TMigrationsConfiguration> : DbContext
    where TDbContext : DbContext
    where TMigrationsConfiguration : DbMigrationsConfiguration<TDbContext>, new()
{
    // ReSharper disable once StaticFieldInGenericType
    private static string nameOrConnectionString = typeof(TDbContext).Name;

    static MigrateInitializeContext()
    {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<TDbContext, TMigrationsConfiguration>());
    }

    protected MigrateInitializeContext(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
        MigrateInitializeContext<TDbContext,TMigrationsConfiguration>.nameOrConnectionString = nameOrConnectionString;
    }

    protected MigrateInitializeContext() : base(nameOrConnectionString)
    {
    }
}

The ReSharper warning is because static fields in a generic class are only static per concrete type which in our case is exactly what we want.

Contexts are defined as:

public class MyContext : MigrateInitializeContext<MyContext, Migrations.Configuration>
{
    public MyContext()
    {
    }

    public MyContext(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
    }

    public virtual DbSet<MyType> MyTypes { get; set; }
}

which can be used normally.

Answer 5

Look at this link: It gives you more freedom to activate the migrations yourself for each database.

I solved this by using a static connection string to a specific database, inside the default constructor.

Let's say I have several databases, all are based on the same schema: myCatalog1, myCatalog2 etc. I use only the first database connection string in the constructor like this:

public MyContext() : base("Data Source=.\SQLEXPRESS;Initial Catalog=myCatalog1;Integrated Security=True")
{
   // Can leave the rest of the constructor function itself empty
}

This constructor is used only for the Add-Migration command to work and create the migrations. Note that there are no side effects for the rest of the databases and if you need another constructor for initializing the context (for other purposes except for migrations), it will work.

After I run the Add-Migration like this:

Add-Migration -ConfigurationTypeName YourAppName.YourNamespace.Configuration "MigrationName"

I can call the next code (taken from the link provided at the beginning) in order to update migrations to each one of my databases which are based on the same schema as myCatalog1:

YourMigrationsConfiguration cfg = new YourMigrationsConfiguration(); 
cfg.TargetDatabase = 
   new DbConnectionInfo( 
      theConnectionString, 
      "provider" );

DbMigrator dbMigrator = new DbMigrator( cfg );
if ( dbMigrator.GetPendingMigrations().Count() > 0 )
{
   // there are pending migrations
   // do whatever you want, for example
   dbMigrator.Update(); 
}

Answer 6

I've come to similar conclusions.

We had a lengthy discussion on that yesterday. Take a look at it.

If connection is invoked via DbContext ctor - it's where problems appear (simplified). As DbMigrator actually calls your 'default empty' constructor - so you get a mix of things. I had some really strange effects from it. My conclusion was that normal initializer CreateDb... works - but migrations don't (and even fail, throw errors in some cases).

Bottom line - is to somehow make a 'singleton' connection - either through the DbContext Factory as @kirsten used - or making and changing a static connection within your DbContext - or similar. Not sure if that resolves all issues, but should help.