entity framework navigation property further filter without loading into memory
- by cellik
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.
