Give every EF Core endpoint a query budget, then let the agent fix the N+1

Ask an agent for “a report of each customer’s name, order count and total spend” and it will write something that reads well, passes a correctness test and issues one database query per customer. With 20 rows in a test database nobody notices. With 20,000 customers in production, the endpoint takes seconds and the database gets hammered.

The usual fix is a code review comment, which depends on someone spotting the loop. A better fix is a test that counts the SQL statements an endpoint runs and fails when the number goes over a budget. The agent then gets a red test with the offending SQL in the message, which is exactly the feedback it needs to repair its own code. Nothing in this setup is specific to one agent: the technique, the prompt and the check all work with any coding agent that can read files and run dotnet test.

The failing case

Here’s a small model, a DbContext and the kind of method an agent writes first. It targets .NET 10 and EF Core 10 with SQLite.

public class Customer { public int Id { get; set; } public string Name { get; set; } = ""; public List<Order> Orders { get; set; } = []; }
public class Order { public int Id { get; set; } public int CustomerId { get; set; } public decimal Total { get; set; } }

public class ShopContext(DbContextOptions<ShopContext> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
}

public sealed record CustomerSummary(string Name, int OrderCount, decimal Spend);

public class CustomerReport(ShopContext db)
{
    public async Task<List<CustomerSummary>> SummariesAsync()
    {
        var result = new List<CustomerSummary>();
        foreach (var c in await db.Customers.OrderBy(c => c.Name).ToListAsync())
        {
            var orders = await db.Orders.Where(o => o.CustomerId == c.Id).ToListAsync();
            result.Add(new(c.Name, orders.Count, orders.Sum(o => o.Total)));
        }
        return result;
    }
}

Every row is correct. Any test that only checks the returned values passes. That’s the problem with N+1: the output is right and the cost is hidden.

Count the statements

EF Core’s interceptors let you observe every command before it goes to the database. According to the EF Core docs, DbCommandInterceptor.ReaderExecuting is called before a query is executed, and interceptors are registered with AddInterceptors. A counter is a few lines:

public sealed class QueryCounter : DbCommandInterceptor
{
    private readonly List<string> _sql = [];
    public IReadOnlyList<string> Statements => _sql;
    public int Count => _sql.Count;
    public void Reset() => _sql.Clear();

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    { _sql.Add(command.CommandText); return result; }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    { _sql.Add(command.CommandText); return ValueTask.FromResult(result); }
}

Override both the sync and async methods. Code under test that mixes ToList() and ToListAsync() would otherwise slip past a counter that only watches one of them. The usings are System.Data.Common, Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.Diagnostics.

The test project needs Microsoft.EntityFrameworkCore.Sqlite (10.0.14 resolved when this was written) and the xUnit template packages. Use a real SQLite connection held open for the test, not the in-memory EF provider. The in-memory provider doesn’t translate LINQ to SQL, so it hides exactly the queries you want to count.

public sealed class QueryCountTests : IAsyncLifetime
{
    private readonly SqliteConnection _conn = new("Data Source=:memory:");
    private readonly QueryCounter _counter = new();
    private ShopContext _db = null!;

    public async Task InitializeAsync()
    {
        await _conn.OpenAsync();
        var options = new DbContextOptionsBuilder<ShopContext>()
            .UseSqlite(_conn).AddInterceptors(_counter).Options;
        _db = new ShopContext(options);
        await _db.Database.EnsureCreatedAsync();
        for (var i = 1; i <= 20; i++)
            _db.Customers.Add(new Customer { Name = $"Customer {i:00}",
                Orders = [new Order { Total = 10 * i }, new Order { Total = 5 }] });
        await _db.SaveChangesAsync();
        _db.ChangeTracker.Clear();
        _counter.Reset();
    }

    public async Task DisposeAsync() { await _db.DisposeAsync(); await _conn.DisposeAsync(); }

    [Fact]
    public async Task Summaries_run_a_constant_number_of_queries()
    {
        var rows = await new CustomerReport(_db).SummariesAsync();

        Assert.Equal(20, rows.Count);
        Assert.True(_counter.Count <= 1,
            $"Expected at most 1 query, got {_counter.Count}:\n{string.Join("\n", _counter.Statements.Take(3))}");
    }
}

Two details matter. ChangeTracker.Clear() after seeding stops the context from answering out of its identity map, which would make a lazy N+1 look cheaper than it is. And _counter.Reset() drops the seeding statements, so the count covers only the code under test.

Run dotnet test and it prints:

Failed Shop.QueryCountTests.Summaries_run_a_constant_number_of_queries [362 ms]
  Error Message:
   Expected at most 1 query, got 21:
SELECT "c"."Id", "c"."Name"
FROM "Customers" AS "c"
ORDER BY "c"."Name"
SELECT "o"."Id", "o"."CustomerId", "o"."Total"
FROM "Orders" AS "o"
WHERE "o"."CustomerId" = @c_Id
SELECT "o"."Id", "o"."CustomerId", "o"."Total"
FROM "Orders" AS "o"
WHERE "o"."CustomerId" = @c_Id

Twenty customers, twenty-one queries. The message shows the first three statements, and the repeated parameterised WHERE "o"."CustomerId" = @c_Id is the signature of an N+1 pattern. An agent reading this doesn’t need to be told what’s wrong.

Why the budget is a number and not a rule

A test that says “no more than N queries” for a fixed N is a budget. It sounds crude, and it’s the point. A budget of 1 for this endpoint states what the author intends: one round trip, whatever the data size.

The seed data has 20 customers, but the real guarantee is that the count doesn’t grow with the row count. If you want that stated explicitly, seed two sizes in a [Theory] and assert the counts are equal. Keep the single-number version as the default, because it’s the one an agent can’t accidentally satisfy by changing the seed.

Hand it to the agent

The prompt is short, and the boundaries are the useful part:

QueryCountTests.Summaries_run_a_constant_number_of_queries fails: 21 queries for
20 customers. Fix CustomerReport.SummariesAsync so the test passes without
changing the test or its budget of 1. Keep the returned values identical.
Run `dotnet test` and show me the final output.

A likely first attempt is the obvious one: Include(c => c.Orders). That would pass the correctness test and, under the default single-query mode, becomes one JOIN. The EF Core docs warn about the cost of that route, though. Include loads related data through JOINs in one query, which duplicates the principal row’s columns for every child and, with two sibling collections, produces a cartesian product. For a report that only needs a count and a sum, loading every order entity is more work than needed.

The version that fits the budget and the intent is a projection, so the database does the aggregation:

public Task<List<CustomerSummary>> SummariesAsync() =>
    db.Customers
        .OrderBy(c => c.Name)
        .Select(c => new CustomerSummary(c.Name, c.Orders.Count, c.Orders.Sum(o => o.Total)))
        .ToListAsync();

With that in place the same dotnet test run prints Passed! - Failed: 0, Passed: 1. One statement, no order entities materialised, nothing tracked.

The budget catches the half-fix

Another common move is AsSplitQuery, which the EF Core docs present as the way around JOIN-heavy single queries. Swap the method body for Include(c => c.Orders).AsSplitQuery() and the test fails differently:

Expected at most 1 query, got 2:
Failed!  - Failed: 1, Passed: 0, Skipped: 0, Total: 1

That’s correct behaviour for the test and for EF. The docs say split queries issue an additional SQL query for each included collection navigation, with an extra round trip each. Two queries for this endpoint is a fine result, and nothing like 21, so it may be a budget you’d happily raise to 2. The test makes you decide that on purpose, in a diff a reviewer can see, rather than having it happen by accident.

That’s the part worth keeping in the review habit: when a budget number changes in a pull request, treat it like a changed assertion. Raising it from 1 to 2 needs a sentence of justification. Raising it to 50 needs an argument.

Make it yours

Put the counter in a shared test-helpers project and give it a Count plus an AssertAtMost(int budget) method, so each endpoint test is three lines.

For ASP.NET Core endpoints, register the interceptor in a WebApplicationFactory by replacing the DbContextOptions registration, then call the endpoint with HttpClient and assert on the counter. That covers the whole request, including query filters and any services that touch the context, so it catches N+1 in code the controller calls indirectly.

Not everything is a count of one. Paged lists, authorisation checks and multi-step workflows have budgets of 2, 3 or 5. Set the number to what the code does when it’s right, then let the test hold it there.

Where it goes wrong

Counting statements measures round trips, not cost. A single query with a bad plan, a missing index or a huge JOIN passes a budget of 1 and is still slow. The budget is a cheap tripwire for one class of defect. A profiler or EXPLAIN on realistic data is still how you find the others.

The test only covers paths you exercise. An endpoint with a conditional branch that loops over related data needs a test that takes that branch.

SQLite makes round trips nearly free, which is why the unfixed endpoint “feels” fine in tests. The count is what matters here, not the duration, so don’t add a timing assertion.

Lazy-loading proxies, if you’ve enabled them, produce N+1 from property access rather than from visible queries. The counter catches those too, since they go through the same command pipeline, but the stack trace won’t point at a loop. Expect to hunt for the property access.

Skip this on endpoints that are called once a day against a few hundred rows. The budget is for hot paths and for anything the agent touches that returns lists.

First 15 minutes

Pick the busiest list endpoint in your API. Add the QueryCounter and a test with a deliberately generous budget, run it, and read the number it reports. If it’s higher than you expected, you have your first finding. Tighten the budget to what the code should do, then hand the failing test to your agent with the prompt above.