Explicit Loading and Targeted Relationship Queries for Conditional Workflows

Learning outcomes

A dispatcher list usually needs only work-order summary rows. Notes are needed only after an operator opens a detail panel; a count may be needed without any note entities. Explicit loading lets the application make that decision visible. The danger is moving the query into a loop and accidentally creating N+1 round trips. This lesson distinguishes LoadAsync from Query() and shows set-based repairs.

1. Explicit loading means the code names the I/O point

With explicit loading, the principal is already tracked and application code later asks EF to load a specific navigation. That is different from lazy loading, where property access triggers the query implicitly. Explicit loading can make conditional workflows readable because the database call is visible and awaitable.

var workOrder = await db.WorkOrders    .SingleAsync(w => w.Id == id, ct);await db.Entry(workOrder)    .Reference(w => w.AssignedTechnician)    .LoadAsync(ct);await db.Entry(workOrder)    .Collection(w => w.Notes)    .LoadAsync(ct);

Each LoadAsync can execute another query. The context remains the same unit of work, so newly materialized related entities are tracked and fixup populates both ends of configured relationships.

2. Query() lets the database answer without populating everything

var workOrder = await db.WorkOrders.SingleAsync(w => w.Id == id, ct);var noteCount = await db.Entry(workOrder)    .Collection(w => w.Notes)    .Query()    .CountAsync(ct);var newestNotes = await db.Entry(workOrder)    .Collection(w => w.Notes)    .Query()    .OrderByDescending(n => n.Id)    .Take(3)    .AsNoTracking()    .Select(n => new { n.Id, n.Text })    .ToListAsync(ct);

CountAsync translates to a database aggregate; it does not materialize every note merely to count them. Query() is therefore a useful bridge from relationship metadata back to normal composable LINQ.

SELECT COUNT(*)FROM work_order_notes AS nWHERE n.work_order_id = @__id_0;

3. IsLoaded and fixup are state, not just query text

Calling LoadAsync marks the navigation loaded. Querying related entities through Query() may materialize matching entities and fix them into a tracked graph, but a partial filtered query does not magically mean “the full navigation is complete.” Inspect the entry state when correctness depends on it.

var notesEntry = db.Entry(workOrder).Collection(w => w.Notes);Console.WriteLine($"Before: {notesEntry.IsLoaded}");await notesEntry.LoadAsync(ct);Console.WriteLine($"After:  {notesEntry.IsLoaded}");Console.WriteLine(db.ChangeTracker.DebugView.ShortView);

4. Deliberately wrong: explicit loading inside a result loop

var workOrders = await db.WorkOrders    .OrderBy(w => w.Id)    .Take(100)    .ToListAsync(ct);         // 1 commandforeach (var workOrder in workOrders){    await db.Entry(workOrder)        .Collection(w => w.Notes)        .LoadAsync(ct);       // up to 100 more commands}

The loop is easy to read and expensive on a remote database: one root query plus one child query per principal. This is the classic N+1 pattern. The repair depends on what the caller actually needs.

var rows = await db.WorkOrders    .OrderBy(w => w.Id)    .Take(100)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        NoteCount = w.Notes.Count    })    .AsNoTracking()    .ToListAsync(ct);

var ids = workOrders.Select(w => w.Id).ToArray();await db.Set<WorkOrderNote>()    .Where(n => ids.Contains(n.WorkOrderId))    .LoadAsync(ct); // fixup distributes notes to tracked WorkOrders

Repair B intentionally relies on tracking/fixup and should be verified against EF Core 10's parameterized-collection translation plus provider parameter limits for very large key sets.

5. Connection lifetime is shorter than context lifetime

A DbContext usually opens a database connection when a command needs it and returns/closes it afterward; keeping a context alive does not imply one continuously open physical connection. Explicit loading adds commands and therefore connection use/round trips. Driver-level connection pooling is separate from DbContext pooling and separate from related-data loading strategy.

6. Conditional workflow example

var workOrder = await db.WorkOrders    .SingleAsync(w => w.Id == id, ct);if (request.IncludeInternalNotes && authorization.CanViewInternalNotes){    await db.Entry(workOrder)        .Collection(w => w.Notes)        .Query()        .OrderByDescending(n => n.Id)        .Take(20)        .LoadAsync(ct);}

Loading is not authorization. The authorization decision must exist independently; query filters/Includes do not replace access control. Also note that partially loading a tracked collection and then serializing the entity can mislead consumers about completeness. Prefer a DTO with an explicit InternalNotes field when crossing an API boundary.

7. Hands-on lab: measure command count, not intuition

1. Seed 30 work orders with deterministic note counts.
2. Load the 30 roots and explicitly load Notes in a loop; capture command logs and count them.
3. Replace the loop with a projection containing Notes.Count; verify the aggregate stays server-side.
4. Repeat with the set-based ids.Contains(...) tracked-load repair and inspect navigation fixup.
5. Record IsLoaded before/after full LoadAsync.
6. Cancel a long-running/repeated operation with a CancellationToken and verify cancellation is awaited before reusing the context.

8. Production judgment and bridge

Explicit loading is appropriate when the condition for fetching related data is known only after the principal is loaded and the extra command is acceptable. Prefer Query() for aggregates and bounded subsets. Never hide a loop of relationship loads behind a “clean” method without command-count tests. Lesson 5 examines lazy loading, where that same extra command can be triggered by ordinary property access and become even harder to see.

Authoritative references

• Explicit Loading of Related Data - EF Core — LoadAsync and navigation Query() examples.
• Loading Related Data - EF Core — eager, explicit, and lazy loading definitions.
• Changing Foreign Keys and Navigations - EF Core — navigation fixup.
• Efficient Querying - EF Core — round trips, N+1, query efficiency.
• DbContext Lifetime, Configuration, and Initialization — unit-of-work and async discipline.