All 18 LINQ questions
More in LINQ
How does LINQ to Objects differ fundamentally from LINQ to Entities (Entity Framework), particularly regarding how the query is actually executed? How would you write a custom LINQ extension method that integrates seamlessly with the standard method-chaining style, like a 'DistinctBy' before it existed natively? How does LINQ's Aggregate() method work, and how would you use it to implement a custom reduction not covered by Sum()/Average()/Count()? How would you write an efficient LINQ query combining Join() with GroupBy() to replicate a SQL-style LEFT OUTER JOIN with aggregation?
Advertisement
Back to results for “LINQ”
C# › LINQ

How would you optimize a LINQ-to-Entities query to avoid the classic 'N+1 query' performance problem when accessing related navigation properties?

Advanced

Answer

Use Include() (and ThenInclude() for nested relationships) to EAGERLY LOAD related entities in the SAME database query, instead of letting Entity Framework lazily issue a SEPARATE query for each related entity accessed individually inside a loop — the N+1 problem occurs when a loop over N parent entities triggers N additional queries for their related child entities.
// N+1 problem: triggers a SEPARATE query for EACH order's Customer, inside the loop
var orders = dbContext.Orders.ToList();
foreach (var order in orders) { Console.WriteLine(order.Customer.Name); } // 1 + N queries!

// Fixed with eager loading: ONE query total
var ordersFixed = dbContext.Orders.Include(o => o.Customer).ToList();
foreach (var order in ordersFixed) { Console.WriteLine(order.Customer.Name); } // just 1 query
Real-world example Fixing a slow API endpoint whose database query count scales linearly (and badly) with the number of returned records.

Common follow-ups: How would you detect an N+1 query problem occurring in a real application, using EF Core's logging or a profiling tool?

Design Patterns in C#
All 18 LINQ questions