EF Core Specifications for Includes, Projection, and Tracking

An EF Core specification with includes and projection is doing more than naming a Boolean rule. It is describing the result shape that a query should produce. That distinction matters because a read screen may need three scalar values, while an update workflow may need a tracked entity graph. Returning the same full graph for both cases is convenient, but it is rarely deliberate.

This article focuses only on result shape: DTO projections, eager loading, filtered includes, tracking modes, identity resolution, and the choice between single and split queries. Dynamic filtering, sorting, and pagination are separate concerns. The goal here is to make the data returned by an EF Core specification visible, reviewable, and testable.

The examples target net10.0, C# 14, and EF Core 10.0.10. They use SQLite as a relational smoke-test provider. Any claim about production SQL, query plans, collation, or provider-specific behavior still requires validation against the production provider.

An EF Core Specification Can Be a Full Query Envelope

A pure domain specification usually answers one question: does this candidate satisfy a rule? An expression specification exposes provider-visible criteria such as Expression<Func<Customer, bool>>. The model in this article is broader. It is a full query envelope because it can also select a result type, include relationships, select a tracking mode, and choose a query-splitting strategy.

That broader model is useful, but it carries a design cost. Each option affects what EF Core materializes and how it executes the query. An include is not merely another predicate. Tracking is not merely a performance toggle. Projection changes the actual result contract.

If you need a refresher on the underlying query pipeline, the EF Core LINQ querying, filtering, projections, and performance guide provides the broader foundation. Here, the important rule is narrower: decide what the caller needs before deciding how the specification loads it.

Start With the Result Contract, Not the Entity Graph

Suppose a customer list needs:

• The customer ID.
• The customer name.
• The number of open orders.
• The total of the most recent open order.

It does not need mutable Customer and Order entities. It does not need every mapped customer column. It does not need every order row.

Microsoft's efficient querying guidance recommends projecting only the properties that are needed. A DTO projection makes that intent explicit. It also avoids the common habit of adding an Include because related data appears somewhere in the response.

The following complete sample defines both result-shaping paths. CustomerSummaryQuery projects a read model. CustomerGraphShape deliberately loads an entity graph for a workflow that actually needs entities and navigations.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace SpecificationQueryShape;

public enum OrderState
{
    Open,
    Closed,
}

public enum CustomerTracking
{
    TrackChanges,
    NoTracking,
    NoTrackingWithIdentityResolution,
}

public enum RelatedDataQueryMode
{
    SingleQuery,
    SplitQuery,
}

public sealed class Customer
{
    private readonly List<Order> _orders = [];

    private Customer()
    {
    }

    public Customer(long id, string name, bool isActive)
    {
        Id = id;
        Name = name;
        IsActive = isActive;
    }

    public long Id { get; private set; }

    public string Name { get; private set; } = string.Empty;

    public bool IsActive { get; private set; }

    public IReadOnlyCollection<Order> Orders => _orders;

    internal void AddOrder(Order order)
    {
        _orders.Add(order);
    }
}

public sealed class Order
{
    private Order()
    {
    }

    public Order(
        long id,
        long customerId,
        OrderState state,
        long createdSequence,
        decimal total)
    {
        Id = id;
        CustomerId = customerId;
        State = state;
        CreatedSequence = createdSequence;
        Total = total;
    }

    public long Id { get; private set; }

    public long CustomerId { get; private set; }

    public OrderState State { get; private set; }

    public long CreatedSequence { get; private set; }

    public decimal Total { get; private set; }
}

public sealed record CustomerSummary(
    long Id,
    string Name,
    int OpenOrderCount,
    decimal? LatestOpenOrderTotal);

public sealed class SalesDbContext(DbContextOptions<SalesDbContext> options)
    : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();

    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var customer = modelBuilder.Entity<Customer>();
        customer.HasKey(item => item.Id);
        customer.Property(item => item.Name).HasMaxLength(200);
        customer
            .HasMany(item => item.Orders)
            .WithOne()
            .HasForeignKey(item => item.CustomerId);
        customer
            .Navigation(item => item.Orders)
            .UsePropertyAccessMode(PropertyAccessMode.Field);

        var order = modelBuilder.Entity<Order>();
        order.HasKey(item => item.Id);
        order.Property(item => item.Total).HasPrecision(18, 2);
    }
}

public static class CustomerSummaryQuery
{
    public static IQueryable<CustomerSummary> Create(
        IQueryable<Customer> customers)
    {
        return customers
            .Where(customer => customer.IsActive)
            .Select(customer => new CustomerSummary(
                customer.Id,
                customer.Name,
                customer.Orders.Count(order => order.State == OrderState.Open),
                customer.Orders
                    .Where(order => order.State == OrderState.Open)
                    .OrderByDescending(order => order.CreatedSequence)
                    .ThenByDescending(order => order.Id)
                    .Select(order => (decimal?)order.Total)
                    .FirstOrDefault()));
    }
}

public sealed record CustomerGraphShape(
    CustomerTracking Tracking,
    RelatedDataQueryMode QueryMode)
{
    public IQueryable<Customer> Apply(IQueryable<Customer> customers)
    {
        IQueryable<Customer> query = customers
            .Where(customer => customer.IsActive)
            .Include(customer => customer.Orders
                .Where(order => order.State == OrderState.Open)
                .OrderByDescending(order => order.CreatedSequence)
                .ThenByDescending(order => order.Id)
                .Take(5));

        query = Tracking switch
        {
            CustomerTracking.TrackChanges => query.AsTracking(),
            CustomerTracking.NoTracking => query.AsNoTracking(),
            CustomerTracking.NoTrackingWithIdentityResolution =>
                query.AsNoTrackingWithIdentityResolution(),
            _ => throw new ArgumentOutOfRangeException(
                nameof(Tracking),
                Tracking,
                "Unsupported tracking mode."),
        };

        return QueryMode switch
        {
            RelatedDataQueryMode.SingleQuery => query.AsSingleQuery(),
            RelatedDataQueryMode.SplitQuery => query.AsSplitQuery(),
            _ => throw new ArgumentOutOfRangeException(
                nameof(QueryMode),
                QueryMode,
                "Unsupported related-data query mode."),
        };
    }
}

These classes can live behind a specification interface or be the concrete specification types themselves. The important part is that the shape is inspectable. A reviewer can see the projection, filtered include, tracking choice, and split-query choice without executing hidden callbacks.

Both latest-order selection and the filtered Take(5) use descending order ID as a tie-breaker when two orders have the same creation sequence. The relational tests seed duplicate sequence values and assert the selected latest total and included subset.

DTO Projection Is Usually the Clearest Read Shape

The projection above does not call Include. EF Core can translate supported navigation use inside a projection while selecting only the requested result columns; the local SQLite test executes this sample's navigation-based count and latest-order subquery. The database returns the selected result columns rather than materializing a customer plus all matching order entities.

That is the key difference between projection and eager loading:

• Projection defines a new result contract.
• Include populates navigations on returned entity instances.

If the caller needs a list item, export row, dashboard tile, or API response, a positional record is often a better contract than an entity graph. The LINQ projection guide goes deeper on Select and result transformation.

There is one tracking detail worth making explicit. EF Core can track entity instances that appear inside a custom projection. A DTO containing only scalar values and other non-entity DTOs has no entity instance for the context to track. A DTO such as new CustomerResult(customer, count) is different because the Customer entity is still part of the result.

Projection has tradeoffs. It is purpose-specific, and an update workflow cannot attach meaning to a DTO automatically. Repeating similar projection expressions can also become noisy. Those are real costs. They do not justify returning a broad graph by default, though. They justify naming and reusing the read model that the use case actually needs.

Over-Fetching Starts With an Unclear Contract

Over-fetching is often described as a database performance problem, but the first defect is usually a vague result contract. If a method returns Customer, the caller cannot tell whether Orders is populated, partially populated, lazy-loaded, or intentionally absent. A DTO or a specifically named graph shape removes that ambiguity.

There are several result-shape forms of over-fetching:

• Column over-fetching: Materializing a full entity when the caller needs a few scalar values.
• Row over-fetching: Loading every related row when the workflow needs a filtered subset.
• Graph over-fetching: Including relationships that the caller never reads.
• Behavior over-fetching: Tracking entities for a workflow that will not update them.
• Command over-fetching: Choosing split execution for a graph that does not justify additional commands.

These categories do not prove that a query is slow. They provide review questions. Generated SQL, command logs, representative data, and production-provider plans are still required before making a performance conclusion.

Projection has the strongest contract because its result type lists the returned values. A filtered include has a weaker contract because the returned entity still exposes a navigation that looks complete even when only part of it was loaded. EF Core marks a filtered included navigation as loaded, so later explicit or lazy loading is not a neutral way to "finish" that collection. If a partial collection is central to the use case, consider representing it in a dedicated read model instead of presenting it as the entity's ordinary navigation.

There are valid reasons to return a graph. An application service may need to evaluate behavior across an aggregate and persist changes. A command handler may need tracked identity resolution. A read screen may need an entity because downstream code is already built around entity methods. The point is not to ban those choices. The point is to make the choice visible and avoid charging every caller for the richest possible shape.

One useful naming convention is to describe intent rather than mechanism. CustomerSummaryQuery communicates a read model. CustomerForOrderReviewGraph would communicate a workflow. CustomerWithEverythingSpecification communicates neither a bounded contract nor a reason for the graph.

Use Includes When the Caller Needs an Entity Graph

Include and ThenInclude are appropriate when the result is an entity and its related navigation data must be populated. Microsoft's eager-loading documentation defines this behavior directly.

The sample's CustomerGraphShape uses a filtered include. EF Core supports Where, ordering, Skip, and Take on an included collection navigation. The filter is not a general result filter. It controls which related Order entities populate each returned customer's Orders navigation.

That distinction prevents a subtle mistake:

using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace SpecificationQueryShape;

public static class CustomerGraphQueries
{
    public static Task<List<Customer>> LoadAsync(
        SalesDbContext dbContext,
        CancellationToken cancellationToken)
    {
        var query = new CustomerGraphShape(
            CustomerTracking.NoTracking,
            RelatedDataQueryMode.SingleQuery)
            .Apply(dbContext.Customers);

        return query.ToListAsync(cancellationToken);
    }
}

This query returns active customers even if they have no open orders. Their filtered Orders navigation is empty. If the requirement were "return only customers with open orders," that would require an outer Where criterion. This article intentionally does not add dynamic criteria because loading shape and result filtering are separate responsibilities.

Filtered includes should also remain deliberate. Hiding a large include graph inside a generic "customer specification" makes it difficult to know which screens pay for the graph and which ones do not. A shape named for the workflow is easier to review.

Navigation Fix-Up Can Change a Filtered Include Result

Tracking queries have a specific filtered-include caveat. EF Core performs navigation fix-up between query results and entities already held by the same change tracker. Microsoft's eager-loading documentation warns that previously tracked related entities can appear in a filtered navigation even when they do not satisfy the include filter.

Imagine that a context first loads all orders for customer 42. A later tracking query includes only that customer's five open orders. Navigation fix-up can reconnect previously tracked closed orders to the customer's Orders collection. The SQL filter may be correct while the in-memory navigation contains more entities than expected.

Practical options are:

• Use a fresh DbContext for the operation.
• Use AsNoTracking when identity reuse is not required.
• Use AsNoTrackingWithIdentityResolution when the result needs identity resolution without adding entities to the context change tracker.

This is not evidence that tracking is wrong. It is evidence that a specification must declare whether existing context state is allowed to influence the materialized graph.

Choose the Tracking Mode for the Workflow

EF Core entity queries track by default. The tracking documentation explains that tracking queries reuse an already tracked instance when possible and persist detected changes during SaveChanges.

The three choices in the sample serve different purposes:

Track changes

Use AsTracking when the workflow loads entities, modifies them, and saves through the same context. Identity resolution comes from the context change tracker. Existing tracked state and navigation fix-up are part of the behavior.

No tracking

Use AsNoTracking for a read-only entity result when separate occurrences do not need to resolve to the same object instance. The result is not added to the context change tracker.

No tracking with identity resolution

Use AsNoTrackingWithIdentityResolution when a read-only result can repeat the same entity through joins or graph materialization and the object graph benefits from one instance per key. EF Core uses a stand-alone change tracker for materialization, then discards it. The entities are not tracked by the context afterward.

There is no responsible universal claim that no-tracking is always faster. Tracking can reuse existing instances, while no-tracking avoids context tracking work. Measure the actual query shape if the difference matters. The tracking choice should first be correct for the workflow.

For broader EF concepts outside this result-shaping focus, see the complete EF Core guide.

Single and Split Queries Solve Different Problems

When an entity query includes related collections, EF Core can execute one SQL query or split the load into multiple SQL queries. The single versus split query documentation describes the tradeoff.

A single query keeps the operation in one database command, but sibling collection joins can create a cross product. Large principal columns can also be duplicated across rows. A split query avoids that join cross product by issuing additional commands for included collections.

Split queries introduce additional database round trips, may buffer intermediate results, and can observe inconsistent data between commands unless the application uses an appropriate transaction and isolation level. They are not automatically superior. Single queries are not automatically superior either. Their joined row shape can be much larger than the object graph suggests.

The sample makes the mode explicit with AsSingleQuery or AsSplitQuery. That has two benefits:

1. Reviewers do not have to infer a global default.
2. Relational tests can assert the command count for this exact include graph.

If a specification includes two sibling collections, the tradeoff becomes more important. That broader diagnosis belongs in a performance investigation with representative data. The local decision remains simple: select a mode for a known result shape, inspect the generated SQL and command count, and validate it on the production provider.

Prove the Generated SQL and Command Count

ToQueryString() is useful debugging output, but it does not execute the query. Command interception proves how many reader commands EF Core actually sent for the tested provider. The following tests execute all three shapes against SQLite.

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Xunit;

namespace SpecificationQueryShape;

public sealed class ReaderCommandCounter : DbCommandInterceptor
{
    public int Count { get; private set; }

    public void Reset()
    {
        Count = 0;
    }

    public override ValueTask<InterceptionResult<DbDataReader>>
        ReaderExecutingAsync(
            DbCommand command,
            CommandEventData eventData,
            InterceptionResult<DbDataReader> result,
            CancellationToken cancellationToken = default)
    {
        Count++;
        return ValueTask.FromResult(result);
    }
}

public sealed class RelationalQueryShapeTests
{
    [Fact]
    public async Task Projection_ExecutedOnSqlite_UsesOneReaderCommand()
    {
        await using var database = await TestDatabase.CreateAsync();
        await using var context = database.CreateContext();
        var query = CustomerSummaryQuery.Create(context.Customers);
        var generatedSql = query.ToQueryString();

        database.Counter.Reset();
        var rows = await query.ToListAsync(CancellationToken.None);

        Assert.False(string.IsNullOrWhiteSpace(generatedSql));
        Assert.Single(rows);
        Assert.Equal(6, rows[0].OpenOrderCount);
        Assert.Equal(300m, rows[0].LatestOpenOrderTotal);
        Assert.Equal(1, database.Counter.Count);
    }

    [Fact]
    public async Task SingleQuery_OneIncludedCollection_UsesOneReaderCommand()
    {
        await using var database = await TestDatabase.CreateAsync();
        await using var context = database.CreateContext();
        var shape = new CustomerGraphShape(
            CustomerTracking.NoTracking,
            RelatedDataQueryMode.SingleQuery);
        var query = shape.Apply(context.Customers);
        var generatedSql = query.ToQueryString();

        database.Counter.Reset();
        var customers = await query.ToListAsync(CancellationToken.None);

        Assert.False(string.IsNullOrWhiteSpace(generatedSql));
        Assert.Single(customers);
        Assert.Equal(5, customers[0].Orders.Count);
        Assert.Equal(
            [13L, 11L, 10L, 14L, 16L],
            customers[0].Orders.Select(order => order.Id));
        Assert.Equal(1, database.Counter.Count);
    }

    [Fact]
    public async Task SplitQuery_OneIncludedCollection_UsesTwoReaderCommands()
    {
        await using var database = await TestDatabase.CreateAsync();
        await using var context = database.CreateContext();
        var shape = new CustomerGraphShape(
            CustomerTracking.NoTrackingWithIdentityResolution,
            RelatedDataQueryMode.SplitQuery);
        var query = shape.Apply(context.Customers);
        var generatedSql = query.ToQueryString();

        database.Counter.Reset();
        var customers = await query.ToListAsync(CancellationToken.None);

        Assert.False(string.IsNullOrWhiteSpace(generatedSql));
        Assert.Single(customers);
        Assert.Equal(5, customers[0].Orders.Count);
        Assert.Equal(
            [13L, 11L, 10L, 14L, 16L],
            customers[0].Orders.Select(order => order.Id));
        Assert.Empty(context.ChangeTracker.Entries());
        Assert.Equal(2, database.Counter.Count);
    }
}

internal sealed class TestDatabase : IAsyncDisposable
{
    private readonly SqliteConnection _connection;
    private readonly DbContextOptions<SalesDbContext> _options;

    private TestDatabase(
        SqliteConnection connection,
        DbContextOptions<SalesDbContext> options,
        ReaderCommandCounter counter)
    {
        _connection = connection;
        _options = options;
        Counter = counter;
    }

    public ReaderCommandCounter Counter { get; }

    public static async Task<TestDatabase> CreateAsync()
    {
        var connection = new SqliteConnection("Data Source=:memory:");
        await connection.OpenAsync();

        var counter = new ReaderCommandCounter();
        var options = new DbContextOptionsBuilder<SalesDbContext>()
            .UseSqlite(connection)
            .AddInterceptors(counter)
            .Options;

        await using var context = new SalesDbContext(options);
        await context.Database.EnsureCreatedAsync();

        var customer = new Customer(1, "Ada Hardware", true);
        customer.AddOrder(new Order(
            10,
            customer.Id,
            OrderState.Open,
            100,
            125m));
        customer.AddOrder(new Order(
            11,
            customer.Id,
            OrderState.Open,
            101,
            250m));
        customer.AddOrder(new Order(
            13,
            customer.Id,
            OrderState.Open,
            101,
            300m));
        customer.AddOrder(new Order(
            14,
            customer.Id,
            OrderState.Open,
            99,
            150m));
        customer.AddOrder(new Order(
            15,
            customer.Id,
            OrderState.Open,
            98,
            100m));
        customer.AddOrder(new Order(
            16,
            customer.Id,
            OrderState.Open,
            98,
            175m));
        customer.AddOrder(new Order(
            12,
            customer.Id,
            OrderState.Closed,
            90,
            500m));

        context.Customers.Add(customer);
        await context.SaveChangesAsync();
        counter.Reset();

        return new TestDatabase(connection, options, counter);
    }

    public SalesDbContext CreateContext()
    {
        return new SalesDbContext(_options);
    }

    public async ValueTask DisposeAsync()
    {
        await _connection.DisposeAsync();
    }
}

These assertions are evidence for this SQLite model and this include graph. They are not promises about every provider or every graph. Microsoft's provider guidance states that providers have their own compatibility and behavior. Microsoft's testing strategy guidance recommends meaningful testing against the production database system.

Before shipping, run the same critical queries through the production provider. Capture generated SQL or command logs. Execute them with representative data. If performance matters, inspect the actual database plan rather than treating ToQueryString() as a benchmark. Microsoft's performance diagnosis guidance explicitly starts with measurement and command evidence.

A Practical Decision Sequence

Use this sequence when an EF Core specification needs to own result shape:

1. Write the caller's result contract.
2. Prefer a scalar or DTO projection when entities are not required.
3. Add Include only when returned entity navigations must be populated.
4. Filter an included collection only when partial navigation loading matches the workflow.
5. Select tracking, no-tracking, or identity-resolving no-tracking deliberately.
6. Choose single or split execution for the known include graph.
7. Inspect generated SQL and execute a relational test.
8. Re-run critical proof against the production provider.

The benefit is not an abstract promise of speed. The benefit is a visible query envelope with fewer accidental choices. The cost is that the specification now owns persistence-specific result behavior and must be tested at that level.

Frequently Asked Questions

Does a DTO projection need Include?

Usually not. A projection can navigate relationships inside Select, and EF Core translates supported expressions into the projected query. Include is intended to populate navigations on returned entities. Execute the projection against the target provider to verify translation.

Can a projected query still track entities?

Yes. If the projection contains entity instances, EF Core can track those instances. A DTO composed only of scalar values and non-entity DTOs does not contain an entity for the context to track.

Is AsNoTracking always the fastest choice?

No universal claim is justified. No-tracking avoids context tracking work, while tracking can reuse an entity already held by the context. Choose the correct semantics first, then measure the exact query if the difference matters.

Why can a filtered Include return unexpected related entities?

In a tracking query, navigation fix-up can reconnect related entities that were loaded earlier into the same context. Use a fresh context or an appropriate no-tracking mode when the filtered navigation must reflect only the current query.

Should every collection Include use AsSplitQuery?

No. Split queries can avoid a single-query cross product, but they add commands, round trips, buffering considerations, and possible consistency differences. Make the choice explicit and prove it for the actual graph.

Shape the Result Deliberately

An EF Core specification for includes, projection, and tracking should make the result contract obvious. Use DTO projection for purpose-built reads. Use includes for workflows that genuinely need entity navigations. Declare tracking and query splitting rather than relying on ambient defaults.

Then prove what happens. Inspect the generated SQL, execute the query through a relational provider, count the commands, and repeat critical validation with the production provider. That process does not guarantee a query is optimal, but it does replace hidden loading behavior with evidence.