ASP.NET Tips #66 - Beware hidden cursors

If you're using an ORM like Entity Framework, when you declare an object mapped to another table with foreign keys, you automatically get references of those related entities. Unfortunately, there is a significant hidden cost when accessing the related entities, as separate queries can be run to retrieve details of each referenced row. This is commonly called the n+1 select problem.

For example, consider the following code where we fetch a list of schools from a database then filter that list. On line 1, a query is run to retrieve a list of n schools. On line 2, for every item in the schools list, a query is run to retrieve the number of pupils at that school, so in total n+1 queries are run.

   List<School> schools = context.Schools.ToList();
   List<School> filteredSchools = schools.Where(s => s.Pupils.Count > 1000).ToList();

Consider using Eager Loading to avoid this scenario if you need to access properties of Pupils later:

   List<School> schools = context.Schools.Include(s => s.Pupils).ToList();

Or in this scenario, simply replace line 1 with:

   List<School> schools = context.Schools.Where(s => s.Pupils.Count > 1000).ToList();