Catching N+1 queries at compile time.

An N+1 query is the bug that hides in plain sight. You write what looks like one query, the ORM quietly issues another for every row it returns, and a method that ran in 8 milliseconds against ten seed rows takes nine seconds against a production table. Nothing threw. Nothing failed. The page just got slow, and the reason is three call-stacks away from where it hurts.

The reason it survives is structural. Unit tests run against an in-memory provider or a handful of fixtures, so the cost — one extra round-trip per row — never adds up to anything you’d notice. Code review reads the C#, not the SQL the C# will generate. And the people best placed to catch it are reading a pull request, not a query plan. So it ships, and the first honest signal arrives weeks later as a latency graph with a step change in it.

The two shapes it takes

In Entity Framework Core, the family has two members. The first is the classic N+1: a lazy navigation accessed inside a loop.

foreach (var order in db.Orders.ToList())      // 1 query
{
    // one more query, every single iteration
    Console.WriteLine(order.Customer.Name);   // N queries
}

The second is sneakier, because it looks like good, declarative LINQ. The trap is where the query stops being a query:

var recent = db.Orders
    .ToList()                                  // ← materialises the WHOLE table
    .Where(o => o.Total > 1_000)               // now filtering in memory
    .OrderByDescending(o => o.Date);

That single misplaced .ToList() turns a WHERE the database could answer from an index into a full table read plus an in-process filter. Move it after the Where and OrderBy and the predicate is translated to SQL — the database does the work it’s built for, and returns the handful of rows you actually wanted.

Why “just be careful” doesn’t scale

The standard advice — use Include for the navigation, push the predicate before materialisation, watch your ToList calls — is all correct and all useless at 2pm on a Friday when someone is three layers deep in a refactor. Discipline is not a control. It depends on the most fallible part of the system being uniformly attentive, forever. The whole history of software quality is the slow realisation that the cheapest place to catch a class of bug is the earliest, and the earliest place is the compiler.

Good software fails loudly, early, and somewhere cheap to fix. The goal is to move the failure as close to the keyboard as it’ll go.

Moving the catch to the red squiggle

This is exactly what a Roslyn analyzer is for. It reads your code as a syntax tree while you type, with full semantic knowledge of the types involved, and it can recognise the shapes above — a navigation property dereferenced inside a loop, a .ToList() sitting upstream of a .Where() on an IQueryable — and surface them as a warning under the offending line. No runtime cost, no extra test run, no waiting for production to tell you. The bug becomes a wavy underline before the code even compiles.

That’s the premise behind LinqContraband, an analyzer I built to read LINQ the way the database will. It refuses to let client-side evaluation and N+1 patterns reach production by flagging them at compile time, with code fixes that rewrite the query into its translatable form. The expensive mistake gets caught in the incident channel that matters least — your editor — instead of the one that matters most.

The general principle

N+1 is just one instance of a broader pattern: bugs that are invisible in the source, invisible under test, and obvious only under production load. Captive dependencies in your DI container, an un-propagated CancellationToken, a forgotten AutoMapper configuration — they all share that profile, and they all yield to the same move. Encode the rule once, in an analyzer, and let the compiler enforce it on every keystroke for everyone, forever. That’s the unglamorous infrastructure of software quality, and it’s most of what I build.

← Back to selected work