LINQ to SQL Stored Procedure Call
I have created a small test web application that uses LINQ to SQL. I have an ObjectDataSource and a GridView. GridView Datasource is an ObjectDataSource object. Now this ObjectDataSource uses one class (method name: GetAllTasks () as mentioned below) called MyTasks to populate the entire task from the task table in SQL using Linq to SQL. It calls the GetAllMyTasks () stored procedure call.
I have the following method that works great.
public static IEnumerable<GetAllMyTasksResult> GetAllTasks()
{
MyTasksDataContext db = new MyTasksDataContext();
var tasks = db.GetAllMyTasks().Select(x => x);
return tasks;
}
Now if I try to replace the above code with the following code, just to use the keyword to create a Disposable MyTasksDataContext object. this gives me an error: "Invalid attempt to call Read when the reader is closed." There is something I don't see here.
public static IEnumerable<GetAllMyTasksResult> GetAllTasks()
{
using (MyTasksDataContext db = new MyTasksDataContext())
{
var tasks = db.GetAllMyTasks().Select(x => x);
return tasks;
}
}
Can anyone explain to me the reason for this? I know that my MyTasksDataContext object is calling its dispose method.
source to share
Use an extension method ToList()
to evaluate early enumeration.
public static IEnumerable<GetAllMyTasksResult> GetAllTasks()
{
using (MyTasksDataContext db = new MyTasksDataContext())
{
return db.GetAllMyTasks().ToList();
}
}
This will cause the enumeration to happen within the usage rather than after the connection has been established.
The reason the enumeration must occur within a "used" block is because LINQ uses something called "deferred execution" to provide more powerful query writing.
For example, if you want a generic function to do the swap, it can just bind .Skip(30).Take(10)
to the end of your result and that logic can be baked into the SQL result.
PS:
You said:
I know that my MyTasksDataContext object is calling its dispose method.
This is not true.
The "consuming" block calls the method Dispose
, not the object itself. And since you wrote the usage, you call Dispose
.
source to share
As John Gietzen pointed out, ToList () will solve your immediate problem.
The reason this is needed is delayed execution. LINQ will not do anything at all until the query is retry. LINQ to SQL will call the stored procedure, but will not read rows from the results until the query is repeated.
source to share