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 eight milliseconds against ten seed rows takes nine seconds against a production table. Nothing threw. Nothing failed. The page just became slow.

The reason it survives is structural. Unit tests run against an in-memory provider or a handful of fixtures, so the cost never adds up to anything you would notice. Code review reads the C#, not the SQL it generates. The first honest signal arrives later as a latency graph with a step change in it.

The two shapes it takes

The first is the classic N+1: a lazy navigation accessed inside a loop.

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

The second is quieter because it looks like ordinary, declarative LINQ:

var recent = db.Orders
    .ToList()                                  // materialises the whole table
    .Where(o => o.Total > 1_000)
    .OrderByDescending(o => o.Date);

Move ToList() after Where and OrderBy and the predicate remains an expression tree. EF Core translates it to SQL, the database does the work it is built for, and the application receives only the rows it asked for.

Why “just be careful” does not scale

The usual advice is correct: use an explicit projection, keep filters on the IQueryable, and inspect the generated SQL. It is also fragile. Discipline depends on every developer recognising every expensive query shape during every refactor. A repeatable engineering control should not depend on permanent vigilance.

If a rule can be checked at compile time, do not leave it to memory in code review.

Checking the query at compile time

This is what a Roslyn analyser is good at. It reads the syntax tree while you type, has semantic knowledge of the types involved, and can recognise a materialising call sitting upstream of a database predicate. The same rule runs in the editor and in CI, without a runtime dependency or a representative production dataset.

LinqContraband encodes those query-shape rules. When a rewrite is provably safe it can offer the fix; when it is not, the diagnostic explains the boundary instead of guessing. That distinction matters. A noisy analyser is simply another warning developers learn to ignore.

The general principle

N+1 is one example of a larger family: bugs that are invisible in source, cheap under test, and obvious only under production load. Captive dependencies, dropped cancellation tokens and unsafe retry policies share the same profile. Encode the rule once, make its limits explicit, and let the compiler apply it consistently. The expensive surprise then becomes an ordinary edit.

← Back to writing