Entity Framework, Linq, ObjectContext and Deferred Execution question?

Question

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,

1. If I use the second approach, will Linq2EF know to close my connection after the above method finishes?
2. With the second approach, ObjectContext is never Disposed, would this be a problem on large sites?
3. 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?

Answer 1

I tend to think of the EF ObjectContext as the DAL, which gets around this issue. If you want your own DAL to encapsulate the EF stuff (not a horrible idea, IMO), I would suggest making your DAL implement IDisposable.

Answer 2

If you are in the context of a website, you should seriously consider making it so that your ObjectContext is alive throughout the request.

You need to keep the ObjectContext around for lazy loading, now way around that.

Connections are opened and closed as needed during queries and SaveChanges, so you should not have a "connection leak".

Answer 3

if you want and IQueryable you can do this by querying against the ToList() result and return that

Answer 4

Regarding your questions:

1. It will close the open and close the connection with the ToList, since all the enumeration is happening at that place. Only place this can get you with a long opened connection, is if you enumerate over it and run some semi-long processing for each item.
2. For normal large sites and normal operations, you will be ok with this. The main thing that can get you into trouble is entity tracking, which involves any entity you have loaded. If you are either loading too much information or have a very large site, you can avoid using it (don't sweat it for a normal site).
3. You can have your data access class implement IDisposable, and dispose the datacontext when it is being disposed. So in your GetUsers you can put an using around your data access class instead.