Sometimes you need to do a really end-to-end automated test involving your Entity Framework database. In cases like this, it’s important to be able to reset the database to a known state, but this can be fraught with difficulties — apps hold onto connections, and the code for re-building the database isn’t obvious. Here’s what I’m using at the moment.

This is a really aggressive database (re)initializer for EF code-first with migrations; use it at your peril but it seems to run pretty repeatably for me. It will;

1. Forcibly disconnect any other clients from the DB
2. Delete the DB.
3. Rebuild the DB with migrations and runs the Seed method
4. Take ages! (watch the timeout limit for your test framework; a default 60 second timeout might not be enough)

Here’s the class;

public class DropCreateAndMigrateDatabaseInitializer<TContext, TMigrationsConfiguration>
    : IDatabaseInitializer<TContext> 
    where TContext: DbContext
    where TMigrationsConfiguration : System.Data.Entity.Migrations.DbMigrationsConfiguration<TContext>, new()
{
    public void InitializeDatabase(TContext context)
    {
        if (context.Database.Exists())
        {
            // set the database to SINGLE_USER so it can be dropped
            context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "ALTER DATABASE [" + context.Database.Connection.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
 
            // drop the database
            context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "USE master DROP DATABASE [" + context.Database.Connection.Database + "]");
        }
        var migrator = new MigrateDatabaseToLatestVersion<TContext, TMigrationsConfiguration>();
        migrator.InitializeDatabase(context);
    }
}

Use it like this;

public static void ResetDb()
{
    // rebuild the database
    Console.WriteLine("Rebuilding the test database");
    var initializer = new DropCreateAndMigrateDatabaseInitializer<MyContext, MyEfProject.Migrations.Configuration>();
    Database.SetInitializer<MyContext>initializer);
    using (var ctx = new MyContext())
    {
        ctx.Database.Initialize(force: true);
    }
}

You should also set up your connection string in a particular way. In your integration test project,

1. set up your connection string to have “Pooling=false;” This doesn’t help the speed of the test, but helps mitigate problems with multiple tests running against the integration test db. (Thanks to Ladislav Mrnka for this.)

2. set the initial catalog to a different DB from your production one — I add ‘IntegationTests’ to the end of the name of the database. This ensures you’re not going to delete the database which is, say, underlying the web app you’re building.

<connectionStrings>
    <add name="MyContext" connectionString="Pooling=false;data source=localhost;initial catalog=MyContextIntegrationTests;[...]" providerName="System.Data.SqlClient" />

</connectionStrings>

Lastly, you’ll need to make sure that your tests run in series, not in parallel. I use NCrunch, and needed to use the NCrunch.Framework.SerialAttribute to make sure that tests don’t overlap.