Implement an EF Core order-list query that returns only the fields and line summaries needed by the API.

An order with two lines containing quantities 2 and 3 returns LineCount = 2, ItemQuantity = 5, and the sum of quantity multiplied by unit price. No entity navigation collections are materialized.

• Filter paid orders before projection.
• Order by CreatedAtUtc and then Id descending.
• Select only response fields.
• Pass the request cancellation token to materialization.

I keep the full query as IQueryable until the final ToListAsync. The projection lets EF translate navigation access and aggregates into SQL. A nullable decimal cast makes an empty sum safe, although a real order should normally have at least one line. AsNoTracking expresses that this path does not need change tracking.

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

    return db.Orders
        .AsNoTracking()
        .Where(order => order.Status == OrderStatus.Paid)
        .OrderByDescending(order => order.CreatedAtUtc)
        .ThenByDescending(order => order.Id)
        .Select(order => new OrderListItem(
            order.Id,
            order.Customer.DisplayName,
            order.CreatedAtUtc,
            order.Lines.Count(),
            order.Lines.Sum(line => line.Quantity),
            order.Lines.Sum(line =>
                (decimal?)(line.Quantity * line.UnitPrice)) ?? 0m))
        .Take(pageSize)
        .ToListAsync(cancellationToken);
}

EF Core emits one bounded SQL query with correlated aggregates or equivalent joins, depending on the provider. The database returns DTO-shaped scalar data rather than full orders, customers, and lines. The order and filter columns should be supported by the production query's index strategy.

• An order unexpectedly has no lines.
• Several orders share a creation timestamp.
• A caller requests zero or an excessive page size.
• Money precision differs between CLR and database columns.

• Inspect ToQueryString() and verify only required columns are selected.
• Use equal timestamps to catch missing id ordering.
• Verify cancellation reaches ToListAsync.
• Run against realistic row counts when checking the execution plan.

• Using Include when the endpoint needs only scalar DTO fields.
• Calling ToList before Select and projecting in memory.
• Returning an unbounded result set.
• Assuming aggregate translation without inspecting generated SQL.