Linq - "var" Falling Out Of Scope

[pgrammer]

Hello,

I have a issue with a Linq query return falling out of scope. I am using the "using" block when initializing the data context so that it is disposed correctly. However, the "using" block is causing another issue down the road.

Example Code: Using a Stored Procedure

public object SelectAllEmployees()
{
using(DatabaseContext dbContext = new DatabaseContext())
{
var assocQuery = dbContext.SelectAllAssociates // accessing stored procedure.
return assocQuery
}
}


Now, I have done this exact thing with other objects that need disposing, but for some reason when using it with Linq it causes assocQuery to fall out of scope before the value is returned. I can fix the code by using the following example:


public object SelectAllEmployees()
{
var assocQuery = dbContext.SelectAllAssociates // accessing stored procedure.
return assocQuery
}


But the problem is this object doesn't appear to be disposed correctly, and my software runs for months without being shutdown, and sometimes these are causing issues. I need them disposed correctly. I tried to Dispose() it manually, however, I cannot place any statements after the "return" statement.

I guess this happens because assocQuery still has a connection to the database context even after the query has been run. Because this code will work normally with other objects that need disposing. Once the database context falls out of scope it is impossible to return the contents of assocQuery.

The second part of this is, that I would like to return a DataSet instead of a generic object. The return value of this function is being injected directly into a DataGridView and I think it's a bad idea to use "object". I have been trying for a few days to convert assocQuery to a DataSet and found many examples online, but none of them work correctly when I use them. I see that assocQuery has many properties like ".Cast<>" but I can't find anything that will convert it to a DataSet. It seems like a pretty basic operation. If I could convert this to a DataSet it would also solve my original problem, of returning a null reference. I could use the "using" block normally writting casting the contents to a DataSet, and returning the contents of the DataSet allowing the database context to dispose normally.

Otherwise, I think I need to make a copy of the data contained within assocQuery to another object that doesn't require disposing, without using a reference copy. I'm not sure how to go about doing that. I thought I would use the "&" operator or similar, but that may have been C++.

Any ideas?

[BigEd781]

I don't know LINQ, so I don't know what SelectAllAssociates returns. If it returns a list or collection of some type, you can always clone it ("CopyTo" for a generic list, but you really just need to clone each element of the collection)

[pgrammer]

I am unsure of how to do that because the return from Linq does not expose a "CopyTo()" method.

I also have no idea what Linq returns. I cannot figure it out, its not castable to any type that I have tried. Yet, what ever it is, it's directly bindable to a DataGridView control so it must be some type of DataSet.

Thanks.

[boudino]

Don't forget, that LINQ is lazy executed - the SelectAllAssociates and corresponding stored procedure are not called until the result is needed. So you cannot use the using statement, because it calls Dispose when the method returns, but it is probably sooner then the SelectAllAssociates should be executed.

I would suggest to force the execution before the method returns by calling a conversion method:

public object SelectAllEmployees()
{
using(DatabaseContext dbContext = new DatabaseContext())
{
var assocQuery = dbContext.SelectAllAssociates // accessing stored procedure.
return assocQuery.ToList();
}
}

[pgrammer]

Thanks,

I will try this when I get home and see what happens. Do you know of any way to convert the result to a DataSet or even a DataTable instead of a List? I have been unable to find an easy method to do so.

[boudino]

What about LINQ to ADO.NET (http://msdn.microsoft.com/en-us/library/bb397942.aspx)?

[eclipsed4utoo]

instead of an "object" datatype, you can use the IQueryable type.


public IQueryable SelectAllEmployees()
{
using(DatabaseContext dbContext = new DatabaseContext())
{
var assocQuery = dbContext.SelectAllAssociates // accessing stored procedure.
return assocQuery.AsQueryable();
}
}


This will also directly bind to a DataGridView.