Most of my large projects are using Entity Framework. And Entity Framework is known to sometimes create crazy sql requests. Or rather it easily allows developers to create crazy queries. It is really easy to throw couple .Include()
into your query and then return objects as they come from EF:
var products = dbContext.Products.Include(p => p.Tags)
.Include(p => p.Reviews)
.ToList(); // first query
foreach(var prod in products)
{
var reviewers = prod.Reviews.Select(r => r.Owner).ToList(); // N+1
}
The above code is fictional, but most likely will produce N+1 issue. N+1 issue is when you don’t just issue a SELECT
query, but follow up every returned row by another SELECT
query to retrieve related entries. This is caused by EF feature called Lazy Loading. Read better explanation about Lazy Loading problem here. Basically think that your first query for Products returns 1000 records. Then in case of N+1, operation inside of foreach
loop will produce another thousand queries to your database.