Project EF Core queries before materializing

ToListAsync() executes and buffers the query. With a relational provider, EF Core can translate supported query expressions before it into SQL. Operations applied to the resulting list run in memory.

Filter, order, limit, and project before that boundary:

public sealed record OrderSummary(
    Guid Id,
    string CustomerName,
    decimal Total,
    DateTime CreatedAtUtc);

List<OrderSummary> orders = await db.Orders
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.CreatedAtUtc)
    .ThenByDescending(order => order.Id)
    .Take(50)
    .Select(order => new OrderSummary(
        order.Id,
        order.Customer.Name,
        order.Total,
        order.CreatedAtUtc))
    .ToListAsync(cancellationToken);

Where filters the candidate rows, OrderBy and ThenBy determine which rows come first, and Take limits the result. Select defines the result shape, so EF Core can generate SQL that returns only the required values. The Id tie-breaker makes the ordering fully unique when multiple orders have the same timestamp. This sample assumes that Customer and Customer.Name are required.

For a fair comparison, the entity query must use the same filter, ordering, and limit:

List<Order> entities = await db.Orders
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.CreatedAtUtc)
    .ThenByDescending(order => order.Id)
    .Take(50)
    .Include(order => order.Customer)
    .ToListAsync(cancellationToken);

// The full entities are already loaded here.
List<OrderSummary> summaries = entities
    .Select(order => new OrderSummary(
        order.Id,
        order.Customer.Name,
        order.Total,
        order.CreatedAtUtc))
    .ToList();

The second query still fetches full Order and Customer entity shapes, materializes those entities, and then creates the DTOs. A tracking query also incurs change-tracking work and retains tracked entity state.

When projecting a DTO, you do not need Include merely to read selected values from a related entity. EF Core can translate navigation access in the projection without materializing the related entity. This is query shaping, not a blanket rule that Include is bad.

Keep filters, ordering, joins, and other database-side operations translatable. EF Core can evaluate an untranslatable expression in the top-level projection on the client, but an untranslatable expression elsewhere normally causes query translation to fail. Calling AsEnumerable() or AsAsyncEnumerable() explicitly makes subsequent operators run on the client.

This DTO projection contains only scalar values and no entity instances, so it does not create tracked entities. A custom projection that includes an entity instance can still track that entity.

Projection can reduce transferred data and materialization work, but indexes and the generated query plan still determine much of the database-side cost. For relational providers, inspect the generated SQL for important queries and measure before claiming a performance gain.

Use tracked entity instances when the workflow needs load-change-save semantics, and load only the relationships the update actually needs. For read models, API responses, and list screens that do not need entity instances, project the required shape before execution.