N+1 Queries and Split Queries

using Microsoft.EntityFrameworkCore;

public record InvoiceRow(int InvoiceId, string CustomerName, decimal Amount, decimal Paid);

public class Invoice
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public string CustomerName { get; set; } = "";
    public decimal Amount { get; set; }
    public DateOnly DueOn { get; set; }
}

public class InvoicePayment
{
    public int Id { get; set; }
    public int InvoiceId { get; set; }
    public decimal Amount { get; set; }
}

public class BillingDb(DbContextOptions<BillingDb> options) : DbContext(options)
{
    public DbSet<Invoice> Invoices => Set<Invoice>();
    public DbSet<InvoicePayment> Payments => Set<InvoicePayment>();
}

public class OverdueReport(BillingDb db)
{
    // N+1: one query for the invoices, then one SUM per invoice.
    public async Task<List<InvoiceRow>> SlowAsync(DateOnly today, CancellationToken ct)
    {
        var invoices = await db.Invoices.AsNoTracking().Where(i => i.DueOn < today).ToListAsync(ct);
        var rows = new List<InvoiceRow>();
        foreach (var invoice in invoices)
        {
            var paid = await db.Payments.Where(p => p.InvoiceId == invoice.Id).SumAsync(p => p.Amount, ct);
            rows.Add(new InvoiceRow(invoice.Id, invoice.CustomerName, invoice.Amount, paid));
        }
        return rows;
    }

    // Two queries in total, whatever the number of invoices.
    public async Task<List<InvoiceRow>> FastAsync(DateOnly today, CancellationToken ct)
    {
        var invoices = await db.Invoices.AsNoTracking().Where(i => i.DueOn < today).ToListAsync(ct);
        var ids = invoices.Select(i => i.Id).ToList();

        var paidById = await db.Payments
            .Where(p => ids.Contains(p.InvoiceId))
            .GroupBy(p => p.InvoiceId)
            .Select(g => new { InvoiceId = g.Key, Paid = g.Sum(p => p.Amount) })
            .ToDictionaryAsync(x => x.InvoiceId, x => x.Paid, ct);

        return invoices
            .Select(i => new InvoiceRow(i.Id, i.CustomerName, i.Amount, paidById.GetValueOrDefault(i.Id)))
            .ToList();
    }
}

using System.Data.Common;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Xunit;

public sealed class CommandCounter : DbCommandInterceptor
{
    private int _count;
    public int Count => Volatile.Read(ref _count);

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        Interlocked.Increment(ref _count);
        return result;
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        Interlocked.Increment(ref _count);
        return ValueTask.FromResult(result);
    }
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public List<OrderLine> Lines { get; set; } = [];
}

public class OrderLine
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public string Sku { get; set; } = "";
    public int Quantity { get; set; }
}

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();
}

public record OrderHistoryItem(int Id, int LineCount, int Units);

public static class OrderQueries
{
    public static Task<List<OrderHistoryItem>> HistoryAsync(ShopDb db, int customerId, CancellationToken ct) =>
        db.Orders
            .Where(o => o.CustomerId == customerId)
            .OrderBy(o => o.Id)
            .Select(o => new OrderHistoryItem(o.Id, o.Lines.Count, o.Lines.Sum(l => l.Quantity)))
            .ToListAsync(ct);
}

public class OrderQueryTests
{
    [Fact]
    public async Task Order_history_is_one_query_regardless_of_order_count()
    {
        var ct = TestContext.Current.CancellationToken;
        await using var connection = new SqliteConnection("Data Source=:memory:");
        await connection.OpenAsync(ct);

        var counter = new CommandCounter();
        var options = new DbContextOptionsBuilder<ShopDb>().UseSqlite(connection).AddInterceptors(counter).Options;
        await using var db = new ShopDb(options);
        await db.Database.EnsureCreatedAsync(ct);

        for (var i = 0; i < 25; i++)
        {
            db.Orders.Add(new Order { CustomerId = 7, Lines = [new() { Sku = "KETTLE-BLUE", Quantity = 2 }] });
        }
        await db.SaveChangesAsync(ct);
        db.ChangeTracker.Clear();

        var before = counter.Count;
        var history = await OrderQueries.HistoryAsync(db, customerId: 7, ct);

        Assert.Equal(25, history.Count);
        Assert.Equal(1, counter.Count - before);
    }
}

{
  // appsettings.Development.json
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.EntityFrameworkCore": "Warning",
      // Every executed command with its duration: repeated SELECTs are the N+1 signature.
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}