entity-frameworkcollectionspaginationef-code-first

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