How to catch N+1 queries in EF Core before they hit production

#database #dotnet #performance #testing

Your API can stay functionally correct while quietly getting slower.

No tests fail.
No alerts fire.
Everything “works”.

And then one day you realize an endpoint that used to execute 2 queries is now doing 15.

In EF Core, it’s surprisingly easy to introduce query regressions:

a small refactor changes how a projection is built
a navigation property is accessed differently
part of the query gets materialized too early
includes / relationships evolve over time

None of this breaks correctness. Your integration tests still pass because:

the response is correct
the database state is correct

But the query shape has changed. And that’s the part we usually don’t test.

Why this matters ? This isn’t about premature optimization. It’s about catching issues like:

N+1 queries
unnecessary roundtrips
query explosions after refactors

These problems don’t show up as failing tests — they show up as slow endpoints in production. If you want to detect this in EF Core tests, you typically end up doing something like:

writing a custom DbCommandInterceptor
wiring it into your test host
collecting executed SQL
asserting on the count It works, but it’s repetitive and low-level. Most teams end up copy-pasting some version of this.

A simpler approach

I wanted something closer to what Django has with assertNumQueries. So I wrapped the interceptor pattern into a small helper.

Example:

await using var guard = factory.TrackQueries<Program, AppDbContext>();
var client = guard.CreateClient();

await client.GetAsync("/api/orders");

guard.AssertCount(atMost: 3);

That’s it.

no manual interceptor wiring
no log parsing
no test boilerplate It just tracks the number of SQL queries executed during a request.

When to use this

I don’t think this belongs in every test. For most endpoints, correctness is enough. But it’s useful for:

list endpoints
dashboards / aggregates
endpoints with multiple relationships
any “hot path” where query shape matters Basically, places where going from 2 queries → 10 queries is a real problem.