C# – EF 5, Code First – Create a new database and run all migrations programmatically

centity-frameworkentity-framework-migrations

I'm using Entity Framework Code First migrations, and I have a scenario where I want to run a suite of integration tests. Each time the tests run, I want to re-create the database, and apply all migrations

The steps should be:

  1. Drop the existing test database (if any)
  2. Create a new test database, and apply all migrations
  3. Seed data

This is an existing project that I've added migrations to, and I used the Enable-Migrations command to create an "InitialCreate" migration that contains code to add all the tables to my database.

The code in my custom IDatabaseInitializer is as follows:

public void InitializeDatabase(MyContext context)
{
    //delete any existing database, and re-create
    context.Database.Delete();
    context.Database.Create();            

    //apply all migrations
    var dbMigrator = new DbMigrator(new Configuration());
    dbMigrator.Update();

    //seed with data
    this.Seed(context);

    context.SaveChanges();
}

The Up method of my InitialCreate migration is not getting called by this code, which is not what I expected. Instead, all of the tables are created when the Database.Create() method is called. I need the InitialCreate migration to run because I have additional code in there to create stored procedures.

So my questions is, how do I programmatically create a new database and run all migrations (including the InitialCreate migration)?

Best Answer

The following code has allowed me to meet the needs of my integration testing scenario outlined in the question, but surely there's a better way?

public void InitializeDatabase(MyContext context)
{
    //delete any existing database, and re-create
    context.Database.Delete();

    var newDbConnString = context.Database.Connection.ConnectionString;
    var connStringBuilder = new SqlConnectionStringBuilder(newDbConnString);
    var newDbName = connStringBuilder.InitialCatalog;

    connStringBuilder.InitialCatalog = "master";

    //create the new DB
    using(var sqlConn = new SqlConnection(connStringBuilder.ToString()))
    {
        using (var createDbCmd = sqlConn.CreateCommand())
        {
            createDbCmd.CommandText = "CREATE DATABASE " + newDbName;
            sqlConn.Open();
            createDbCmd.ExecuteNonQuery();
        }
    }

    //wait up to 30s for the new DB to be fully created
    //this takes about 4s on my desktop
    var attempts = 0;
    var dbOnline = false;
    while (attempts < 30 && !dbOnline)
    {
        if (IsDatabaseOnline(newDbConnString))
        {
            dbOnline = true;
        }
        else
        {
            attempts++;
            Thread.Sleep(1000);
        }
    }

    if (!dbOnline)
        throw new ApplicationException(string.Format("Waited too long for the newly created database \"{0}\" to come online", newDbName));

    //apply all migrations
    var dbMigrator = new DbMigrator(new Configuration());
    dbMigrator.Update();

    //seed with data
    this.Seed(context);

    context.SaveChanges();
}

private bool IsDatabaseOnline(string connString)
{
    try
    {
        using (var sqlConn = new SqlConnection(connString))
        {
            sqlConn.Open();
            return sqlConn.State == ConnectionState.Open;
        }
    }
    catch (SqlException)
    {
        return false;
    }
}
Related Topic