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.