Project to DTOs with Select

When you're building an API endpoint or a report query, fetching the entire entity graph from your database is wasteful. You load columns you don't need, trigger lazy-load surprises, and burn through memory on a 10,000-row result set. Project to DTOs with Select is the technique that stops this: you use LINQ's Select to define exactly which columns you want, and Entity Framework Core translates that directly into a narrow SQL query that fetches only what matters.

Why Select Over Include

Most developers reach for Include when they need related data. It makes sense at first—load the order, include the customer, done. But Include pulls the full rows back into memory, and if you only need the customer's name, you've wasted bandwidth and CPU cycles.

Select, on the other hand, pushes your column list down into the SQL query itself. The database does the joining and projection for you, and you get back only a DTO with the exact fields you need.

Here's the practical difference:

csharp

// Include approach: loads full Order + full Customer
var orders = await db.Orders
  .Include(o => o.Customer)
  .Where(o => o.Status == Status.Paid)
  .ToListAsync(ct);

// Select approach: projects only what you need
var orders = await db.Orders
  .Where(o => o.Status == Status.Paid)
  .Select(o => new OrderRow(
    o.Id,
    o.Customer.Name,
    o.Total
  ))
  .ToListAsync(ct);

The second query generates SQL that looks roughly like this:

csharp

SELECT o.Id, c.Name, o.Total
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id
WHERE o.Status = 1

Much leaner. Much faster at scale.

Select Generates a Narrow SQL SELECT

When you write a projection in Select, EF Core analyzes it and builds a SQL query that matches. It's not magic—it's LINQ-to-SQL translation at work. You tell it which properties you want, and it constructs the column list and joins.

Let's say you have this model:

csharp

public class Order
{
  public int Id { get; set; }
  public DateTime CreatedAt { get; set; }
  public decimal Total { get; set; }
  public Status Status { get; set; }
  public int CustomerId { get; set; }
  public Customer Customer { get; set; }
  public ICollection<OrderItem> Items { get; set; }
}

public class Customer
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Email { get; set; }
}

public record OrderRow(int Id, string CustomerName, decimal Total);

Now this query:

csharp

var list = await db.Orders
  .Where(o => o.Status == Status.Paid)
  .Select(o => new OrderRow(
    o.Id,
    o.Customer.Name,
    o.Total
  ))
  .ToListAsync(ct);

Becomes:

csharp

SELECT o.Id, c.Name, o.Total
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id
WHERE o.Status = 1

Three columns. One join. No bloat. In a high-volume API or report, this difference compounds fast. If you have 50,000 orders and each Order entity loads 20 columns, you're moving 1 million columns unnecessarily. With Select, you move exactly 150,000.

No Tracking, No Lazy-Load Surprises

When you use Select to project into a DTO, the result is no longer an entity that EF Core tracks. It's just plain objects—records, anonymous types, or POCOs. This means:

No change tracking overhead. EF Core isn't watching for modifications.
No lazy loading. If you didn't fetch a property in the Select, it won't magically load later.
No stale-data surprises. You get what you asked for, nothing more.

This is crucial in HTTP request contexts. You load a DTO, serialize it to JSON, send it out, and you're done. There's no chance of accidentally triggering a second database hit because you accessed a navigation property that wasn't eagerly loaded.

csharp

// DTO is not tracked
var dto = await db.Orders
  .Where(o => o.Id == orderId)
  .Select(o => new OrderRow(o.Id, o.Customer.Name, o.Total))
  .FirstOrDefaultAsync(ct);

// If you try to access dto.Items, it won't exist.
// If you try to save changes to dto, EF Core won't even know what to do.
// That's the point.

In contrast, if you loaded the full entity and forgot to Include the items collection, accessing order.Items would trigger a lazy-load query—a hidden database call inside your controller. Your N+1 bug is born.

DTOs prevent that entirely. You define the shape you need, fetch it once, and move on.

Works Through Navigations Without Include

Here's a subtle but powerful point: you can traverse navigation properties in a Select projection without ever writing Include. EF Core knows how to join through them because your projection tells it which columns you actually need.

Consider a more complex example:

csharp

public class Order
{
  public int Id { get; set; }
  public int CustomerId { get; set; }
  public Customer Customer { get; set; }
}

public class Customer
{
  public int Id { get; set; }
  public string Name { get; set; }
  public int CityId { get; set; }
  public City City { get; set; }
}

public class City
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Country { get; set; }
}

public record OrderWithLocationDto(int OrderId, string CustomerName, string CityName, string Country);

Now this projection chains through three entities:

csharp

var list = await db.Orders
  .Where(o => o.Status == Status.Paid)
  .Select(o => new OrderWithLocationDto(
    o.Id,
    o.Customer.Name,
    o.Customer.City.Name,
    o.Customer.City.Country
  ))
  .ToListAsync(ct);

EF Core generates:

csharp

SELECT o.Id, c.Name, ct.Name, ct.Country
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id
INNER JOIN Cities ct ON c.CityId = ct.Id
WHERE o.Status = 1

You never wrote a single Include. EF Core figured out the join path by analyzing what you projected. This is efficient, readable, and safe.

Real-World Scenarios

API list endpoints: A GET /orders endpoint that returns a paginated list with 20 fields per order is slow and over-engineered. Project to a slim DTO with Id, CustomerName, Total, and CreatedDate. Your API response shrinks, client parsing is faster, and the query runs in milliseconds instead of seconds.

Reports and dashboards: A reporting query that needs 50 orders with aggregate data—customer name, order total, item count—can be built entirely with Select and grouping. No full entity load. No N+1 queries. One tight SQL statement.

Search result pages: When you search orders by customer or date, you don't need the full order history or the items collection. Project to a result DTO: Id, CustomerName, Total, OrderDate. Fast, focused, done.

Common Mistakes

Forgetting to call ToListAsync or FirstOrDefaultAsync. If you don't materialize the query, the Select hasn't run yet. You're still holding an IQueryable, and lazy evaluation might bite you if the context is disposed.
Projecting with complex logic inside Select. Avoid method calls that can't be translated to SQL: .Select(o => new { Name = o.Customer.GetFullName() }) will fail. Keep projections to simple property access and arithmetic.
Using Select without a Where clause when you expect filtering. If you don't filter first, you project the entire table. On a large dataset, that's slow. Apply Where before Select.
Mixing Select with AsNoTracking. Using Select already returns untracked DTOs, so AsNoTracking is redundant. It doesn't hurt, but it's unnecessary noise.
Projecting optional navigations without null checks. If Customer can be null, and you access o.Customer.Name in the projection, your SQL will still generate an INNER JOIN, dropping orders with no customer. Use a ternary or null-coalescing operator if you want an OUTER JOIN.

Key Takeaways

Select projects entities into DTOs, and EF Core translates the projection into a narrow SQL SELECT statement with only the columns you need.
Projections eliminate tracking overhead and lazy-load surprises because DTOs are not managed entities.
You can traverse navigation properties in a Select without writing Include—EF Core generates the joins automatically based on what you project.
Apply Where filters before Select to reduce the dataset early.
Always materialize with ToListAsync, FirstOrDefaultAsync, or SingleAsync to execute the query and prevent lazy-eval surprises.
Keep projection logic simple: property access and arithmetic. Avoid method calls that can't translate to SQL.
DTOs are the right shape for API responses and reports—they're fast, explicit, and safe.

Project to DTOs with Select: a .NET guide for developers