Entity framework, Really do drop create database if model changes and Db is in use

This week I saw a tweet from Julie Lerman (@julielerman) which caught my interest:

The error report found here, says:

Once you have expanded a database in VS 2012 SQL Server Object Explorer, it is not possible to delete or detach it. This is a big problem for Entity Framework Code First database initialization when code first needs to drop and recreate a database. I have seen this consistently with (localdb)/v11.0 databases. In SSMS, you can solve this problem by expanding tables in a different database. But in SSOE, this won’t work. I have also disconnected the server instance and then reconnected it but the database is still “in use” and cannot be detached or deleted.

I mainly work in Management Studio when I work with SQL Server and the same behavior goes there as well. If you change your model and use the initializer DropCreateDatabaseIfModelChanges, you will be presented with:

Since I don’t have these issues with SisoDb that I develop, I thought, “there has to be a good workaround”. Well, if it’s good or not I don’t know, but here it goes. I just had a quick look at Codeplex at the source code for DropCreateDatabaseIfModelChanges and made my own initializer that works.

public class ReallyDoDropCreateDatabaseIfModelChanges 
    : IDatabaseInitializer where TContext : DbContext
{
    protected const string Sql =
        "if (select DB_ID('{0}')) is not nullrn"
        + "beginrn"
        + "alter database [{0}] set offline with rollback immediate;rn"
        + "alter database [{0}] set online;rn"
        + "drop database [{0}];rn"
        + "end";

    public virtual void InitializeDatabase(TContext context)
    {
        if (DbExists(context))
        {
            if (context.Database.CompatibleWithModel(false))
                return;

            DropDatabase(context);
        }

        context.Database.Create();
        Seed(context);
        context.SaveChanges();
    }

    protected virtual bool DbExists(TContext context)
    {
        using (new TransactionScope(TransactionScopeOption.Suppress))
        {
            return context.Database.Exists();
        }
    }

    protected virtual void DropDatabase(TContext context)
    {
        context.Database.ExecuteSqlCommand(
            string.Format(Sql, context.Database.Connection.Database));
    }

    protected virtual void Seed(TContext context) { }
}

This will work even if I have an query window against my db in Management studio. Of course it get’s a bit slower if the db is in use and the model has been updated, but it beats manual workarounds.

This was tested with Entity framework 5.0, SQL Server 2012

//Daniel