Find N+1 queries in EF Core without changing a line of code

Your endpoint works. Tests are green. In development it answers in 80 ms. In production, for a customer with a long history, it takes two seconds and the database CPU climbs.

Nine times out of ten with EF Core, the culprit is an N+1 query: one query to load a list, then one more query for each item of that list.

What it looks like

public async Task<List<OrderSummary>> GetOrdersAsync(int customerId)
{
    var orders = await db.Orders
        .Where(o => o.CustomerId == customerId)
        .OrderByDescending(o => o.PlacedAt)
        .ToListAsync();                                   // 1 query

    var result = new List<OrderSummary>();
    foreach (var order in orders)
    {
        var lines = await db.OrderLines
            .Where(l => l.OrderId == order.Id)
            .ToListAsync();                               // + 1 query per order
        result.Add(new OrderSummary(order.Id, order.PlacedAt,
            lines.Sum(l => l.Quantity * l.UnitPrice)));
    }
    return result;
}

Nothing looks wrong. With 3 orders in your dev database, it runs 4 fast queries. With a customer who has 48 orders, it runs 49 queries, each with a network round trip. Multiply by concurrent requests and you have a performance problem that no unit test will ever show.

It also hides in places that do not look like queries at all:

• Lazy loading (UseLazyLoadingProxies()): order.Lines in a loop silently runs a query per order.
• Mapping code: a Select in memory, a mapper or a foreach in a view that touches a navigation.
• Helper methods called in a loop that each run "just one small query".

How to spot it

1. Read the SQL log. EF Core logs every command at the Information level:

options.UseSqlServer(connectionString)
       .LogTo(Console.WriteLine, LogLevel.Information);

Then look for the same SELECT repeated with a different parameter. It works, but in a real app the log is a wall of text, and it tells you what ran, not which line of your code ran it.

2. Count queries per request. An interceptor (DbCommandInterceptor) or a profiler such as MiniProfiler can count commands per HTTP request. More reliable, but it means adding a package and code to your app, and you still have to trace each query back to its origin.

3. Watch the database. Query Store, pg_stat_statements or Application Insights will show a query executed thousands of times. In production. After your users noticed.

Three ways to fix it

Project what you need, in one query. Usually the best fix: EF Core turns the whole thing into a single SQL statement with a subquery, and loads only the columns you use.

return await db.Orders
    .Where(o => o.CustomerId == customerId)
    .OrderByDescending(o => o.PlacedAt)
    .Select(o => new OrderSummary(o.Id, o.PlacedAt,
        o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
    .ToListAsync();

Load the related data with the parent. When you need the entities themselves:

var orders = await db.Orders
    .Where(o => o.CustomerId == customerId)
    .Include(o => o.Lines)
    .ToListAsync();

With several collection Includes, the single query can return a huge cartesian product: add .AsSplitQuery() to load each collection with its own query (a fixed number of queries, not N).

Batch the second query. When the loop must stay (different contexts, complex logic), load everything the loop needs in one query, then look it up in memory:

var ids = orders.Select(o => o.Id).ToList();
var linesByOrder = (await db.OrderLines
        .Where(l => ids.Contains(l.OrderId))
        .ToListAsync())
    .ToLookup(l => l.OrderId);

foreach (var order in orders)
{
    var lines = linesByOrder[order.Id];
    // ...
}

Two queries, whatever the number of orders.

Catching it at the line that causes it

The hard part is not the fix, it is noticing. I wanted something that tells me, while I code, "this line ran the same query 48 times in one request".

So I built it into Schemawise, a VS Code extension for EF Core. Its Query Profiler shows every SQL query of your app above the line of code that runs it, flags N+1 queries, slow queries and large results, and fixes the N+1 in one click (here, 144 queries become 3):

How it works, since "no code change" deserves an explanation:

• EF Core publishes every command it runs through DiagnosticListener (the same events its logging uses). A small .NET startup hook (DOTNET_STARTUP_HOOKS, a runtime feature since .NET Core 3.0) subscribes to them when your app starts. No NuGet package, no line of code in your app.
• For each command, it records the SQL text, the duration, the number of rows read, the parameter names and types (never their values), and the first frames of your own code in the call stack. That is what puts the result on the right line.
• Queries are grouped by request (the root Activity of ASP.NET Core). The same SELECT from the same line five times or more in one request is an N+1.
• Everything stays on your machine: the hook writes to a local file that the extension reads. It refuses to run in Production and Staging.

The one-click fix is the "batch the second query" rewrite above: the lightbulb moves the query out of the loop, loads everything with Contains, groups it with ToLookup, and shows the change in VS Code's refactor preview before applying it. When the code has another shape, it explains the three fixes instead of guessing.

The same data catches the other classic EF Core mistakes, each with its fix:

• Cartesian explosion: several collection Includes in one statement (EF Core's own warning, now at the right line): AsSplitQuery().
• Synchronous queries in async methods: ToList() or SaveChanges() blocking a thread: await ToListAsync().
• Read-only queries that track entities in a DbContext that never saves: AsNoTracking().

It can also group everything by HTTP request (GET /api/orders/{id}: 49 queries per request), and read the estimated execution plans of those queries (never running them) to suggest the missing indexes, with the HasIndex(...) line to add to your model.

Full disclosure: the profiler is part of Schemawise Pro, free to try for 14 days with no account and no card.

The extension also has free features for the rest of the EF Core workflow: the effective model of each DbContext, schema drift against a real SQL Server or PostgreSQL database, and the risks of a migration before you apply it.

The takeaway

• N+1 queries are invisible with small dev data: look at queries per request, not at durations.
• Prefer a projection (Select) to loading entities and walking their navigations.
• Include for entities, AsSplitQuery when several collections multiply rows, and batching with Contains when the loop must stay.
• Make the problem visible where you write the code, not in a production dashboard.

If you try Schemawise, I would love to hear what it finds in your project, and what it misses: issues and ideas are welcome.

Comments

[Mehdi Mohseni]

The batch fix is the one that changes most between versions, because ids.Contains doesn't produce the same SQL everywhere. On EF 8 and 9 it's a single OPENJSON parameter, so the plan is shared but the planner has no idea if the list holds 3 ids or 3,000, and on EF 10 every id becomes its own parameter with padding (8 ids go out as 10 parameters), which gives the cardinality back to the planner. Does the lightbulb look at the EF version or the UseParameterizedCollectionMode setting before it suggests Contains for a customer with a very long history?