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

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

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?

Solution

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();