dotnet-n-plus-one-check

name	dotnet:n-plus-one-check
description	Scan for EF Core N+1 query patterns — loops over materialized lists that access navigations, missing Include, lazy loading in hot paths. Reports file:line findings.
argument-hint	<optional: file or directory>
effort	medium

n-plus-one-check

Targeted scan for the most common EF Core performance bug.

What N+1 Looks Like

// ❌ N+1 — accesses .Customer per iteration, one SQL each
var orders = await _ctx.Orders.Where(o => o.Status == "Open").ToListAsync();
foreach (var o in orders)
    Console.WriteLine(o.Customer.Name);       // lazy-load OR null

// ❌ N+1 via explicit query per item
var orders = await _ctx.Orders.ToListAsync();
foreach (var o in orders)
    o.Total = await _ctx.Items.Where(i => i.OrderId == o.Id).SumAsync(i => i.Price);

// ❌ N+1 inside a projection that escapes
var vm = orders.Select(o => new { o.Id, Name = o.Customer.Name }).ToList();

Detection Patterns

Grep for these shapes:

Pattern	Regex
foreach over query result + navigation	foreach\s*\([^)]+\).*\n.*\.(Customer|Order|Items|Tag)\b
Query inside loop	for(each)?\s.*\{[\s\S]*?_(db|ctx)\.\w+\.(Where|First|Single|Find)
Lazy loading on	UseLazyLoadingProxies\(\)
Missing Include before projection	Queries that return entity lists without .Include

Flow

1. Locate all EF queries (DbSet<T>.Where/Select/ToList/First)
2. Look for sibling code — is the result iterated?
3. Check if any navigation is accessed in the iteration
4. Check if that navigation is eager-loaded (.Include(x => x.Nav))
5. Report findings with file:line and suggested fix

Fixes (ranked by preference)

// ✅ 1. Project to DTO — best for read paths
await _ctx.Orders
    .Where(o => o.Status == "Open")
    .Select(o => new OrderDto(o.Id, o.Customer.Name))   // translates to SQL JOIN
    .ToListAsync(ct);

// ✅ 2. Include — for entity use (writes, deep operations)
await _ctx.Orders
    .Where(o => o.Status == "Open")
    .Include(o => o.Customer)
    .AsNoTracking()
    .ToListAsync(ct);

// ✅ 3. Batch load via .AsSplitQuery when .Include count ≥ 3
await _ctx.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items).ThenInclude(i => i.Product)
    .AsSplitQuery()
    .ToListAsync(ct);

Iron Laws

• #11: No N+1 — this skill is the enforcement tool
• #6: AsNoTracking() on read paths after fixing N+1
• Don't "fix" N+1 by disabling lazy loading silently — that just moves the bug to null-navigation exceptions

Output

.claude/audit/n-plus-one.md:

# N+1 Scan — 2026-04-18

## Findings (3)

### 🔴 src/Api/Orders/OrdersController.cs:47
Iterates `orders` list and accesses `.Customer.Name` without Include.
Fix: project to `OrderDto` in the query.

### 🟠 src/Services/InvoiceReport.cs:103
`.ForEach(o => LoadItems(o))` calls DbSet inside loop.
Fix: `.Include(o => o.Items)` or projection.

### 🟡 src/Admin/DashboardViewModel.cs:55
Projects via `.Select(o => o.Customer.Name)` — translated, not N+1,
but flagged for review because lazy loading is enabled project-wide
(see Program.cs:34).

Integration

/dotnet:n-plus-one-check → .claude/audit/n-plus-one.md
        ↓
/dotnet:plan "Fix N+1 queries"
        ↓
/dotnet:work

References

• ${CLAUDE_SKILL_DIR}/references/detection-rules.md — full grep pattern library
• ${CLAUDE_SKILL_DIR}/references/split-query.md — when .AsSplitQuery beats single query
• ${CLAUDE_SKILL_DIR}/references/logging-interceptor.md — verify fix by counting queries in tests

Anti-patterns

• Reporting a projected query as N+1 (it's not — EF translates)
• Suggesting .ToList() + loop as a "fix"
• Ignoring deeply nested foreach (still N+1 even if pretty)
• Not adding an integration test to lock in the fix