c# - 使用 EF4(edmx 模型)时偶尔出现 "The underlying provider failed on Open"错误

Question

我希望有人能帮我解决以下错误。发生错误的应用程序正在生产中运行，我自己从未遇到过错误。然而，我每天大约有 20 次收到错误邮件，告诉我:

The underlying provider failed on Open. ---> System.InvalidOperationException: The connection was not closed. The connection's current state is connecting.

这是堆栈跟踪

System.Data.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: The connection was not closed. The connection's current state is connecting. at System.Data.ProviderBase.DbConnectionBusy.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at HibernatingRhinos.Profiler.Appender.ProfiledDataAccess.ProfiledConnection.Open() at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) --- End of inner exception stack trace --- at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure) at System.Data.EntityClient.EntityConnection.Open() at System.Data.Objects.ObjectContext.EnsureConnection() at System.Data.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption) at System.Data.Objects.ObjectQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable1 source) at System.Data.Objects.ELinq.ObjectQueryProvider.b__1[TResult](IEnumerable1
sequence) at
System.Data.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable1 query, Expression queryRoot) at System.Data.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
at GuideSites.DomainModel.Repositories.ClinicTermRepository.GetClinicTermByGuideSiteId(Int32 guideSiteId) in C:\Projects\GuideSites\GuideSites.DomainModel\Repositories\ClinicTermRepository.cs:line 20 at GuideSites.Web.Frontend.Helpers.VerifyUrlHelper.RedirectOldUrls() in C:\Projects\GuideSites\GuideSites.Web.Frontend\Helpers\VerifyUrlHelper.cs:line 91 at GuideSites.Web.Frontend.MvcApplication.Application_BeginRequest(Object sender, EventArgs e) in C:\Projects\GuideSites\GuideSites.Web.Frontend\Global.asax.cs:line 412 at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

我通过 EDMX 模型使用 EF4，我连接到数据库 (MS SQL 2008) 的方式是通过基于 HttpContext 的每个请求对象上下文，这样就不会为每个请求打开和关闭与数据库的连接我在给定页面加载时需要的数据。

我的数据库上下文类如下所示:

public class DatabaseContext : IDisposable
{
    private const string ContextName = "context";
    private static dbEntities _dbEntities;

    public dbEntities GetDatabaseContext()
    {
        SqlConnection.ClearAllPools();

        if (HttpContext.Current == null)
            return _dbEntities ?? (_dbEntities = new dbEntities());

        if (HttpContext.Current.Items[ContextName] == null)
            HttpContext.Current.Items[ContextName] = new dbEntities();

        _dbEntities = (dbEntities)HttpContext.Current.Items[ContextName];
        if (_dbEntities.Connection.State == ConnectionState.Closed)
        {
            _dbEntities.Connection.Open();
            return _dbEntities;
        }

        return _dbEntities;
    }


    public void RemoveContext()
    {
        if (HttpContext.Current != null && HttpContext.Current.Items[ContextName] != null)
        {
            ((dbEntities)HttpContext.Current.Items[ContextName]).Dispose();
            HttpContext.Current.Items[ContextName] = null;
        }

        if (_dbEntities != null)
        {
            _dbEntities.Dispose();
            _dbEntities = null;
        }
    }


    public void Dispose()
    {
        RemoveContext();
    }

}

在我的存储库中，我使用这样的数据库上下文:

public class SomeRepository
{
    private static readonly object Lock = new object();
    private readonly dbEntities _dbEntities;

    public SomeRepository()
    {
        var databaseContext = new DatabaseContext();
        _dbEntities = databaseContext.GetDatabaseContext();
    }


    public IEnumerable<SomeRecord> GetSomeData(int id)
    {
        lock (Lock)
        {
            return
                _dbEntities.SomeData.Where(c => c.Id == id);
        }
    }
 }

我读到的关于锁(锁)的东西应该有助于解决这个问题，但在我的情况下却没有。通常很难找到能够准确描述我的问题的话题，更不用说问题的解决方案了。

该应用程序是一个 ASP.NET MVC3 应用程序，它被设置为一个为 9 个不同网站运行的应用程序(域决定了要提供给客户端的内容)。这9个网站每天的浏览量都不超过2000，所以数据库应该压在那个账户上。

我希望有人能提供帮助，如果有什么我忘记说的，请告诉我。

Answer 1

根据我的评论，Dispose() 必须在请求结束时由某些东西调用。您可以像这样使用 HttpModule 执行此操作:

public class ContextDisposer : IHttpModule
{
    private readonly DatabaseContext _context = new DatabaseContext();

    public void Init(HttpApplication context)
    {
        context.EndRequest += (sender, e) => this.DisposeContext(sender, e);
    }

    private static bool DoesRequestCompletionRequireDisposing(
        string requestPath)
    {
        string fileExtension = Path.GetExtension(requestPath)
            .ToUpperInvariant();

        switch (fileExtension)
        {
            case ".ASPX":
            case string.Empty:
            case null:
                return true;
        }

        return false;
    }

    private void DisposeContext(object sender, EventArgs e)
    {
        // This gets fired for every request to the server, but there's no 
        // point trying to dispose anything if the request is for (e.g.) a 
        // gif, so only call Dispose() if necessary:
        string requestedFilePath = ((HttpApplication)sender).Request.FilePath;

        if (DoesRequestCompletionRequireDisposing(requestedFilePath))
        {
            this._context.Dispose();
        }
    }
}

然后像这样将模块插入请求管道(将其放入 system.web 和 system.webserver，以便它包含在 IIS 和 VS 开发 Web 服务器中):

<system.web>
    <httpModules>
        <add name="ContextDisposer" 
             type="MyNamespace.ContextDisposer" />
    </httpModules>
</system.web>

<system.webServer>
    <modules runAllManagedModulesForAllRequests="true">
        <add name="ContextDisposer" 
             type="MyNamespace.ContextDisposer" />
    </modules>
</system.webServer>