Challenge: Find & Fix the N+1 Query in This Controller

[HttpGet("orders")]
public async Task<IActionResult> GetOrders()
{
    var orders = await _db.Orders
        .Where(o => o.Status == "Pending")
        .ToListAsync();

    var result = orders.Select(o => new
    {
        o.Id,
        o.CreatedAt,
        CustomerName = o.Customer.Name,       // Problem 1
        ItemCount    = o.OrderItems.Count,     // Problem 2
        Total        = o.OrderItems.Sum(i => i.Quantity * i.UnitPrice) // Problem 3
    }).ToList();

    return Ok(result);
}

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(msg => Debug.WriteLine(msg), LogLevel.Information)
           .EnableSensitiveDataLogging());

info: Microsoft.EntityFrameworkCore.Database.Command
      Executed DbCommand (2ms) SELECT ... FROM Orders WHERE Status = 'Pending'

info: Microsoft.EntityFrameworkCore.Database.Command
      Executed DbCommand (1ms) SELECT ... FROM Customers WHERE Id = 7

info: Microsoft.EntityFrameworkCore.Database.Command
      Executed DbCommand (1ms) SELECT ... FROM OrderItems WHERE OrderId = 1

info: Microsoft.EntityFrameworkCore.Database.Command
      Executed DbCommand (1ms) SELECT ... FROM Customers WHERE Id = 12
-- repeating for every order...

[HttpGet("orders")]
public async Task<IActionResult> GetOrders([FromQuery] int page = 1, [FromQuery] int pageSize = 20)
{
    var orders = await _db.Orders
        .AsNoTracking()
        .Where(o => o.Status == "Pending")
        .OrderByDescending(o => o.CreatedAt)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(o => new OrderSummaryDto
        {
            Id           = o.Id,
            CreatedAt    = o.CreatedAt,
            CustomerName = o.Customer.Name,
            ItemCount    = o.OrderItems.Count,
            Total        = o.OrderItems.Sum(i => i.Quantity * i.UnitPrice)
        })
        .ToListAsync();

    return Ok(orders);
}

SELECT o.Id, o.CreatedAt, c.Name AS CustomerName,
       COUNT(oi.Id) AS ItemCount,
       SUM(oi.Quantity * oi.UnitPrice) AS Total
FROM Orders o
INNER JOIN Customers c ON c.Id = o.CustomerId
LEFT JOIN OrderItems oi ON oi.OrderId = o.Id
WHERE o.Status = 'Pending'
GROUP BY o.Id, o.CreatedAt, c.Name
ORDER BY o.CreatedAt DESC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

var orders = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(i => i.Product)
    .Where(o => o.Status == "Pending")
    .OrderByDescending(o => o.CreatedAt)
    .Take(pageSize)
    .ToListAsync();

100 orders × 50 items each = 5,000 rows fetched

var orders = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
    .AsSplitQuery()   // runs 3 separate queries, joins in memory
    .Where(o => o.Status == "Pending")
    .Take(pageSize)
    .ToListAsync();

-- Query 1
SELECT TOP 20 o.*, c.* FROM Orders o JOIN Customers c ... WHERE Status='Pending'
-- Query 2
SELECT oi.* FROM OrderItems WHERE OrderId IN (1, 2, 3, ...)

public class OrderQueries
{
    // Compiled once at startup
    private static readonly Func<AppDbContext, string, int, int, IAsyncEnumerable<OrderSummaryDto>>
        GetPendingOrders = EF.CompileAsyncQuery(
            (AppDbContext db, string status, int skip, int take) =>
                db.Orders
                  .AsNoTracking()
                  .Where(o => o.Status == status)
                  .OrderByDescending(o => o.CreatedAt)
                  .Skip(skip)
                  .Take(take)
                  .Select(o => new OrderSummaryDto
                  {
                      Id           = o.Id,
                      CreatedAt    = o.CreatedAt,
                      CustomerName = o.Customer.Name,
                      ItemCount    = o.OrderItems.Count
                  }));

    public static IAsyncEnumerable<OrderSummaryDto> GetPendingOrdersAsync(
        AppDbContext db, int page, int pageSize)
        => GetPendingOrders(db, "Pending", (page - 1) * pageSize, pageSize);
}

public record PagedResult<T>(IReadOnlyList<T> Items, int TotalCount, int Page, int PageSize)
{
    public int TotalPages => (int)Math.Ceiling((double)TotalCount / PageSize);
    public bool HasNext => Page < TotalPages;
}

public async Task<PagedResult<OrderSummaryDto>> GetPagedOrdersAsync(int page, int pageSize)
{
    var query = _db.Orders
        .AsNoTracking()
        .Where(o => o.Status == "Pending");

    var totalCount = await query.CountAsync();

    var items = await query
        .OrderByDescending(o => o.CreatedAt)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .Select(o => new OrderSummaryDto { /* ... */ })
        .ToListAsync();

    return new PagedResult<OrderSummaryDto>(items, totalCount, page, pageSize);
}

// In entity configuration
builder.HasIndex(o => new { o.Status, o.CreatedAt })
       .IsDescending(false, true); // Status ASC, CreatedAt DESC

[MemoryDiagnoser]
public class OrderControllerBenchmark
{
    private AppDbContext _db = null!;

    [GlobalSetup]
    public void Setup() => _db = CreateDb(); // seed with test data

    [Benchmark(Baseline = true)]
    public async Task<List<object>> Naive_NPlus1()
    {
        var orders = await _db.Orders.Where(o => o.Status == "Pending").ToListAsync();
        return orders.Select(o => new { o.Id, Name = o.Customer.Name }).ToList();
    }

    [Benchmark]
    public async Task<List<OrderSummaryDto>> Optimized_Select()
    {
        return await _db.Orders
            .AsNoTracking()
            .Where(o => o.Status == "Pending")
            .Take(20)
            .Select(o => new OrderSummaryDto { Id = o.Id, CustomerName = o.Customer.Name })
            .ToListAsync();
    }
}