EF Code First - navigation property counts

Watch out for unbounded result sets when using navigation properties.

using (var context = new NorthwindContext()))
{
    var category = context.Categories.Find(1); //Beverages
    Console.WriteLine(category.Products.Count);

}

This loads ALL the products for the category, and then counts them.

Fine for a small result set, not so good if you have 1000s of products per category.

Simple solution: use a filter on products.

Console.WriteLine(context.Products
    .Count(p => p.Category.CategoryId == category.CategoryId));

This generates SQL in the form "SELECT COUNT(*) FROM Products WHERE CategoryID = @p"

Alternative solution: use context.Entry with .Query()

Console.WriteLine(context.Entry(category)
    .Collection(x => x.Products)
    .Query()
    .Count());

static void

EF Code First - navigation property counts