NPlusOne.EntityFrameworkCore 1.0.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: selcukgural.github.io/NPlusOne

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 132 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.

Packages

How it compares

Measured on the same proven cases, the analyzer finds 231 of 231 N+1 methods with no false warning; LinqContraband, the static analyzer with the most N+1 rules we found, finds 53. 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