In entity framework code first is there a way to add paging to navigation collections?

Question

If I have a Blog entity with a BlogEntries collection which may have hundreds of entries, is there a way to add any server-side paging functionality with EF code first? For example if I do a typical .Skip(x).Take(y) like you would on a DbSet, will it lazy load the entire collection and page it in memory?

Weatherbeaten

Answer 1

If you query directly DbSet you can use Take and Skip and it will indeed execute paging on database server (these method calls are translated to SQL). So this works as expected:

// Loads only 10 expected entries through Linq-to-entities
var entries = context.BlogEntries.OrderBy(e => e.Date).Skip(10).Take(10);

Beware that paging navigation properties on loaded entity doesn't work this way:

var blog = context.Blogs.First();
// Lazy loading always loads all related entries and executes ordering and 
// paging through Linq-to-objects!
var entires = blog.BlogEntries.OrderBy(e => e.Date).Skip(10).Take(10);

If you want to get paging on navigation property you must use explicit loading

var blog = context.Blogs.First();
var dbEntry = context.Entry(blog);
// This is the way to use Linq-to-entities on navigation property and 
// load only subset of related entities
var entries = dbEntry.Collection(b => b.BlogEntries)
                     .Query()
                     .OrderBy(e => e.Date)
                     .Skip(10)
                     .Take(10)
                     .Load();

Decerebrate

Thanks! Im glad there is a backdoor way to accomplish this with EF. I am hoping in future versions there will be a way to do this built in so we dont have to to go to the DbContext to accomplish this. Do you know if it does .Count() and .Any() without loading the collection (using select Count(1) and if Exists in SQL) like NHibernate does? – Weatherbeaten

Navigation property always loads all related entities even if you want just count them. To support Count you need extra loading. – Decerebrate

If we call .Load() again on the same data context for a different, it adds the next page to the collection instead of replacing it. Is there any way around this, apart from creating a new data context? – Afrikah