LINQ to SQL Calling stored procedure

Question

Hi Guys,

I have created small test web application which makes use of LINQ to SQL. I have ObjectDataSource and GridView. GridView's Datasource is ObjectDataSource. Now this ObjectDataSource uses one class(Method Name:GetAllTasks() as mentioned below) called MyTasks to populate all the task from Tasks table in SQL using Linq to SQL. It makes call to stored procedure GetAllMyTasks().

I have following method which works perfectly.

 public static IEnumerable<GetAllMyTasksResult> GetAllTasks()
{
    MyTasksDataContext db = new MyTasksDataContext();
    var tasks = db.GetAllMyTasks().Select(x => x);
    return tasks;
}

Now if i try to replace above code with following code just to make use of using key word to create Disposable MyTasksDataContext object. it gives me error saying "Invalid attempt to call Read when reader is closed.". Is there anything which i am missing here.

 public static IEnumerable<GetAllMyTasksResult> GetAllTasks()
{
    using (MyTasksDataContext db = new MyTasksDataContext())
    {
        var tasks = db.GetAllMyTasks().Select(x => x);
        return tasks;
    }
}

Can anyone please give me reason behind this? I know that my MyTasksDataContext object is calling its dispose method.

Answer 1

Use the ToList() extension method to evaluate the enumeration early.

public static IEnumerable<GetAllMyTasksResult> GetAllTasks()
{
    using (MyTasksDataContext db = new MyTasksDataContext())
    {
        return db.GetAllMyTasks().ToList();
    }
}

This will cause the enumeration to happen inside the using, rather than after the connection is disposed.

The reason the enumeration needs to happen inside of the 'using'-block is that LINQ makes use of something called 'delayed execution' to allow for more powerful query writing.

For example, if you wanted a generic function to do paging, it could just attach the .Skip(30).Take(10) to the end of your result, and that logic can be baked into the resulting SQL.

PS:
You said:

I know that my MyTasksDataContext object is calling its dispose method.

That is false.

The 'using'-block is calling the Dispose method, not the object itself. And, since you wrote the using, you are calling Dispose.

Answer 2

As John Gietzen has mentioned, ToList() will solve your immediate problem.

The reason this is necessary is because of delayed execution. LINQ in general will not actually do anything until a query is iterated. LINQ to SQL will call the stored procedure, but will not read rows from the results, until the query is iterated.