Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 41 ยท Querying with LINQ

Projections with Select

Use Select to load only the columns you need into DTOs, records and anonymous types, shape related data without Include, and avoid the cost of loading and tracking full entities.

When you write db.Customers.ToListAsync(), EF Core selects every column of every row, creates a full entity object for each one and (by default) tracks it. A customer list screen that shows a name and a city does not need the profile text, the password hash and twelve other columns.

A projection with Select says exactly what you want. EF Core then selects only those columns, skips tracking (nothing to track: the result is not an entity) and often avoids joins. It is one of the cheapest big performance wins in EF Core.

The real EF Core cannot run on this site, so queries are read-only. The runnable stand-ins use LINQ over in-memory data to show the shapes.

Selecting columns

C#
// One column: List<string>
var names = await db.Customers.Select(c => c.Name).ToListAsync();

// Several columns: an anonymous type
var rows = await db.Customers
    .Select(c => new { c.Id, c.Name, c.City })
    .ToListAsync();

The first query generates SELECT "c"."Name" FROM "Customers" AS "c". The second selects three columns. Anonymous types are fine inside a method, but you cannot return them from it, so for results you pass around use a named type.

DTOs and records

C#
public record CustomerListItem(int Id, string Name, string City);

public async Task<List<CustomerListItem>> ListAsync() =>
    await db.Customers
        .OrderBy(c => c.Name)
        .Select(c => new CustomerListItem(c.Id, c.Name, c.City))
        .ToListAsync();

A DTO (data transfer object) is a class made for one use: an API response, a list row, a form model. Projecting straight into it means no mapping step and no accidental exposure of sensitive columns such as password hashes.

You can reach related data inside Select, and EF Core builds the joins itself. You do not need Include:

C#
var orders = await db.Orders
    .Select(o => new OrderSummary(
        o.Id,
        o.Customer.Name,                         // join to Customers
        o.Lines.Count,                           // correlated COUNT
        o.Lines.Sum(l => l.Quantity * l.UnitPrice)))
    .ToListAsync();

One SQL statement returns exactly four columns per order. Compare that with Include(o => o.Lines), which loads every line entity just so you can count and add them in memory.

Nested collections

C#
var customers = await db.Customers
    .Select(c => new CustomerWithOrders(
        c.Name,
        c.Orders
         .OrderByDescending(o => o.PlacedAt)
         .Take(3)
         .Select(o => new OrderRow(o.Id, o.Total))
         .ToList()))
    .ToListAsync();

"Customers with their latest three orders" in one query, with filtering and a limit inside the collection, which Include historically could not do as easily (filtered include now can, see Eager Loading with Include).

Computing in the projection

Arithmetic, string concatenation, conditionals and many functions are translated:

C#
var view = await db.Products.Select(p => new
{
    p.Name,
    PriceWithTax = p.Price * 1.2m,
    Label = p.Name + " (" + p.Category + ")",
    Tier = p.Price > 100m ? "Premium" : "Standard",
    InStock = p.Stock > 0
}).ToListAsync();

Note the rule: the last Select in the query decides what runs in SQL, but code that cannot be translated (calling your own method, formatting with culture-specific methods) should be applied after ToListAsync():

C#
var rows = await db.Products.Select(p => new { p.Name, p.Price }).ToListAsync();
var shown = rows.Select(r => new { r.Name, Text = FormatMoney(r.Price) });   // in memory, fine

Read more about this boundary in Client vs Server Evaluation.

Why projections are faster

Entity or projection?

Entity All columns, tracked
Projection Chosen columns only
Less memory Smaller objects
No tracking Nothing to snapshot

Use entities to write, projections to read

1 / 2

A good rule: use entities when you intend to change and save them, use projections when you only read. Projecting into entity types is possible (Select(c => new Customer { Name = c.Name })) but those instances are not tracked and are easy to confuse with real, complete entities, so prefer DTOs.

AutoMapper-style and ProjectTo

Libraries like Mapster and AutoMapper offer ProjectTo<T>(), which builds the Select expression from a mapping configuration so DTO mappings do not need hand-written lambdas. They still produce a Select and a narrow SQL statement; hand-written projections are explicit and need no extra dependency.

Stand-in: shape versus entity

This stand-in is not EF Core. It counts how many fields travel when you load whole objects versus projecting only what a list needs.

C# Stand-in: full entity versus projection

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Stand-in: a summary that crosses relationships

C# Stand-in: order summaries via navigations

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

In EF Core the same lambda becomes one SELECT with a join and subqueries; empty collections give a count of 0 and a sum of 0 (the SQL uses COALESCE).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main benefit of Select(c => new { c.Id, c.Name }) over loading whole entities?

    EF Core selects only those columns, and projected results are not tracked entities.
  2. 2. Do you need Include to use o.Customer.Name inside a Select?

    Include is for loading related entities; a projection simply reads what it needs.
  3. 3. Why is a DTO preferable to returning an entity from an API?

    A DTO is a deliberate shape; it also decouples your API contract from the table design.
  4. 4. Where should code that EF Core cannot translate (your own formatting method) run?

    Project the raw values in SQL, materialize, then apply untranslatable code in memory.

Summary

  • Select limits the columns EF Core reads and avoids tracking entities.
  • Project into records or DTOs; anonymous types are for local use.
  • Navigation properties inside Select create joins and subqueries automatically, with no Include.
  • Read with projections, write with tracked entities, and run untranslatable code after ToListAsync.

Next: Joins, Grouping and Aggregates