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
– BrokenGlass, Commented Jun 21, 2012 at 4:27

Damir Arh · Accepted Answer · 2012-06-21 06:54:24Z

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, 2012 at 6:54

answered Jun 21, 2012 at 4:47

Damir Arh

17.9k2 gold badges49 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user990635 Over a year ago

When I did that I got: Cannot access a disposed object. Object name: 'DataContext accessed after Dispose.'.

Gustavo Gondim Over a year ago

Maybe the IQueryable return is still trying to query over DataContext.

user990635 Over a year ago

I get it when I press F11 to get to the "if (result.HasValue)" line after it executed the method

Damir Arh Over a year ago

You're right, the error you got was to be expected. I changed the code a bit and now it should work.

Collectives™ on Stack Overflow

Exception in a Linq query (MVC2)

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related