Suppose we want to count how many counties are there in Texas:
var counties = dbContext.States.Single(s => s.Code == "tx").Counties.Count();
The query is correct, but inefficient.
States.Single(…) loads a state from the database. Next,
Counties loads all 254 counties with all of their fields in a second query.
.Count() is then performed in memory on the loaded
We've loaded a lot of data we don't need, and we can do better:
var counties = dbContext.Counties.Count(c => c.State.Code == "tx");
Here we only do one query, which in SQL translates to a count and a join. We return only the count from the database - we've saved returning rows, fields, and creation of objects.
It is easy to see where the query is made by looking at the collection type: