Exception in a Linq query (MVC2)

Question

I get the following error coming from my Linq query:

ExecuteReader requires an open and available Connection. The connection's current state is open.

If it requires an open connection, and the connection is open... why is this an error?

The was a post on stackoverflow regarding this error, and the solution was:

"Essentially, there were a few 'rogue queries' in our application that were affecting the entire thing from working correctly, so we just had to go through every class (all 88 of them!) and check that they were written correctly."

I tried to find problems with my query and couldn't. I tested it and it got the right result..
Here is my query:

int? result = Helper.GetResult(b).FirstOrDefault();
if (result.HasValue)
     //something
-----------------------------------------------------------
Inside Helper: 

private static PingDbDataContext pingDataContext = new PingDbDataContext(); 
public static IQueryable<int?> GetResult(string b)
{
    pingDataContext.CommandTimeout = 5000;
    pingDataContext.Log = new DebugTextWriter();
    var q = from a in pingDataContext.SomeTable
            where a.SomeColumn == b
            select a.id;
    return q;
}

And here is the stack trace:

Exception information: 
    Exception type: InvalidOperationException 
    Exception message: ExecuteReader requires an open and available Connection. The connection's current state is open.
   at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
   at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   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.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(Expression query)
   at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at MyProject.Controllers.MyController.Index() in (path...)\MyProject\Controllers\MyController.cs:line 53
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.<>c__DisplayClassd.<InvokeActionMethodWithFilters>b__a()
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)
   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)
   at System.Web.Mvc.Controller.ExecuteCore()
   at System.Web.Mvc.MvcHandler.<>c__DisplayClass8.<BeginProcessRequest>b__4()
   at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass1.<MakeVoidDelegate>b__0()
   at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Are you sharing you data context between multiple threads by chance? Just re-create and use the data context locally with a using statement - it's cheap. — BrokenGlass, Jun 21 '12 at 04:27

Damir Arh · Accepted Answer · 2012-06-21T06:54:24.513

1

Since this is an MVC project and your data context is in a static variable, it could very well be a concurrency issue, like BrokenGlass suggests. Just to be sure, try creating the data context inside your method like this:

public static IEnumerable<int?> GetResult(string b)
{
    using (PingDbDataContext pingDataContext = new PingDbDataContext())
    {
        pingDataContext.CommandTimeout = 5000;
        pingDataContext.Log = new DebugTextWriter();
        var q = from a in pingDataContext.SomeTable
                where a.SomeColumn == b
                select a.id;
        return q.ToList();
    }
}

Does the problem still occur?

EDIT: I've rewritten the above method a little bit. You shouldn't be returning IQueryable outside the scope of data context. You need to retrieve the data from the database before you dispose of it. But I don't find a good to idea to expose IQueryable outside your data access code anyway. You would only need that if you wanted to modify the query being run outside of your method.

edited Jun 21 '12 at 06:54

answered Jun 21 '12 at 04:47

Damir Arh

17,637
2
45
83

When I did that I got: Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'. – user990635 Jun 21 '12 at 06:06
1

Maybe the IQueryable return is still trying to query over DataContext. – Gustavo Gondim Jun 21 '12 at 06:33
I get it when I press F11 to get to the "if (result.HasValue)" line after it executed the method – user990635 Jun 21 '12 at 06:35
You're right, the error you got was to be expected. I changed the code a bit and now it should work. – Damir Arh Jun 21 '12 at 06:55

Exception in a Linq query (MVC2)

1 Answers1