Find the expensive part of an EF Core query

Capture one request, not just one statement

Record the EF Core/provider version, database version, operation, tenant/data size, and parameter values approved for diagnosis. Capture an ordinary request and a slow one. Count every database command in the request; one fast statement repeated for each row can dominate the total.

Use a fixed query tag to connect LINQ with command logs. Capture logs briefly in an approved environment, then disable extra logging. Keep secrets and customer values out of logs and AI inputs. Record end-to-end latency separately from command duration.

Let the observation choose the next check

• Command count grows with result count: inspect lazy loading and queries inside loops. Compare an explicit projection or deliberate related-data load.
• A single statement does most of the work: inspect its plan, rows read versus returned, estimates versus actual rows, and sort or spill warnings. A scan alone does not prove an index is missing.
• The database returns quickly but the request stays slow: measure result transfer, materialization, serialization, and other dependencies before changing SQL.
• Multiple collection joins multiply result rows: compare a projection with split queries. Splitting adds round trips and can return inconsistent results during concurrent changes; choose the required consistency explicitly.

Example: return an order-summary page

Suppose the screen needs only an order ID, creation time, status, and total. Loading complete orders and line collections adds data the screen does not use. This illustrative EF Core fragment has not been compiled or executed here; adapt it to your model and verify the generated SQL.

Assume Id is a unique numeric key, CreatedUtc is non-null and stable, tenantId comes from the authorized tenant context, and both cursor values come from the last displayed row. The first page omits the cursor filter. Existing authorization and global query filters must remain in force.

var orders = db.Orders
    .TagWith("OrderHistory.Page")
    .Where(o => o.TenantId == tenantId);

if (hasCursor)
{
    orders = orders.Where(o =>
        o.CreatedUtc < lastCreatedUtc ||
        (o.CreatedUtc == lastCreatedUtc && o.Id < lastId));
}

var page = await orders
    .OrderByDescending(o => o.CreatedUtc)
    .ThenByDescending(o => o.Id)
    .Select(o => new { o.Id, o.CreatedUtc, o.Status, o.Total })
    .Take(50)
    .ToListAsync(cancellationToken);

Check the tradeoff before adopting the example

The unique ordering prevents ties from making page boundaries ambiguous. Keyset pagination fits next/previous navigation; it does not preserve an arbitrary page-number API. Define how concurrent inserts or changes should appear. An index beginning with TenantId, CreatedUtc, and Id is a candidate to evaluate against existing indexes, data distribution, and write cost.

This scalar projection contains no entity instances to track. AsNoTracking would not remove additional entity-tracking work here; projections that contain entities have different behavior.

• Inspect the executed SQL: only the required columns, tenant predicate, ordering, and row limit should remain. Count commands for the whole request.
• Test an empty tenant, a large tenant, duplicate creation times, the first page, and later pages. Confirm another tenant’s rows are never returned.
• Compare request latency, command duration, logical reads, result bytes, and allocations under the same workload. Keep the change only when the measured benefit justifies its tradeoffs.