51

I have an MVC3 and EF 4 Code First application, which is configured to change the DB when the model changes, by setting the DB Initializer to a DropCreateDatabaseIfModelChanges<TocratesDb>, where TocratesDb is my derived DbContext.

I have now made a change to the model, by adding properties to a class, but when EF tries to drop and recreate the DB, I get the following error:

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

I have absolutely no other connections anywhere open on this database. I assume that my cDbContext still has an open connection to the database, but what can I do about this?

NEW: Now my problem is how to re-create the database based on the model. By using the more general IDatabaseInitializer, I lose that and have to implement it myself.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
ProfK
  • 49,207
  • 121
  • 399
  • 775
  • This was happening to me because I was calling Membership methods against the DB and that was creating a conflict. I resolved this by forcing the initializer to run and seed before using the Membership system. – Christopher Mar 15 '12 at 18:12
  • Chris, I have a similar problem but I am really new to asp .net. Would you be able to tell him how to force the initializer to run before the membership system? Thanks – Tripping Aug 08 '12 at 22:13
  • Take a look at the answer for this other similar question http://stackoverflow.com/q/7004701/247328 – Camilo Sanchez Jun 30 '13 at 13:32

6 Answers6

46

Your current context must have an opened connection to be able to drop the database. The problem is that there can be other opened connections which will block your db initializer. One very nice example is having opened any table from your database in management studio. Another possible problem can be opened connections in the connection pool of your application.

In MS SQL this can be avoided for example by switching DB to SINGLE USER mode and forcing all connections to be closed and incomplete transactions rolled back:

ALTER DATABASE Tocrates SET SINGLE_USER WITH ROLLBACK IMMEDIATE

You can create a new intializer which will first call this command and then drops the database. Be aware that you should handle a database connection by yourselves because ALTER DATABASE and DROP DATABASE must be called on the same connection.

Edit:

Here you have example using Decorator pattern. You can modify it and initialize inner initializer inside the constructor instead of passing it as a parameter.

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);
    }
}
user247702
  • 23,641
  • 15
  • 110
  • 157
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • I have no SSMS connections open on the DB, but my problem is the Initializer offers no proper hook where I can execute your recommended code, not to mention I should not be doing the frameworks housekeeping. – ProfK Mar 13 '11 at 12:27
  • It provides the hook - you can implement custom `IDatabaseInitializer` and you can register it in `context.Database.SetInitializer()`. – Ladislav Mrnka Mar 13 '11 at 12:36
  • Ah yes, I actually found that a short while ago, and am trying it now. – ProfK Mar 13 '11 at 13:46
  • Now my problem is how to re-create the database based on the model. By using the more general IDatabaseInitializer, I lose that and have to implement it myself. – ProfK Mar 13 '11 at 14:03
  • This answer is OK. But doesn't seem to work real well with seeding. Also, the innerInitializer stuff is not necessary if you subclass the Initialization strategy you want. – mlibby Jan 25 '12 at 19:42
  • @mcl: This answer is about deleting database in use. I don't know what you mean by seeding - you can seed data in another initializer passed to this wrapper. Initializer stuff is needed because subclassing initializer will not help - you must run custom SQL prior to running initializers code - that is not possible with subclassed initializer because `InitializeDatabase` method is not virtual. – Ladislav Mrnka Jan 25 '12 at 19:49
  • @ladislav, I entered what seems to work for me in a separate answer. Am I missing something? – mlibby Jan 25 '12 at 19:56
  • @ladislav clearly I did miss something with what I was doing, thank you for your patience. – mlibby Jan 26 '12 at 00:29
  • This code will, however, always drop and create the database. I think it's going to be challenging to convert it to only do this if the model changes. – ProfK May 30 '13 at 12:12
  • Using this example, I couldn't get in the database myself anymore. I fixed that by setting it back to MULTI_USER in the end of my script. – Jowen Oct 30 '13 at 13:46
  • @Jowen: It seems like your database wasn't really dropped and recreated but just modified. – Ladislav Mrnka Oct 30 '13 at 14:24
  • I have implemented this but on occasion I get the error System.Data.SqlClient.SqlException was unhandled by user code HResult=-2146232060 Message=ALTER DATABASE statement not allowed within multi-statement transaction. – Jeff Pearce Mar 09 '14 at 09:24
41

I found in EF 6 this fails with an ALTER DATABASE statement not allowed within multi-statement transaction error.

The solution was to use the new transaction behavior overload like this:

context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "ALTER DATABASE [" + context.Database.Connection.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
Kevin Kuszyk
  • 1,958
  • 24
  • 37
  • 2
    Great comment. Fixed my problems with EF 6 code first database creation when issuing ALTER DATABASE to SET ALLOW_SNAPSHOT_ISOLATION ON. Thanks! – RaoulRubin Dec 04 '13 at 17:15
  • Love SO for finding little time-saving gems like this one... Thanks for sharing! – Dav May 08 '14 at 16:25
  • 1
    It also makes sense to switch DB back to multi-user mode after re-creation, like: context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "ALTER DATABASE [" + context.Database.Connection.Database + "] SET MULTI_USER WITH ROLLBACK IMMEDIATE"); – Corwin Dec 20 '14 at 17:25
  • 1
    This was extremely convenient for me to use. Just dropped it one line before my context.Database.Delete() call and it sorted my problem. – 4imble Mar 22 '16 at 10:09
22

I had the same issue.

I resolved it by closing a connection open under the Server Explorer view of Visual Studio.

psy
  • 2,791
  • 26
  • 26
13

I realize this is dated but I couldn't get the accepted solution working so I rolled a quick solution...

using System;
using System.Data.Entity;

namespace YourCompany.EntityFramework
{
    public class DropDatabaseInitializer<T> : IDatabaseInitializer<T> where T : DbContext, new()
    {
        public DropDatabaseInitializer(Action<T> seed = null)
        {
            Seed = seed ?? delegate {};
        }

        public Action<T> Seed { get; set; }

        public void InitializeDatabase(T context)
        {
            if (context.Database.Exists())
            {
                context.Database.ExecuteSqlCommand("ALTER DATABASE [" + context.Database.Connection.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
                context.Database.ExecuteSqlCommand("USE master DROP DATABASE [" + context.Database.Connection.Database + "]");
            }

            context.Database.Create();

            Seed(context);
        }
    }
}

This works for me and supports seeding easily.

Dave Jellison
  • 924
  • 13
  • 22
  • This errors for me as it says database already exists using VS2012 – Jon Feb 26 '13 at 11:28
  • Do you have access to master when you're authenticating? That's critical for USE master DROP DATABASE – Dave Jellison Mar 01 '13 at 20:17
  • 4
    @DaveJellison I tried your solution but it gives me System.Data.SqlClient.SqlException : ALTER DATABASE statement not allowed within multi-statement transaction. Any ideas ? – ashutosh raina Apr 14 '13 at 09:52
  • 1
    Actually, you should wrap the database name variable in brackets like `context.Database.ExecuteSqlCommand("ALTER DATABASE [" + context.Database.Connection.Database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");` Otherwise, I received an error complaining about the WITH keyword. – Nullius Jul 21 '13 at 11:48
  • @ashutoshraina Try to add the TransactionalBehavior.DoNotEnsureTransaction param to the ExecuteSqlCommand method. More info: http://stackoverflow.com/questions/21699075/alter-database-in-entity-framework-6 – Jowen Jan 26 '15 at 12:23
3

In Visual Studio 2012, the SQL Server Object Explorer window can hold a connection to the database. Closing the window and all windows opened from it releases the connection.

Edward Brey
  • 40,302
  • 20
  • 199
  • 253
0

A simple closing of my whole project and reopening it did the trick for me. It's the easiest way to make sure there are no connections still open

user6302183
  • 1