danielwertheim

danielwertheim


notes from a passionate developer

Developer that lives by the mantra "code is meant to be shared".

Share


Tags


Disclaimer

This is a personal blog. The opinions expressed here represent my own and not those of my employer, nor current or previous. All content is published "as is", without warranty of any kind and I don't take any responsibility and can't be liable for any claims, damages or other liabilities that might be caused by the content.

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

Daniel WertheimDaniel Wertheim

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

tweet

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:

SqlException

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

Developer that lives by the mantra "code is meant to be shared".

Comments