数据库在使用错误时先用实体框架4代码

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.

我有一个MVC3和EF 4代码第一应用程序，它被配置为在模型更改时更改DB，方法是将DB初始化设置为DropCreateDatabaseIfModelChanges ，其中TocratesDb是我的派生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:

我现在对模型做了一个修改，将属性添加到一个类中，但是当EF试图删除并重新创建DB时，我得到以下错误:

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?

在这个数据库上，我绝对没有任何其他连接。我假设我的cDbContext仍然有一个到数据库的开放连接，但是我能做些什么呢?

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.

新:现在我的问题是如何基于模型重新创建数据库。通过使用更通用的IDatabaseInitializer，我丢失了它，必须自己实现它。

6 个解决方案

#1

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.

您当前的上下文必须有一个打开的连接才能删除数据库。问题是可能有其他打开的连接会阻塞您的db初始化器。一个很好的例子是在management studio中打开数据库中的任何表。另一个可能的问题可以在应用程序的连接池中打开连接。

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:

在MS SQL中，可以避免这种情况，例如将数据库切换为单用户模式，并强制关闭所有连接，并回滚不完全的事务:

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.

您可以创建一个新的初始化器，它首先调用这个命令，然后删除数据库。请注意，您应该自己处理数据库连接，因为必须在相同的连接上调用ALTER database和DROP数据库。

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.

这里有一个使用Decorator模式的示例。您可以修改它并在构造函数中初始化内部初始化器，而不是将它作为参数传递。

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);
    }
}

#2

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

我在EF 6中发现，在多语句事务错误中不允许使用ALTER数据库语句，这是失败的。

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");

#3

I had the same issue.

我也有同样的问题。

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

我通过关闭在Visual Studio的Server Explorer视图下打开的连接来解决这个问题。

#4

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.

这对我很有效，而且很容易支持播种。

#5

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.

在Visual Studio 2012中，SQL Server Object Explorer窗口可以保存到数据库的连接。关闭窗口和从窗口中打开的所有窗口将释放连接。

#6

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

一个简单的结束我的整个项目和重新打开它为我做了窍门。这是确保没有连接仍然打开的最简单的方法

#1