Refactor an endpoint that loads a page of orders and then queries the latest status separately inside a loop.

• Keep ordering deterministic for both the order page and latest status.
• Do not materialize orders before projecting status.
• Orders with no status history return null status data if the model permits them.
• Keep page size bounded.

I move the latest-status subquery into the projection while the whole expression is still translatable. I shape the latest child once, ordered by timestamp and id, and project it into the DTO. The provider can translate that into a lateral join or correlated subquery rather than issuing commands from an application loop.

public static Task<List<OrderStatusItem>> LoadOrderStatusesAsync(
    AppDbContext db,
    int pageSize,
    CancellationToken cancellationToken)
{
    pageSize = Math.Clamp(pageSize, 1, 100);

    return db.Orders
        .AsNoTracking()
        .OrderByDescending(order => order.CreatedAtUtc)
        .ThenByDescending(order => order.Id)
        .Take(pageSize)
        .Select(order => new
        {
            order.Id,
            Latest = order.StatusHistory
                .OrderByDescending(status => status.ChangedAtUtc)
                .ThenByDescending(status => status.Id)
                .Select(status => new
                {
                    status.Status,
                    status.ChangedAtUtc
                })
                .FirstOrDefault()
        })
        .Select(row => new OrderStatusItem(
            row.Id,
            row.Latest == null ? null : row.Latest.Status,
            row.Latest == null ? null : row.Latest.ChangedAtUtc))
        .ToListAsync(cancellationToken);
}

The expected shape is one SQL command for one page. Exact SQL varies by provider, so command logging or an interceptor should verify the round-trip count. An index on status history beginning with order id and descending change order supports the latest-child lookup.

• An order has no status history.
• Two status rows share a timestamp.
• The page contains the maximum allowed number of orders.
• Serialization accesses no lazy-loaded navigation afterward.

• Count database commands for a representative request.
• Create tied timestamps to verify the id tie-breaker.
• Disable lazy loading and ensure the endpoint still works.
• Inspect row count and query plan, not only command count.

• Moving the loop to a helper without changing query count.
• Replacing N plus one with a broad Include and huge payload.
• Materializing before the projection.
• Fixing query count but losing deterministic latest-row behavior.