Include and ThenInclude: Loading Graphs Without Losing Sight of Result Cardinality

Learning outcomes

A ServiceHub API now needs a work-order detail graph: assigned technician, SLA snapshot, tags, notes, and attachments. The tempting answer is “add Include until everything is present.” That can return the correct object graph while transferring far more relational rows than expected. This lesson treats eager loading as a query-shape decision: decide the graph, inspect the SQL, predict row cardinality, then choose Include/ThenInclude or projection deliberately.

1. Loading strategy is part of the data contract

Eager loading asks EF to load related entities as part of the query. A reference navigation points to zero/one related entity; a collection navigation represents many. Include selects the first navigation edge; ThenInclude continues from an already included edge. These operators do not mean “one SQL statement forever”—split queries can execute eager loading as multiple statements—but they do declare what related entity graph EF should assemble.

2. Deliberate Chapter 10 navigation evolution: expose existing collections

Chapter 05 already introduced work_order_notes and work_order_attachments as required dependents, but used unidirectional WithMany() mappings. Chapter 10 adds principal-side collections so loading behavior can be studied. The foreign keys and tables do not change; only the navigability of the EF model changes.

public sealed partial class WorkOrder{    private readonly List<WorkOrderNote> _notes = new();    private readonly List<WorkOrderAttachment> _attachments = new();    public IReadOnlyCollection<WorkOrderNote> Notes => _notes;    public IReadOnlyCollection<WorkOrderAttachment> Attachments => _attachments;}modelBuilder.Entity<WorkOrderNote>()    .HasOne(x => x.WorkOrder)    .WithMany(x => x.Notes)    .HasForeignKey(x => x.WorkOrderId)    .IsRequired();modelBuilder.Entity<WorkOrderAttachment>()    .HasOne(x => x.WorkOrder)    .WithMany(x => x.Attachments)    .HasForeignKey("WorkOrderId")    .IsRequired();

3. Include a reference and a collection

var query = db.WorkOrders    .Where(w => w.WorkOrderNumber == requestedNumber)    .Include(w => w.AssignedTechnician)    .Include(w => w.WorkOrderTags)        .ThenInclude(link => link.Tag)    .TagWith("Chapter10/Lesson1 detail graph");Console.WriteLine(query.ToQueryString());var workOrder = await query.SingleAsync(ct);

For a relational provider, the SQL normally contains left joins for optional/reference edges and joins for collection paths. The exact aliases and selected columns are provider/version details; the relational mechanism is not. A work order with four tag links can appear in four database rows even though the tracking result is one WorkOrder instance with four WorkOrderTag instances.

SELECT w..., t..., wt..., tag...FROM work_orders AS wLEFT JOIN technicians AS t  ON w.assigned_technician_id = t.idLEFT JOIN work_order_tags AS wt  ON w.work_order_id = wt.work_order_idLEFT JOIN tags AS tag  ON wt.tag_id = tag.idWHERE w.work_order_number = @__requestedNumber_0;

4. Identity resolution does not cancel transferred rows

Tracking queries perform identity resolution: if the same primary key occurs repeatedly in the result, the context reuses one tracked entity instance. That protects graph identity, but it happens after the database produced and transferred rows. If one work order has 5 tags, 4 notes, and 3 attachments, three sibling collection joins can yield up to 5 × 4 × 3 = 60 relational rows for that principal. EF may return one work-order object, but the database/network/materializer still handled the multiplied row set.

var graph = await db.WorkOrders    .Include(w => w.WorkOrderTags)    .Include(w => w.Notes)    .Include(w => w.Attachments)    .SingleAsync(w => w.Id == id, ct);Console.WriteLine($"Tags={graph.WorkOrderTags.Count}");Console.WriteLine($"Notes={graph.Notes.Count}");Console.WriteLine($"Attachments={graph.Attachments.Count}");Console.WriteLine(db.ChangeTracker.DebugView.ShortView);

The tracker view proves which entity instances EF is tracking. It does not reveal bytes transferred or the raw joined-row count; use command logging/database tools for that evidence.

5. Multiple paths and derived navigations

When two include paths share a starting navigation, repeat the root include and continue with different ThenInclude chains. EF combines what it can; do not infer the SQL shape from the number of Include calls alone. For an inheritance hierarchy, a derived navigation can be included after narrowing with OfType<TDerived> or by using a cast-supported include form when the model actually contains that navigation.

// Chapter 07's hierarchy remains isolated in ServiceHub.MappingLab.// If EquipmentTarget owns a mapped collection such as CalibrationRecords:var equipment = await mappingDb.Set<ServiceTarget>()    .OfType<EquipmentTarget>()    .Include(e => e.CalibrationRecords)    .AsNoTracking()    .ToListAsync(ct);

6. Deliberately wrong: eager-load the domain instead of the use case

var rows = await db.WorkOrders    .Include(w => w.AssignedTechnician)    .Include(w => w.SlaSnapshot)    .Include(w => w.WorkOrderTags).ThenInclude(x => x.Tag)    .Include(w => w.Notes)    .Include(w => w.Attachments)    .ToListAsync(ct);

This may be semantically correct and still be a bad endpoint query: wide principal columns are duplicated across collection rows, multiple collections can multiply rows, tracking allocates state for every entity, and serialization can expose bidirectional cycles or internal fields. The repair is to define the response contract first.

var dto = await db.WorkOrders    .Where(w => w.Id == id)    .Select(w => new WorkOrderDetailDto(        w.Id,        w.WorkOrderNumber,        w.CustomerName,        w.AssignedTechnician == null ? null : w.AssignedTechnician.DisplayName,        w.WorkOrderTags            .OrderBy(x => x.DisplayOrder)            .Select(x => x.Tag.Name)            .ToArray(),        w.Notes.Count,        w.Attachments.Count))    .SingleAsync(ct);

Projection is not automatically faster; it is simply more explicit about required columns and shape. Inspect the translated SQL and measure the actual workload.

7. Hands-on lab: count relational work before object results

1. Reset the disposable SQLite lab and seed one work order with deterministic counts: 5 tags, 4 notes, 3 attachments.
2. Add the principal-side navigations and verify the navigation-only migration is empty.
3. Capture ToQueryString() for reference-only, one-collection, nested-collection, and three-sibling-collection shapes.
4. Enable Microsoft.EntityFrameworkCore.Database.Command logging and record command count.
5. Materialize the graph and inspect ChangeTracker.DebugView.ShortView.
6. Replace the graph with a DTO projection and compare selected columns/rows; do not invent timing numbers.

8. Production judgment and bridge

Use eager loading when the unit of work genuinely needs the related entities together and the resulting cardinality is bounded. Prefer projection for API/read models. Review generated SQL, command count, tracking mode, and serialization shape before calling a query “simple.” Lesson 2 adds filters inside Include and demonstrates why the same query can produce a surprising collection when the context already tracks related entities.

Authoritative references

• Eager Loading of Related Data - EF Core — Include/ThenInclude, multiple paths, derived types, filtered Include.
• Single vs. Split Queries - EF Core — JOIN duplication and cartesian explosion.
• Tracking vs. No-Tracking Queries - EF Core — identity resolution and tracking behavior.
• Relationships - EF Core — navigation and foreign-key semantics.
• EF Core logging and diagnostics — command/log evidence.