Entity Framework 4 Code First error: database in use

code-first ef-code-first entity-framework sql

Question

By setting the DB Initializer to a value of 1, my MVC3 and EF 4 Code First application is set up to modify the database whenever the model changes.DropCreateDatabaseIfModelChanges<TocratesDb> , whereTocratesDb which I derivedDbContext .

My model has since been modified by the addition of properties to a class, however when EF attempts to dump and restore the database, I get the following error:

Cannot drop database "Tocrates" because it is currently in use.

There aren't any other connections open at all on this database for me. What can I do about the fact that my cDbContext still seems to be connected to the database?

NEW: My current challenge is figuring out how to rebuild the database using the model. I lose that when I use the more all-encompassing IDatabaseInitializer and have to build it myself.

1
51
3/13/2011 5:29:14 PM

Accepted Answer

To be able to drop the database, your current context has to have an established connection. The issue is that there can be open connections that prevent your database initializer from working. Opening any table from your database in management studio is a pretty excellent example. Open connections in your application's connection pool are still another potential issue.

This may be prevented with MS SQL, for instance, by setting the database to SINGLE USER mode and mandating that all connections be terminated and completed transactions be rolled back:

ALTER DATABASE Tocrates SET SINGLE_USER WITH ROLLBACK IMMEDIATE

You may develop a new initializer that first executes this command before erasing the database. Be mindful that you must manage a database connection independently becauseALTER DATABASE and DROP DATABASE called on the same connection is required.

Edit:

You can see an example of the Decorator pattern here. Instead of giving it as a parameter, you may alter it and initialize the inner initializer inside the constructor.

public class ForceDeleteInitializer : IDatabaseInitializer<Context>
{
    private readonly IDatabaseInitializer<Context> _initializer;

    public ForceDeleteInitializer(IDatabaseInitializer<Context> innerInitializer)
    {
        _initializer = innerInitializer;    
    }

    public void InitializeDatabase(Context context)
    {
        context.Database.SqlCommand("ALTER DATABASE Tocrates SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
        _initializer.InitializeDatabase(context);
    }
}
46
4/26/2012 11:15:09 AM

Popular Answer

In EF 6, I discovered that this fails with anALTER DATABASE statement not allowed within multi-statement transaction error.

Utilizing the new transaction behavior overload in the following manner was the solution:

context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "ALTER DATABASE [" + context.Database.Connection.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow