← Back to all posts
dotnetentity-frameworkperformancedatabasecsharpaspnet-core

EF Core: Projections

EF Core: Projections

Reading Time: 3 minutes

Problem

A common EF Core performance issue is loading full entities when an endpoint only needs a small subset of fields.

Typical example:

  • querying Order entities with multiple navigation properties
  • returning only Id, CustomerName, and Total to the client

This causes:

  • more columns fetched than necessary
  • larger result payloads
  • extra memory and tracking overhead
  • slower endpoints under load

In short, over-fetching data hurts both database and API performance.

Solution

Use projections with Select to shape query results into DTOs or read models.

Key practices:

  • project only required fields
  • keep projection logic server-translatable
  • combine projections with AsNoTracking() for read-only endpoints
  • avoid materializing entities before projecting

For most read APIs, projection-first queries are the safest default.

Description

1) Basic projection to DTO (.NET)

public sealed class ProductListItemDto
{
    public int Id { get; init; }
    public string Name { get; init; } = string.Empty;
    public decimal Price { get; init; }
}

var products = await db.Products
    .Where(p => p.IsActive)
    .Select(p => new ProductListItemDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price
    })
    .AsNoTracking()
    .ToListAsync();

This generates SQL that fetches only selected columns instead of full entity data.

2) Projection with aggregates

public sealed class OrderSummaryDto
{
    public int OrderId { get; init; }
    public string CustomerName { get; init; } = string.Empty;
    public int ItemCount { get; init; }
    public decimal TotalAmount { get; init; }
}

var summaries = await db.Orders
    .Where(o => o.CreatedAt >= fromDate)
    .Select(o => new OrderSummaryDto
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        TotalAmount = o.Items.Sum(i => i.UnitPrice * i.Quantity)
    })
    .AsNoTracking()
    .ToListAsync();

EF Core translates these calculations into SQL, keeping work close to the database.

3) Nested projections for response models

public sealed class OrderDetailDto
{
    public int Id { get; init; }
    public string CustomerName { get; init; } = string.Empty;
    public List<OrderItemDto> Items { get; init; } = [];
}

public sealed class OrderItemDto
{
    public int ProductId { get; init; }
    public string ProductName { get; init; } = string.Empty;
    public int Quantity { get; init; }
}

var order = await db.Orders
    .Where(o => o.Id == orderId)
    .Select(o => new OrderDetailDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        Items = o.Items.Select(i => new OrderItemDto
        {
            ProductId = i.ProductId,
            ProductName = i.Product.Name,
            Quantity = i.Quantity
        }).ToList()
    })
    .AsNoTracking()
    .FirstOrDefaultAsync();

This avoids loading full tracked graphs when you only need a response shape.

4) Common mistakes to avoid

  • Calling ToListAsync() too early and projecting in memory afterward.
  • Using methods inside Select that cannot translate to SQL.
  • Returning entities directly from read endpoints when DTOs are enough.

A good mental model: keep the whole query expression database-friendly until final materialization.

Summary

EF Core projections are one of the highest-impact optimizations for read-heavy applications.

They help you:

  • fetch less data
  • reduce memory/CPU pressure
  • return cleaner API contracts
  • improve endpoint latency

If an endpoint is read-only, start with projection + no-tracking, then expand only when necessary.

References