static void

EF Code First - navigation property counts

Published Thursday 16 February 2012

Watch out for unbounded result sets when using navigation properties.

using (var context = new NorthwindContext()))
    var category = context.Categories.Find(1); //Beverages

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.

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

    .Collection(x => x.Products)

Previously: EF Code First tracing - logging the SQL (14 Feb 2012)