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

asp.net-mvclinq-to-sqlnetusingusing-statement

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?

Best Answer

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.

Related Topic