NPlusOne.EntityFrameworkCore 1.1.0

N+1

Finds N+1 query problems in .NET and EF Core code: at build time with a Roslyn analyzer, and in tests and requests with a runtime guard. Free and open source (MIT).

OrderService.cs(42,31): warning NP0001: 'IOrderRepository.GetByCustomerAsync' runs a query ('ToListAsync' in
  OrderRepository.GetByCustomerAsync) once per element of the foreach loop over 'customers'. Load the rows for all
  elements up front with one query, Where(o => keys.Contains(o.CustomerId)), and group them with ToLookup(o => o.CustomerId).

Documentation · Which package where · All packages on NuGet · Changelog

Packages

Three packages, one for each place an N+1 can be caught, released together under one version. Use the analyzer, the guard, or both.

Quick start

1. Add the analyzer to every project with EF Core code (EF Core 8, 9 or 10; any SDK since .NET 8):

dotnet add package NPlusOne.Analyzers

2. Build. N+1 queries show up as warnings, in the build and in the IDE, each with the rewrite that removes it:

dotnet build

3. Keep them out. Raise the rules to errors in .editorconfig:

[*.cs]
dotnet_diagnostic.NP0001.severity = error
dotnet_diagnostic.NP0002.severity = error
dotnet_diagnostic.NP0003.severity = error
dotnet_diagnostic.NP0005.severity = error

4. Catch the rest in tests. The runtime guard fails a test when the same query or lazy load repeats:

dotnet add package NPlusOne.EntityFrameworkCore

var options = new DbContextOptionsBuilder<ShopContext>().UseSqlite(connection).UseNPlusOneGuard().Options;

using var guard = NPlusOneGuard.Begin();
await new OrderService(new ShopContext(options)).ListAsync();
guard.AssertNoNPlusOne();

5. Watch requests in development with NPlusOne.AspNetCore: builder.Services.AddNPlusOneGuard() and app.UseNPlusOneGuard().

The getting started guide walks through each step.

What it finds

N+1 hides in more places than a foreach with a query in it. The analyzer follows round trips through:

and it stays quiet where a loop is meant to make one round trip per iteration: batches, paging, polling, search loops, cached lookups, and anything EF Core translates into the same SQL statement.

Every one of the 143 shapes in the catalogue is a test that runs on SQLite and counts the SQL: a reported case must send more commands for more rows, a silent one the same number, on EF Core 8, 9 and 10.

Rules

Options (lazy loading, methods the analyzer cannot see into, call depth) are listed in configuration.

How it compares

Measured on the same proven cases, the analyzer finds 260 of 260 N+1 methods with no false warning; LinqContraband, the static analyzer with the most N+1 rules we found, finds 55. At run time the guard here, EFCore.QueryGuard and DbEye all see most cases; the guard alone also sees SQL that differs per element, transactions per element, and counts one batched SaveChanges once. The corpus was written for this tool, so read the comparison for what each number means.

Contributing

dotnet build and dotnet test (SDK pinned in global.json; the .NET 8, 9 and 10 runtimes run the tests). A missed N+1 or a false warning starts as a corpus case; see CONTRIBUTING.md and how it works.

License

MIT