static void

EF Code First - navigation collection paging

Published Friday 17 February 2012

There is a risk of unbounded result sets when using navigation collections.

If you try to do paging (Skip/Take) on a navigation collection, you actually load all the related entities and then page in memory. Opps.
//unbounded result set - there could be 1000s!
var products = category.Products
    .OrderBy(p => p.ProductName) //you have to order
    .Skip((page - 1) * pageSize)
    .Take(pageSize);
You have the same issue with a simple .Count, as shown in my last post.

The solutions are the same. You can use a filter directly on the products DbSet.
var pagedProductsByCategory = context.Products
    //have to specify primary keys here- can't match on "category"
    .Where(p => p.Category.CategoryId == category.CategoryId)
    .OrderBy(p => p.ProductName) //you have to order
    .Skip((page - 1) * pageSize)
    .Take(pageSize);
Or you can use the context.Entry(x).Collection(y).Query(). This is the equivalent of an NHibernate CreateFilter.
var pagedProducts = context.Entry(category)
    //from the DbEntityEntry, get the navigation property
    .Collection(x => x.Products)
    //turn it into a query
    .Query()
    //page
    .OrderBy(p => p.ProductName) //you have to order
    .Skip((page - 1) * pageSize)
    .Take(pageSize);
To remove temptation, you might want to remove the collection navigation property. In this case, category has no Products collection (the many end of the foreign key), but Product has a Category property (the 0.1 end of the foreign key).

You can specify the mapping in an EntityTypeConfiguration<Product> class map. Because the many end isn't defined, you use an empty .WithMany().
HasOptional(x => x.Category)
    .WithMany() //.WithMany(c => c.Products)
    .Map(m => m.MapKey("CategoryID"));

Remember you can (and should) be profiling your generated SQL, for instance with the EFTracingProvder as shown here.




Previously: EF Code First - navigation property counts (16 Feb 2012)