entity framework navigation property further filter without loading into memory

Question

Hi,

I've two entities with 1 to N relation in between. Let's say Books and Pages. Book has a navigation property as Pages. Book has BookId as an identifier and Page has an auto generated id and a scalar property named PageNo. LazyLoading is set to true.

I've generated this using VS2010 & .net 4.0 and created a database from that. In the partial class of Book, I need a GetPage function like below

public Page GetPage(int PageNumber)
{
     //checking whether it exist etc are not included for simplicity
     return Pages.Where(p=>p.PageNo==PageNumber).First();
}

This works. However, since Pages property in the Book is an EntityCollection it has to load all Pages of a book in memory in order to get the one page (this slows down the app when this function is hit for the first time for a given book). i.e. Framework does not merge the queries and run them at once. It loads the Pages in memory and then uses LINQ to objects to do the second part

To overcome this I've changed the code as follows

  public Page GetPage(int PageNumber)
    {
          MyContainer container = new MyContainer();
          return container.Pages.Where(p=>p.PageNo==PageNumber && p.Book.BookId==BookId).First();
    }

This works considerably faster however it doesn't take into account the pages that have not been serialized to the db.

So, both options has its cons. Is there any trick in the framework to overcome this situation. This must be a common scenario where you don't want all of the objects of a Navigation property loaded in memory when you don't need them.

Answer 1

Trick? Besides "Try both?"

public Page GetPage(int pageNumber)
{
     // check local values, possibly not persisted yet. 
     // works fine if nothing loaded.
     var result = Pages.Where(p => p.PageNo == pageNumber).FirstOrDefault();
     if (result != null) 
     {
         return result;
     }
     // check DB if nothing found
     MyContainer container = new MyContainer();
     return container.Pages.Where(p => p.PageNo == pageNumber && p.Book.BookId==BookId).First();
}

There's nothing to do this automatically except for the specific case of loading by the PK value, for which you can use ObjectContext.[Try]GetObjectByKey.