Cannot drop database with ef

If you have done testing with Entity Framework you might have come across "Cannot drop database XX because it is currently in use.", EFUtilities can solve that for you.

Typically this situation occur if you debug something by querying the database in Sql Server Management Studio. Management studio then holds an open connection and once it's time to clean up for the next test you get the error message:

Cannot drop database "Nogginator.Test" because it is currently in use.

I got slightly tired of this an built a helper function that kills all connections to the database so it always can be delete.

To use it you only need to make a small change to your Delete statement.

db.Database.Delete(); //Old code

db.Database.ForceDelete(); //New code

Read more at https://github.com/MikaelEliasson/EntityFramework.Utilities#dbdatabaseforcedelete

The requirement is that you have rights to the master database. The code that's run looks like this:

public static void ForceDelete(this Database db, string name = null)
{
    name = name ?? GetDatabaseName(db.Connection);
    using (SqlConnection sqlconnection = new SqlConnection(db.Connection.ConnectionString)) //Need to run this under other transaction
    {
        sqlconnection.Open();
        // if you used master db as Initial Catalog, there is no need to change database
        sqlconnection.ChangeDatabase("master");

        string rollbackCommand = @"ALTER DATABASE [" + name + "] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;";

        SqlCommand deletecommand = new SqlCommand(rollbackCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();

        string deleteCommand = @"DROP DATABASE [" + name + "];";

        deletecommand = new SqlCommand(deleteCommand, sqlconnection);

        deletecommand.ExecuteNonQuery();
    }
}