Entity Framework Optimization Techniques in EF Execute queries in the database when possible, not in memory.
Example
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 Counties collection.
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: IQueryable<T> vs. IEnumerable<T>.
