What is the N+1 query problem in EF Core, and how do you fix it?
Quick Answer
N+1 is when you run 1 query to load a list of parents, then 1 more query per parent to load its children — 1 + N round trips. It usually comes from lazy loading inside a loop. Fix it with eager loading (Include), a projection that pulls exactly what you need, or a single join query.
N+1 is when I load a list — say 100 orders in one query — and then, for each one, EF fires another query to load that order's lines. That's 101 round trips for something that should be one or two. It almost always comes from lazy loading inside a loop. The fixes are: use Include to eager-load in one joined query, or better, project straight into a DTO with Select so EF pulls only the columns I actually use and tracks nothing. If I Include several collections I switch on AsSplitQuery to avoid a cartesian blow-up. I catch it by watching the SQL EF logs in development.
- Symptom: a burst of near-identical
SELECT ... WHERE ParentId = @pqueries in the EF logs. - Cause:
foreach (var order in db.Orders) total += order.Lines.Sum(...)with lazy-loading proxies enabled. - Fix 1 — eager:
db.Orders.Include(o => o.Lines). One query with a join. Watch for cartesian explosion when including multiple collections — useAsSplitQuery(). - Fix 2 — projection (best):
db.Orders.Select(o => new OrderDto { Id = o.Id, Total = o.Lines.Sum(l => l.Amount) }). One query, only the needed columns, no tracking. - Detect it: log SQL in development, or use a profiler / MiniProfiler; EF Core can also warn on certain patterns.
- Enabling lazy-loading proxies globally and then iterating navigation properties in a loop.
Include-ing multiple sibling collections in one query, causing a cartesian product (rows = parents × collection1 × collection2).- Calling
.ToList()before theInclude, so the include runs client-side or not at all. - Loading full entities when a small projection would do — over-fetching wide rows.