Disposing datacontext causes Invalid attempt to call Read when reader is closed

Question

I'm building an MVC 2 app and using linq to sql with stored procs.

I created a data access layer that has an internal datacontext class and a public class that I expose applications. In my public class I expose methods which access the datacontext class and convert the data to my own object model classes using linq.

In my public class, I would expose a method using the following patter:

public IEnumerable<MyObject> ListObjects(int iParameter)
{
    using (MyDataContext db = new MyDataContext)
    {
        //call stored proc and convert results to my object model
        return db.List_Objects().Select(o => new MyObject()
            {
                ID = o.ID,
                Name = o.Name
                Text = o.Code + " " + o.Description
            };
    } 
}

My MVC app would call this method from a model class, and the aspx would iterate through the results. I found that I always get an error "datacontext causes Invalid attempt to call Read when reader is closed" because I wrap my data context usage inside a using scope. If I don't scpope everything in a using clause it works fine. Why is this?

I think this is not necessarily a linq or mvc thing (but don't know for sure), is the using clause causing the dispose to be called before all the objects are returned? Or maybe the select clause is only executing as the enumerator is being iterated through similar to how yield works?

Answer 1

Linq to Sql uses the unit of work pattern to encapsulate access to the database which on dispose (end of using scope) closes the connection to the database, the reason it works when you dont wrap the statement is the context is still alive when enumarating the query (which may be bad as it may lead to the connection remaining open), it throws because the execution will only occur when you first use the IEnumerable which may be somewhere down the line as far as the view, what you need to do is transform the IEnumerable to a list using ToList() which will force execution immediately instead of delaying it so the connection will close and you will have your collection.

Answer 2

The using lasts for the invocation of the method, so if you have this:

var objects = ListObjects(123);

Then the DataContext has been created and disposed, but no results have been returned yet.

When you start enumerating results:

foreach(var o in objects)

The method expression tree starts executing the query, but the datacontext is already disposed, and therefore can't open a new connection.

Something like this will work, but it prevents the ability to 'extend' the query from outside ListObjects.

public IEnumerable<MyObject> ListObjects(int iParameter)
{
    List<MyObject> objects;
    using (MyDataContext db = new MyDataContext())
    {
        //call stored proc and convert results to my object model
        objects = (db.List_Objects().Select(o => new MyObject()
            {
                ID = o.ID,
                Name = o.Name
                Text = o.Code + " " + o.Description
            }).ToList();
    } 
    return objects;
}

If you understand when to dispose the context, and when it's OK not to, it's safe to do so IMO.