EntityFramework code-first custom connection string and migrations

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

Solution 1

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

Solution 2

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) { }
}
}

Solution 3

I have been able to switch between connections using the following technique

1) Have multiple connection string names defined in app.config.

2) Have a constructor in the context that takes the connection string name

public Context(string connStringName)
        : base(connStringName)
    {

    }

3) Set up the Create method for the context - and make it able to receive the connection name ( using a bit of a trick )

  public class ContextFactory : IDbContextFactory<Context>
  {
    public Context Create()
    {
        var s = (string)AppDomain.CurrentDomain.GetData("ConnectionStringName");
        var context = new Context(s);
        return context;
    }
}

4) My migration configuration ....

 public sealed class Configuration : DbMigrationsConfiguration<SBD.Syrius.DataLayer.Context>
{
   etc
}

5) Set up a function to create the context.

 private static Context MyCreateContext(string connectionStringName )
    {
        // so that we can get the connection string name to the context create method 
       AppDomain.CurrentDomain.SetData("ConnectionStringName", connectionStringName);

        // hook up the Migrations configuration
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<Context, Configuration>());

        // force callback by accessing database
        var db = new Context(connectionStringName);
        var site = db.Sites.FirstOrDefault()  // something to access the database

        return db;
    }

Solution 4

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.

Solution 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(); 
}

View more solutions