c# - EF 5, Code First - Create a new database and run all migrations programmatically

Question

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)?

Answer 1

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;
    }
}