EF Core N+1 query problem: causes, detection and fixes (projection, Include, AsSplitQuery)

// ======================================================================
// Q: What is the N+1 problem in EF Core? How do you detect and fix it?
//
// 1 query for the list plus N queries, one per item, for the related data. With
// 500 orders: 501 round trips. It is invisible locally; in production with
// database latency it becomes seconds.
// - Projection is the best fix for reads: one query, only the columns you need.
// - Eager loading with Include when you need the entities (e.g. to modify them).
// - Batch loading with Contains when the data comes from elsewhere (e.g. ids
//   from the ERP).
// - AsSplitQuery with multiple collection Includes. A JOIN across two
//   collections returns lines × payments rows (cartesian explosion).
// - In production: Application Insights / OpenTelemetry. The telltale sign: the
//   same query repeated dozens of times in the same request.
// - Lazy loading is off by default in EF Core. Leave it off in web APIs. Also
//   watch serialization: entities with navigation properties in JSON can trigger
//   lazy loading. Return DTOs.
// ======================================================================

// ✗ Common causes of N+1
// ✗ Cause 1: lazy loading (UseLazyLoadingProxies). Every navigation = a new query
var orders = await db.Orders.Where(o => o.Status == OrderStatus.Placed).ToListAsync(ct);
foreach (var o in orders)
    Console.WriteLine($"{o.Customer.Name}: {o.Lines.Count} lines");   // 1 + 2N queries

// ✗ Cause 2: query inside a loop (even without lazy loading)
foreach (var o in orders)
{
    var customer = await db.Customers.FindAsync([o.CustomerId], ct); // N queries
}

// ✗ Cause 3: calling a service / repository per item
var dtos = new List<OrderDto>();
foreach (var o in orders)
    dtos.Add(await orderService.GetDetailsAsync(o.Id, ct));       // N queries, "hidden"

// Fix 1: projection (best for reads)
var rows = await db.Orders.AsNoTracking()
    .Where(o => o.Status == OrderStatus.Placed)
    .Select(o => new OrderRow(o.OrderNumber, o.Customer.Name, o.Lines.Count))
    .ToListAsync(ct);                                   // 1 query with JOIN / subquery

// Fix 2: eager loading with Include (when you need the entities)
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines).ThenInclude(l => l.Product)
    .Where(o => o.Status == OrderStatus.Placed)
    .ToListAsync(ct);

// Fix 3: batch loading with Contains
var ids = orders.Select(o => o.CustomerId).Distinct().ToList();
var customers = await db.Customers
    .Where(c => ids.Contains(c.Id))                 // WHERE id = ANY(@ids): 1 query
    .ToDictionaryAsync(c => c.Id, ct);

foreach (var o in orders)
    Console.WriteLine(customers[o.CustomerId].Name);

// Fix 4: AsSplitQuery for multiple collection Includes
var orders = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .AsSplitQuery()                // 3 small queries instead of 1 huge JOIN
    .ToListAsync(ct);

// Detection
// 1. See the SQL in development
options.UseNpgsql(cs)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging(env.IsDevelopment())
       // warning → exception when AsSplitQuery is missing on multiple collection Includes
       .ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning));

// 2. A test that counts an endpoint's queries
public class QueryCounter : DbCommandInterceptor
{
    public int Count;
    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData data,
        InterceptionResult<DbDataReader> result, CancellationToken ct = default)
    {
        Interlocked.Increment(ref Count);
        return base.ReaderExecutingAsync(command, data, result, ct);
    }
}
// Assert.True(counter.Count <= 2);   // if someone adds an N+1, the test breaks