ExecuteReader requires open Connection. Current state: Broken

Question

I'm using StructureMap to inject a LINQ data context (DB class) into my repositories for read queries on a HTTP-context basis, which are queried via a service layer. Everything works fine for a few hours until I get a "Broken" Connection ExecuteReader error (see below for full dump).

Write queries (not read queries) are wrapped in a using (var db = new DB()) {...} statement, which I don't think cause the problem.

I've added MultipleActiveResultSets=true; to my connection string, which seemed to solve the problem for a while, but the error returns until I recycle my application pool, which temporarily solves the problem. Presumably recycling the pool frees all the "broken" data contexes.

StructureMap is configured to inject a data context and repository for each HTTP context:

For<DB>().HttpContextScoped().Use(new DB());
For<IUserRepository>().HttpContextScoped().Use<SqlUserRepository>();

The data context is consumed by my user repository like so:

private DB _db;
public SqlUserRepository(DB db)
{
    _db = db;
}

which, in turn conveniently queries the data context like so:

public IQueryable<User> GetUsers()
{
    var users = from u in _db.Users
                select u; // omitted the rest
}

Which after a few hours ends up with this horrible error:

System.InvalidOperationException: ExecuteReader requires an open and available 
Connection. The connection's current state: Broken. at 
System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at 
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior 
runBehavior, Boolean returnStream, String method, DbAsyncResult result) at 
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior 
runBehavior, Boolean returnStream, String method) at 
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at 
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at 
System.Data.Common.DbCommand.ExecuteReader() at 
System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, 
IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] 
subQueries, Object lastResult) at 
System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, 
IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at 
System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expressi
on query) at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression 
expression) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source) at 
MyApp.Data.SqlUserRepository.GetUser(String username) at 
MyApp.Services.BranchService.GetUser(String username) at 
MyApp.Controllers.BranchController.get_CurrentUser() at 
MyApp.Controllers.BranchController.OnActionExecuting(ActionExecutingContext filterContext) 
at 
System.Web.Mvc.Controller.System.Web.Mvc.IActionFilter.OnActionExecuting(ActionExecutingCon
text filterContext) at 
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, 
ActionExecutingContext preContext, Func`1 continuation) at 
System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<>c__DisplayClassf.b__c() at 
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext 
controllerContext, IList`1 filters, ActionDescriptor actionDescriptor, IDictionary`2 
parameters) at System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext 
controllerContext, String actionName)

How do I solve this? I really don't want to have to manually wrap every query in a using-statement.

Answer 1

It looks like you're unintentionally are creating a singleton of the Context:

For<DB>().HttpContextScoped().Use(new DB());

In order to create a new context per request, use this instead:

For<DB>().HttpContextScoped().Use(() => new DB());