Filtered Include, Navigation Fixup, Tracking Side Effects, and Predictable Result Shapes

Learning outcomes

A dispatcher asks for a work order with only the three newest notes and only high-priority tag associations. The filtered query looks obvious, but a long-lived tracking context has already loaded older notes. EF's navigation fixup can make the in-memory collection larger than the SQL filter suggests. This lesson makes the mismatch observable and teaches when a fresh context or no-tracking query is the safer read boundary.

1. Filtered Include is eager loading plus collection operators

EF Core supports a constrained set of enumerable operations inside a collection Include: Where, OrderBy, OrderByDescending, ThenBy, ThenByDescending, Skip, and Take. These shape which related rows the eager-loading query asks the database to return. They do not turn a navigation collection into a permanently filtered domain property.

var query = db.WorkOrders    .Where(w => w.Id == id)    .Include(w => w.Notes        .OrderByDescending(n => n.Id)        .Take(3));Console.WriteLine(query.ToQueryString());var workOrder = await query.SingleAsync(ct);

The exact SQL form varies by provider. The contract to verify is that the related-note subquery/order/limit is represented server-side, not that a particular alias name appears.

2. One unique filter set per included navigation

If the same collection is included more than once—for example, because different ThenInclude paths continue from it—EF allows a filter set on one path and an unfiltered copy on the other, or the same filter operations on each repeated path. Two different filter sets over the same included collection are not a safe way to request two differently filtered versions of one navigation property.

var query = db.WorkOrders    .Include(w => w.WorkOrderTags        .Where(x => x.DisplayOrder <= 3)        .OrderBy(x => x.DisplayOrder))        .ThenInclude(x => x.Tag)    .Include(w => w.WorkOrderTags        .Where(x => x.DisplayOrder <= 3)        .OrderBy(x => x.DisplayOrder))        .ThenInclude(x => x.WorkOrder);

3. Navigation fixup is tracker behavior, not SQL leakage

Navigation fixup is EF's process of keeping tracked relationship navigations consistent. When EF tracks a note whose WorkOrderId points to a tracked work order, it can add that note to the work order's Notes collection. Therefore a later filtered Include may execute correctly and still leave extra already-tracked notes in the collection.

// Step 1: preload old notes into this context.var preloaded = await db.Set<WorkOrderNote>()    .Where(n => n.WorkOrderId == id)    .OrderBy(n => n.Id)    .ToListAsync(ct);// Step 2: ask for only the newest 3 in the same tracking context.var workOrder = await db.WorkOrders    .Include(w => w.Notes.OrderByDescending(n => n.Id).Take(3))    .SingleAsync(w => w.Id == id, ct);Console.WriteLine($"Preloaded notes: {preloaded.Count}");Console.WriteLine($"Collection now: {workOrder.Notes.Count}");Console.WriteLine(db.ChangeTracker.DebugView.ShortView);

The important diagnosis is: SQL filtering did not fail. The context already knew about additional related entities and fixup kept the tracked graph relationship-consistent.

4. Filtered Include marks the navigation loaded

In a tracking query, EF considers a filtered-included navigation loaded. That means later explicit or lazy loading will not automatically “fill in the missing rows” simply because the collection contains only a subset. This is intentional: EF cannot infer whether the filter expressed “complete for this workflow” or “temporary sample.”

var entry = db.Entry(workOrder).Collection(w => w.Notes);Console.WriteLine($"Notes IsLoaded = {entry.IsLoaded}");// If the workflow intentionally wants all notes later, make that transition explicit:entry.IsLoaded = false;await entry.LoadAsync(ct);

5. Repair pattern A: a fresh unit of work

The most reliable repair for an endpoint that expects database-only filtered results is a fresh short-lived DbContext. That removes unrelated historical tracker state from the equation.

await using var readDb = await dbFactory.CreateDbContextAsync(ct);var workOrder = await readDb.WorkOrders    .Include(w => w.Notes.OrderByDescending(n => n.Id).Take(3))    .AsNoTracking()    .SingleAsync(w => w.Id == id, ct);Console.WriteLine(workOrder.Notes.Count); // database-filtered shape

AsNoTracking means the result isn't attached to the context change tracker. It does not change database correctness; it changes client-side state management. If the same entity repeats in a read shape and identity matters without tracking, consider AsNoTrackingWithIdentityResolution.

6. Repair pattern B: project the filtered child rows

var result = await db.WorkOrders    .Where(w => w.Id == id)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        RecentNotes = w.Notes            .OrderByDescending(n => n.Id)            .Take(3)            .Select(n => new { n.Id, n.Text })            .ToList()    })    .AsNoTracking()    .SingleAsync(ct);

The property name RecentNotes expresses that this is a subset. That is semantically clearer than returning an entity whose Notes navigation looks complete but is intentionally partial.

7. Deliberately wrong: reuse one context as a read cache

// Anti-pattern: one context survives multiple unrelated screens/requests.await db.Set<WorkOrderNote>().Where(n => n.WorkOrderId == id).ToListAsync(ct);var filtered = await db.WorkOrders    .Include(w => w.Notes.Where(n => n.Id > cutoffId))    .SingleAsync(w => w.Id == id, ct);// Developer assumes every note in filtered.Notes satisfies Id > cutoffId.

This violates Chapter 02's short-unit-of-work discipline and turns tracker history into an implicit input to query results. The safe repair is not “clear random entries until it works”; align context lifetime with the operation, or use an explicit read model.

8. Hands-on lab: prove SQL and graph can tell different stories

1. Seed one work order with six notes.
2. In one context, preload all six notes, then execute a filtered Include for the newest two.
3. Record ToQueryString(), command logs, Notes.Count, and DebugView.
4. Inspect Collection(...).IsLoaded.
5. Repeat in a fresh context with AsNoTracking().
6. Replace the entity graph with a RecentNotes projection and compare semantics.

9. Production judgment and bridge

Filtered Include is useful when a tracked aggregate genuinely needs a bounded subset, but it is easy to misread after prior tracking. Treat context lifetime and tracker contents as part of the query's client-side semantics. Lesson 3 now tackles a different form of “correct but expensive”: multiple collection Includes that are accurate yet create cartesian explosion in one SQL statement.

Authoritative references

• Eager Loading of Related Data - EF Core — filtered Include operators, one-filter-set rule, fixup warning and loaded-state note.
• Tracking vs. No-Tracking Queries - EF Core — tracking and identity resolution.
• Changing Foreign Keys and Navigations - EF Core — relationship fixup mechanics.
• DbContext Lifetime, Configuration, and Initialization — short-lived unit-of-work guidance.
• Explicit Loading of Related Data - EF Core — loaded navigation and explicit query patterns.