LINQ to SQL Calling stored procedure

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.

13.10.2009 16:01:46
just noting that Select(x => x) does absolutely nothing
Esben Skov Pedersen 13.10.2009 16:24:25
2 ОТВЕТА
РЕШЕНИЕ

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.

10
13.10.2009 16:16:27
you could use as Enumerable method too. whats the deal with lambda x=>x, from my understanding its not doing any transform.
Perpetualcoder 13.10.2009 16:07:58
@John: Thanks. This change did worked for me. But can you please give me reason why enumeration did not work in first place. By default method's return type is ISingleResult<GetAllMyTasksResult>. Now ISingleResult does implement IEnumerable<T>, IEnumerable.
Nirlep 13.10.2009 16:10:19
@Perpetualcode: i was using that to give method implementation for generic delegate Func<T.TResult> using lamda expression.
Nirlep 13.10.2009 16:13:31
Updated with an example of delayed execution being useful.
John Gietzen 13.10.2009 16:17:39

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.

3
13.10.2009 16:06:06
"LINQ to SQL will call the stored procedure, but will not read rows" Exactly! It will start the query in SQL Server and freeze it with PREEMPTIVE_OS_WAITFORSINGLEOBJEC until you use/dispose ISingleResult (with ToList() or Dispose())
Ivan Akcheurov 6.06.2014 09:57:06