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.