An endpoint built on a LINQ query is far slower than its SQL — find the cause
An endpoint returns 50 orders with their lines and takes about 900 ms, while running the equivalent SQL by hand against the same database takes 6 ms. The data volume has not grown and nothing else changed. Below is the EF Core command log captured for a single call to that endpoint.
Executed DbCommand (4ms) [Parameters=[], CommandType='Text']
SELECT o."Id", o."CustomerId", o."CreatedAt" FROM "Orders" AS o LIMIT 50
Executed DbCommand (3ms) [Parameters=[@__p_0='1'], CommandType='Text']
SELECT l."Id", l."OrderId", l."Sku", l."Qty" FROM "Lines" AS l WHERE l."OrderId" = @__p_0
Executed DbCommand (3ms) [Parameters=[@__p_0='2'], CommandType='Text']
SELECT l."Id", l."OrderId", l."Sku", l."Qty" FROM "Lines" AS l WHERE l."OrderId" = @__p_0
... 48 further commands, identical apart from @__p_0 = 3 .. 50 ...Diagnose the cause and say how you would fix it.
The log shows one query for the 50 orders and then one query per order for its lines — 51 round trips. That is N+1, caused by a navigation resolved lazily inside the loop; each command is fast, so the SQL is not the problem, the count of them is. Load the lines in the same query with Include or a Select projection, turn lazy loading off, and add AsNoTracking to drop the tracking cost on a read-only endpoint.
- ✗Reading fast individual commands as proof the query is healthy, ignoring how many of them there are
- ✗Blaming a missing index when the round-trip count is what costs the time
- ✗Assuming an untranslatable
Whereis silently evaluated on the client — since 3.0 it throws
- →Which log category or diagnostic would you switch on first to see this?
- →How much does tracking still cost once the N+1 is gone, and how would you measure that?
What the log shows
Executed DbCommand (4ms) SELECT ... FROM "Orders" AS o LIMIT 50
Executed DbCommand (3ms) SELECT ... FROM "Lines" AS l WHERE l."OrderId" = @__p_0 -- @__p_0 = 1
Executed DbCommand (3ms) SELECT ... FROM "Lines" AS l WHERE l."OrderId" = @__p_0 -- @__p_0 = 2
... 48 further commands just like these ...Diagnosis
Every single command is fast — 3-4 ms. The shape of the SQL is not the problem; the number of commands is: one query for the list of orders plus one query for the lines of each of the 50 orders — 51 round trips instead of one. This is textbook N+1.
The signature in the log is unambiguous: the commands are identical in shape and differ only in the parameter @__p_0, which runs through the parent identifiers. That is what a lazily resolved navigation inside a loop looks like: the code walks the orders and touches order.Lines on each one, and the proxy goes to the database right there.
What it is not:
- ⚠️ Not a missing index — a table scan would not finish in 3 ms per command, and the total time is dominated by the 51 round trips anyway.
- ⚠️ Not client-side evaluation — since 3.0
EF Corethrows on an untranslatableWhererather than quietly pulling the table into memory. - ⚠️ Not split query — that produces a fixed number of queries (one per collection), not one query per parent row.
The fix
// ❌ before: the navigation resolves lazily inside the loop → 1 + 50 queries
var orders = await db.Orders.Take(50).ToListAsync();
foreach (var o in orders)
total += o.Lines.Sum(l => l.Qty); // every access is a trip to the database
// ✅ after: the related data comes back in the same query, untracked
var orders = await db.Orders
.AsNoTracking() // no snapshot, no identity resolution
.Include(o => o.Lines) // the lines arrive in one round trip
.Take(50)
.ToListAsync();Cheaper still — project only the columns you need, materializing no entities at all:
var rows = await db.Orders
.AsNoTracking()
.Take(50)
.Select(o => new OrderView(o.Id, o.Lines.Sum(l => l.Qty)))
.ToListAsync();And switch the lazy-loading proxies off in an API — otherwise the N+1 comes back with the next loop somebody writes.
