Tuesday, 2 August 2011

How to Get rid of N+1 problem in Linq To Entities in C#

For example, if we have two tables Country and cities then you'll write code like:




  1. foreach (var country in countriesEntities.Countries)
  2. {
  3.     Console.WriteLine(country.CountryName + "\nCities:");
  4.  
  5.     foreach (var city in country.Cities)
  6.     {
  7.         Console.WriteLine("{0}", city.CityName);
  8.     }
  9. }



Now, Imagine there are 100 Countries and each Country has at least 100 Cities, then at least 10000 queries will be executed. Lot of performance effect. 


Fortunately there is a way to avoid this problem. Using Include(…) method only one query is made to the Database. No Extra queries will be made to the database Now your code will be:


  1. foreach (var country in countriesEntities.Countries.Include("Cities"))
  2. {
  3.     Console.WriteLine(country.CountryName + "\nCities:");
  4.     foreach (var city in country.Cities)
  5.     {
  6.         Console.WriteLine(" {0}", city.CityName);
  7.     }
  8. }



Please do like/Comment/share if you like this.


3 comments: