How to Reuse a Select Projection with an Expression in EF Core

When several queries return the same response, you want one definition of how to build it. The obvious way, a normal C# method inside Select, loads every column, and throws NullReferenceException as soon as it touches a navigation. The same code written as an Expression lets EF Core read it, and it sends only the columns and joins it needs.

Prerequisites

• A .NET C# project with EF Core and a table with a relationship. The examples use EF Core 10, Oracle's MySql.EntityFrameworkCore, MySQL 9.7, and products that belong to categories, with this record:

The response record

public sealed record ProductSummary(string Sku, string Category);

Step 1: See Why a Method Doesn't Work

The first idea is a method that builds the record:

A temporary endpoint

private static ProductSummary ToSummary(Product p) => new(p.Sku, p.Category.Name);

// …

var products = await db.Products
    .OrderBy(p => p.Id)
    .Take(2)
    .Select(p => ToSummary(p))
    .ToListAsync();

It compiles. Running it gives a 500:

API log (the lines that matter)

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (12ms) [Parameters=[@p='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `p`.`Id`, `p`.`ArchivedAtUtc`, `p`.`CategoryId`, `p`.`CreatedAtUtc`, `p`.`Description`, `p`.`IsArchived`, `p`.`Name`, `p`.`QuantityOnHand`, `p`.`ReorderLevel`, `p`.`Sku`, `p`.`UnitPrice`, `p`.`UpdatedAtUtc`, `p`.`Version`
      FROM `products` AS `p`
      ORDER BY `p`.`Id`
      LIMIT @p
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'Dewiride.Inventory.Data.InventoryDbContext'.
      System.NullReferenceException: Object reference not set to an instance of an object.
      System.NullReferenceException: Object reference not set to an instance of an object.

Two things went wrong, and the log shows both:

• The SELECT has all thirteen columns. EF Core can't look inside a compiled method, so it doesn't know what ToSummary needs. It loads the whole row, and calls the method in C#. EF Core's client evaluation page describes this for the top-level projection.
• There's no JOIN. So p.Category is null, and p.Category.Name throws.

Step 2: Write It as an Expression

An Expression<Func<…>> holds a description of the code, which EF Core can read. The body is exactly the same, and the type is the only real change:

A static field on the record or endpoint class

private static readonly Expression<Func<Product, ProductSummary>> SummaryExpression =
    p => new ProductSummary(p.Sku, p.Category.Name);

Expression needs using System.Linq.Expressions;. Pass it straight to Select:

A temporary endpoint

var products = await db.Products
    .OrderBy(p => p.Id)
    .Take(2)
    .Select(SummaryExpression)
    .ToListAsync();

Response

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

[
  {
    "sku": "CBL-USBC-2M",
    "category": "Cables"
  },
  {
    "sku": "CBL-USBC-1M",
    "category": "Cables"
  }
]

API log

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[@p='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT `p0`.`Sku`, `c`.`Name`
      FROM (
          SELECT `p`.`Id`, `p`.`CategoryId`, `p`.`Sku`
          FROM `products` AS `p`
          ORDER BY `p`.`Id`
          LIMIT @p
      ) AS `p0`
      INNER JOIN `categories` AS `c` ON `p0`.`CategoryId` = `c`.`Id`
      ORDER BY `p0`.`Id`

The join is there, and p0.Sku and c.Name are the only columns returned to the app.

Step 3: Use It Everywhere

Put the expression on the record, so it lives next to what it builds. This is the one from the inventory API:

src/Dewiride.Inventory.Api/Features/Products/ProductContracts.cs (excerpt)

public static readonly Expression<Func<Product, ProductResponse>> FromProduct = p =>
    new ProductResponse(
        p.Id,
        p.Sku,
        p.Name,
        p.Description,
        p.CategoryId,
        p.Category.Name,
        p.UnitPrice,
        p.QuantityOnHand,
        p.ReorderLevel,
        p.IsArchived,
        p.ArchivedAtUtc,
        p.Version,
        p.CreatedAtUtc,
        p.UpdatedAtUtc);

Then every query that returns a product uses it, and a new field only needs adding once:

src/Dewiride.Inventory.Api/Features/Products/ProductEndpoints.cs (excerpt)

var product = await db.Products
    .Where(p => p.Id == id)
    .Select(ProductResponse.FromProduct)
    .FirstOrDefaultAsync(cancellationToken);

Common Mistakes

Mistake	What happens	Do this instead
A method in Select	Every column loads, and navigations are empty	Use an expression
Declaring it as Func<…> instead of Expression<Func<…>>	It's compiled code again, so EF Core can't read it	Keep Expression
Calling a method inside the expression	EF Core can't translate it	Keep to properties and new
Calling .Compile() on it	It becomes a plain method	Pass the expression itself

FAQ

Why does a method work without the navigation?

Without p.Category, nothing is null, so new(p.Sku) works. It still loads all the columns, so you pay for data you don't use, with no error to warn you.

Can I add an Include to make the method work?

Yes, and then it loads the category's columns as well. The expression is less code and less data.

Conclusion

To share a projection, write it as Expression<Func<TEntity, TResponse>> and pass it to Select. EF Core reads the expression, so the SQL has only what it needs, and navigations become joins. A normal method loads the whole row and leaves navigations empty, so avoid it in Select.

Additional Resources

• Client vs. Server Evaluation — what runs in C#, and what runs in the database.
• Efficient Querying — projecting only what you need.

Related

• How to Select Only the Columns You Need with EF Core
• How to Load Related Data in EF Core: Include vs Select