I'm new to Linq to EntityFramework, one question in Linq2EntityFramework I have is when or if to dispose the ObjectContext. The reason I ask this question is that normally in my DAL I have code like
public List<User> GetUsers()
{
using (MyEntities db = new MyEntities()) //where MyEntities inherits ObjectContext.
{
// do some linq select operation which returns IQueryable<User>
// call ToList() on the return IQueryable which is when the DB is really accessed
// then we return List<User>
}
// after using statement a Dispose method on ObjectContext is called, hence disposed the ObjectContext, and in turn it closes my DB connection and releases it to the pool
}
Now, I don't want to do this, I want my DAL return IQueryable to my BLL instead of List, so that my BLL can do filtering like Skip() on the returned IQueryable then call ToList() in the BLL. So the code becomes like this,
public IQueryable<User> GetUsers()
{
// do some linq select operation which returns IQueryable<User>
// then just return what we got back
// Note: no DB access occurred here, this is called deferred execution, because the real DB access happens later in BLL
}
Then in my BLL I have a method like
public List<User> GetUsers()
{
// get IQueryable<User> from DAL to a var say users
return users.Skip(10).Take(20).ToList(); // here the DB access really happens. Note: if we put using in DAL, here will throw exception saying DB is already closed!!
}
My questions are,
- If I use the second approach, will Linq2EF know to close my connection after the above method finishes?
- With the second approach, ObjectContext is never Disposed, would this be a problem on large sites?
- Or how do I get IQuerayable back but still dispose the ObjectContext in DAL?
Thank you so much, Ray.
Edit: One thing I don't understand is that if the connection is smartly managed by the ObjectContext, then is it OK to just not disposing the ObjectContext? What other things beside the ObjectContext manage?
