klod68/debug-ef-query

---

name: debug-ef-query description: "Use when diagnosing slow EF Core queries, N+1 problems, or unexpected SQL generation. Covers query logging, AsNoTracking, Include chains, projection fixes, and compiled queries. Invoke when a LINQ query generates more SQL than expected, a page loads slowly due to database calls, Include chains produce Cartesian explosion, or tracking is unnecessary on read-only queries. Also trigger when the user asks: why is my EF query slow, N+1 problem, fix EF query, EF SQL logging, AsNoTracking, Include performance, compiled query, EF Core optimization. Domain: EF Core / Data Access. Level: Intermediate."

Diagnose & Fix EF Core Query Problems

---

Step 1 — Enable query logging (if not already on)

// In DbContext options (development only)
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information)
              .EnableSensitiveDataLogging()
              .EnableDetailedErrors();

Capture the generated SQL for the problem query before doing anything else.

---

Step 2 — Identify the problem class

| Symptom | Likely cause | |---|---| | Many identical queries in a loop | N+1 — missing Include() or batching | | Query returns more columns than needed | Missing Select() projection | | Full table scan in logs | Missing index or EF can't translate filter | | Tracking overhead on reads | Missing AsNoTracking() | | Cartesian explosion | Multiple collection Include() on same query | | InvalidOperationException on async | .Result / .Wait() deadlock |

---

Step 3 — Fix patterns

N+1

// Before (N+1)
var orders = await context.Orders.ToListAsync();
foreach (var o in orders) { _ = o.Customer.Name; } // lazy load per row

// After
var orders = await context.Orders
    .Include(o => o.Customer)
    .AsNoTracking()
    .ToListAsync(ct);

Projection instead of full entity

// Before
var entities = await context.Products.ToListAsync(ct);
var dtos = entities.Select(e => new ProductDto(e.Id, e.Name)).ToList();

// After
var dtos = await context.Products
    .AsNoTracking()
    .Select(e => new ProductDto(e.Id, e.Name))
    .ToListAsync(ct);

Cartesian explosion (split queries)

context.Orders
    .Include(o => o.Lines)
    .Include(o => o.Shipments)
    .AsSplitQuery()  // generates separate SQL per collection
    .AsNoTracking()

Untranslatable filter → raw SQL (last resort)

// Document WHY LINQ was insufficient before using raw SQL
var results = await context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE FullTextMatch(@term)", term)
    .AsNoTracking()
    .ToListAsync(ct);

---

Step 4 — Validate the fix

1. Re-run with logging enabled — confirm the SQL is what you expect.
2. Check query count: one query (or split queries) per logical operation.
3. Confirm AsNoTracking() is present on all read paths.
4. Run affected unit/integration tests.

---

Step 5 — Checklist before closing

• [ ] No N+1 in the affected query path
• [ ] AsNoTracking() on all read-only queries
• [ ] Projection (Select) used instead of full entity load where applicable
• [ ] No Include() chains deeper than 2 levels
• [ ] Raw SQL documented with inline comment if used
• [ ] Logging disabled or scoped to development environment