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

The Broken Controller — Spot the Problems

[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);
}

Found them? There are three issues:

o.Customer.Name — triggers a lazy load query per order (N queries)
o.OrderItems.Count — triggers another lazy load per order (N more queries)
o.OrderItems.Sum(...) — triggers yet another lazy load per order (N more)
No pagination — this returns the entire Orders table

For 100 pending orders: 301+ SQL queries for one GET request.

Confirm With EF Core Query Logging

Enable logging in Program.cs (development only):

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

Hit the endpoint and watch the output:

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...

You'll see the same query template repeated N times. That's your N+1.

Fix 1 — Project to DTO With Select()

The cleanest fix: let the database do the work.

[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);
}

Generated SQL — one query:

SQL

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

From 301 queries to 1. Add an index on Orders.Status and this will be fast at any scale.

Fix 2 — Include() When You Need Full Entities

Sometimes you genuinely need to work with the full entity graph (e.g., to modify it). Use Include:

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();

Still one query (or a few with AsSplitQuery), no N+1.

Fix 3 — AsSplitQuery for Cartesian Explosions

If OrderItems contains many rows, the JOIN approach multiplies rows. SQL returns one row per (Order × OrderItem) combination:

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

Use split queries to avoid this:

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();

The generated queries:

SQL

-- 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, ...)

Fix 4 — Compiled Queries for Hot Paths

If an endpoint is called thousands of times per second, EF's LINQ-to-SQL translation cost is measurable. Compiled queries translate once and cache the plan:

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);
}

Compiled queries can't use Include — use Select projections instead.

Fix 5 — Pagination to Avoid Full Table Scans

Never return unbounded result sets. Always paginate:

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);
}

Add an index on (Status, CreatedAt DESC) to make the pagination query fast:

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

Benchmarking Before and After

Use BenchmarkDotNet to measure:

[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();
    }
}

Typical results for 100 orders:

| Method | Mean | Allocated | |---|---|---| | Naive_NPlus1 | 320 ms | 2.4 MB | | Optimized_Select | 8 ms | 120 KB |

40x faster, 20x less memory. And this is with a local database — the difference grows with network latency.

Checklist: EF Core Performance Review

[ ] Every query in a loop uses Include or Select — no nav property access in loops
[ ] GET endpoints use AsNoTracking()
[ ] All list endpoints are paginated with Skip/Take
[ ] Indexes exist for WHERE and ORDER BY columns
[ ] Response shapes use DTO projections with Select(), not full entities
[ ] Large collections use AsSplitQuery()
[ ] Hot-path queries use compiled queries
[ ] Query logging enabled in development to catch regressions