EntityFramework custom connection string with codes and migration

Question

When I create a context with the default connection string (as read from app.config ), the database is created and the migrations work - basically everything is fine. If the connection string is created programmatically (using SqlConnectionStringBuilder ):

• the database is not created when the database is absent (scenario A );
• CreateDbIfNotExists() creates the latest version of the database model, but the migration mechanisms do not call (script B ).

An exception is thrown in A when I want to access the database, because - obviously - it is not there. In B , the database is created correctly, the migration mechanisms are not called, as in the 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 >() ); 

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

Answer 1

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

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

I have a similar problem with migrations. And in my solution, I should always install the database initializer in ctor, for example below

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

In a custom initializer, you must implement the InitalizeDatabase(CustomContex context) method, for example.

 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 2

It is a solution with NO Connection Strings in app.config. Uses automatic migrations and 2 databases using the same context. In real time, a connection is established. An 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 little 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 3

I managed to switch between connections using the following technique

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

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

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

3) Set the Create method for the context - and make it able to get the name of the connection (using a little 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) Configure the function to create 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; } 

Answer 4

I came to similar conclusions.

We discussed this for a long time yesterday . Take a look at this.

If the connection is called via the DbContext ctor, where the problems appear (simplify). Since DbMigrator actually calls your constructor "default empty", so you get a lot of things. I had some really strange consequences. My conclusion was that the usual CreateDb... initializer CreateDb... , but the migrations do not (and even do not, in some cases errors occur).

The bottom line is to somehow make a singleton connection - either through DbContext Factory as @kirsten used - or creating and modifying a static connection in your DbContext - or the like. Not if this fixes all the problems, but should help.

Answer 5

For migration, you can (1) use MigrateDatabaseToLatestVersion , which will automatically start when any of the objects in your context start, or (2) use DbMigrator to explicitly specify EF to start the migration. The advantage (2) is that you do not need to perform a dummy operation (for example, AddJunk in the AddJunk example), and you can even use MigratorScriptingDecorator if you want to extract the SQL migration query (see Example 2 in the code)

The trick with (2) seems to be to ensure that the same connection string is used sequentially by your DbMigrationsConfiguration and DbContext . Note that during DbMigration.Update , an instance of several contexts is created, all of which invoke the default context constructor (so be careful if you have more than one constructor). You also have 2 options: you can use the connection string name in app.config (but then you cannot programmatically define the connection string) or build \ hardcode \ load, etc ... full connection string . See comments in the code below.

Tested in EF 6.0.1 and 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 6

Take a look at this link: This gives you more freedom to activate migration for each database.

I solved this using a static connection string for a specific database inside the default constructor.

Let's say I have several databases, all based on the same scheme: myCatalog1, myCatalog2, etc. I use only the first line of the database connection in the constructor as follows:

 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 migration. Please note: there are no side effects for the rest of the database, and if you need another constructor to initialize the context (for purposes other than migration), it will work.

After running Add-Migration do the following:

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

I can call the following code ( taken from the link provided at the beginning ) to update the migrations for each of my databases which are based on the same scheme 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 7

I wanted to automatically migrate when I started to DEBUG to facilitate the work of developers (as a rule, the production installer performs the migration), but I had the same problem, when executing the migration, the line of code specified in the code is ignored.

My approach was to infer migrating contexts from this pedigree that handles the “save” 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 that static fields in the general class are only static for a particular 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 as usual.