DataReader already open when using LINQ

Question

I've got a static class containing a static field which makes reference to a wrapper object of a DataContext.

The DataContext is basically generated by Visual Studio when we created a dbml file & contains methods for each of the stored procedures we have in the DB.

Our class basically has a bunch of static methods that fire off each of these stored proc methods & then returns an array based on a LINQ query.

Example:

public static TwoFieldBarData[] GetAgesReportData(string pct)
        {
                return DataContext
                .BreakdownOfUsersByAge(Constants.USER_MEDICAL_PROFILE_KEY, pct)
                .Select(x => new TwoFieldBarData(x.DisplayName, x.LeftValue, x.RightValue, x.TotalCount))
                .ToArray();
        }

Every now and then, we get the following error:

There is already an open DataReader associated with this Command which must be closed firs

This is happening intermittently and I'm curious as to what is going on. My guess is that when there's some lag between one method executing and the next one firing, it's locking up the DataContext and throwing the error.

Could this be a case for wrapping each of the DataContext LINQ calls in a lock(){} to obtain exclusivity to that type and ensure other requests are queued?

Answer 1

DataContext is not meant to be long-lived, and definitely is not meant to be used with multiple threads.

A static member is long-lived and is accessed by multiple threads. That conflict could be the root of your problem.

Answer 2

Locking the data context could be problematic, it could really bust up your performance or give you deadlocks if you're not careful.

This usually happens when you are cycling though records in a reader, and then try to run another query command within that (e.g. loading a hierarchical object model)

Try turning adding the MultipleActiveResultSets=True parameter to your connection string if possible, that usually solves the problem more cleanly than trying seperate your calls.

http://msdn.microsoft.com/en-us/library/cfa084cz(VS.80).aspx

Also, use your data context for the smallest scope possible, and single "unit of work" if you will. Unless you need to maintain some state, (like loading some objects and then saving them back all at once), you shouldn't be reusing the data context very much.

Answer 3

As Bryan Watts pointed out. DataContext should be short lived. Make sure you are only using the DataContext when required and for a small period of time.

using(var db = new DataContext()) 
{
    // do your things here! 
}

Hope it helps!