.Net Learning Notes: Custom In-Memory Provider(5) - Include & ThenInclude — Navigation Loading and Fix-Up

In EF Core, Include and ThenInclude are not just “query operators that add extra columns.” They are a loading contract: “when the outer entities are materialized, also load the related entities and make the relationship consistent in the tracked graph.” Relational providers often implement this by producing SQL that happens to look like LEFT JOIN, but the real goal is not the join itself. The real goal is: load related rows and run relationship fix-up so that navigation properties on both sides point to the correct tracked instances.

This is why Include feels deceptively simple at the API level but becomes difficult at provider level. It sits right at the boundary between query execution, entity materialization, tracking/identity resolution, and navigation fix-up. If you get any of those wrong, you either populate navigations with duplicate instances, break EF Core’s identity map guarantees, or end up with “loaded but not marked loaded” behaviors that cause EF Core to issue unexpected additional loads later.

Reference Include vs Collection Include vs ThenInclude

A reference include populates a single navigation property such as Order.Customer. Conceptually it is “1:0..1” or “many-to-one”: each outer entity points to at most one related entity. The fix-up is straightforward: once both entities are materialized and tracked, the outer reference can be assigned, and the inverse navigation (if present) can also be set.

A collection include populates a collection navigation such as Customer.Orders. This is “1:N”. The key difference is that collection include is not just “attach one inner row.” It is “attach potentially many related rows,” and that immediately introduces grouping semantics. A join-shaped result naturally duplicates outer rows; EF Core must prevent that duplication from turning into duplicated outer instances and duplicated navigation items. This is why collection includes are harder: the provider must either group inner rows per outer key or build a correlated subquery per outer row, and then apply fix-up consistently.

ThenInclude is not a third separate mechanic; it is simply “Include on an entity that is itself included.” In a reference chain, it looks like Blog -> Owner -> Address. In a collection chain, it looks like Blog -> Posts -> Comments. The difficulty is that it composes include paths and requires the provider to apply fix-up in the correct sequence across multiple relationship edges, while still respecting identity resolution.

How EF Core Represents Include Internally

At the surface level, Include looks simple. You write:

context.Customers.Include(c => c.Orders)

and expect the related entities to be loaded. But internally, EF Core does not treat Include as a normal LINQ operator like Where or Select. So internally, Include lives inside the shaping layer, not the filtering layer.

reference navigation

For a reference navigation such as:

context.Orders.Include(o => o.Customer)

EF Core translates the include into a LEFT JOIN during the translation phase. Internally, the query is rewritten so that the related entity is brought into scope through a join operation. The translation visitor typically calls into TranslateLeftJoin, producing a join expression that temporarily carries both the outer and inner elements.

Conceptually, the transformation looks like this:

Orders
    .LeftJoin(
        Customers,
        o => o.CustomerId,
        c => c.Id,
        (o, c) => new TransparentIdentifier<Order, Customer>(o, c)
    )

The TransparentIdentifier<Outer, Inner> is an internal carrier that keeps both sides of the join available for the next projection step.

After the join, EF Core applies a Select projection that restores the final result shape to the outer entity type. At this stage, the query element type becomes Order again, not a pair of (Order, Customer). The join is used only to retrieve the related row.

The important part is how Include itself is represented. It is not treated as a normal executable LINQ operator. Instead, EF Core inserts an IncludeExpression (or equivalent marker) into the ShaperExpression, not the main query expression. The shaper is responsible for materialization. During execution, the shaper uses the joined inner entity to populate the navigation:

order.Customer = customer;

If tracking is enabled, EF Core’s identity resolution and relationship fix-up mechanisms are triggered automatically.

This is the essential pattern for reference include: LEFT JOIN to fetch the related entity, projection back to the outer type, and navigation assignment handled during shaping and tracking.

collection navigation & ThenInclude

In this provider, collection navigation and ThenInclude are implemented using the same underlying rewriting mechanism. Instead of relying on EF Core’s relational join-based pipeline, the provider intercepts IncludeExpression during the compilation stage and replaces it with executable logic that performs a correlated subquery and explicit navigation fix-up.

For collection navigation (for example, Blog.Include(b => b.Posts)), EF Core represents the include inside the shaper as an IncludeExpression. When the navigation is a collection, the NavigationExpression is typically a MaterializeCollectionNavigationExpression, which contains a subquery describing how to load the related entities. This subquery is not directly executable and cannot be left in the final expression tree. It must be transformed.

During compilation, the provider scans Queryable.Select calls and detects whether the selector body contains an IncludeExpression. If found, it rewrites the selector. For collection includes, the subquery stored in MaterializeCollectionNavigationExpression. Subquery is first rewritten into a provider-executable query expression. This step ensures that any ShapedQueryExpression, query roots, or provider-specific expressions are converted into an expression that returns an IEnumerable<TElement>.

Instead of embedding the subquery expression directly into the final tree, the provider compiles it into a delegate of type Func<QueryContext, TOuter, IEnumerable<TElement>>. At runtime, this delegate is invoked to execute the correlated subquery for each outer entity instance. The result is materialized as a list.

The original IncludeExpression is then replaced with a call to a helper method such as LoadCollection. This method receives the current entity instance, the navigation metadata, and the materialized related entities. It retrieves or creates the navigation collection on the entity, clears any existing contents, adds the loaded elements, and optionally sets IsLoaded = true on the navigation entry. In this way, the navigation is populated explicitly during materialization.

ThenInclude does not require a separate mechanism. It works naturally through recursion. When an include subquery itself contains further includes, the same rewriting logic is applied to the nested query during compilation. Because subqueries are rewritten into executable delegates before being invoked, any nested IncludeExpression encountered inside them is also transformed. In effect, ThenInclude is just another include applied within a deeper subquery scope, and the same rewrite-and-fix-up process is reused.

This approach avoids join-based row duplication and does not depend on TransparentIdentifier shapes. Instead of flattening data through joins and reconstructing object graphs from duplicated rows, the provider executes a correlated subquery per outer entity and performs fix-up directly on entity instances. The final query shape remains a flat sequence of outer entities, with collection navigations populated after materialization.

The main limitation of this strategy is that it relies on LINQ-to-Objects execution of subqueries and explicit fix-up logic, rather than fully integrating with EF Core’s internal include pipeline. To implement include in a fully “native” EF Core way, the provider would need to participate in navigation expansion, entity key resolution factories, include metadata binding, and shaper-based fix-up integration. That would require significantly more work and much deeper alignment with EF Core’s internal query pipeline.

Why Include/ThenInclude Is Difficult for a Custom Provider

The core difficulty is that Include spans multiple EF Core subsystems at once.

First, it depends on correct identity resolution. If the included inner entity is materialized as a new object each time it appears, the graph becomes inconsistent immediately.

Second, it depends on navigation metadata and FK matching. For reference include, you need to locate the inner entity by foreign key from the outer entity. For collection include, you need to locate many inner entities by matching their FK to the outer PK. EF Core has metadata for this (keys, foreign keys, navigations, inverse navigations), but providers must wire enough services so that EF can ask for entity finders and key factories consistently.

Finally, ThenInclude multiplies the complexity because the provider must apply include logic repeatedly across the included entities, not only the root query entity, while keeping graph identity stable.

Why LINQ-to-Objects Was the Practical Choice

In your current approach, you treat Include as “metadata in the shaper,” not as something you execute directly. The core pipeline still compiles the main query (QueryRows → TrackFromRow → replay steps → apply terminal). For Include, you extract the navigation names from the shaper before stripping the Include markers, then you run fix-up outside the formal translation/compilation of Include itself.

That is exactly what this part of your code implies:

var includeNavs = ExtractIncludeNavigationNames(shapedQueryExpression.ShaperExpression);
executor = CompileQueryRowsPipeline(q);
// executor = new IncludeStrippingVisitor().Visit(executor)!;
executor = ApplyMarkLoadedWrapper(executor, QueryCompilationContext.QueryContextParameter, includeNavArrayExpr);

The sequence matters. You extract include information while it still exists in the shaper. Then you compile your own executable pipeline that does not rely on EF Core’s IncludeExpression being executable. Then you apply a wrapper to ensure EF Core sees navigations as loaded (and/or you apply your own fix-up step).

Chosing LINQ-to-Objects because it’s the shortest path to a correct demonstration: you can run the main query normally, materialize and track root entities correctly, then for each root entity perform additional in-memory lookups against your IMemoryDatabase tables to populate navigations. This avoids having to fully model EF Core’s internal include shaping contracts while still producing correct final graphs for a demo provider.

What “Formal EF Core Pipeline Include” Would Require

If we wanted to implement Include using EF Core’s formal pipeline rather than rewriting it into LINQ-to-Objects logic, the provider would need to participate much deeper in EF Core’s shaping and materialization infrastructure.

First, the provider would need to fully support EF Core’s IncludeExpression inside the shaper expression. That means not stripping or rewriting it, but allowing it to flow into the shaped query compilation stage. The provider’s IShapedQueryCompilingExpressionVisitor would then be responsible for generating materialization code that cooperates with EF Core’s navigation fix-up system rather than performing manual assignment.

Second, reference include would require proper join translation support. The translation visitor would need to implement TranslateLeftJoin (and possibly other join patterns) so that EF Core can express reference navigations as join-based query shapes. The provider must then handle TransparentIdentifier-style intermediate projections correctly and ensure that both outer and inner entities are materialized in a coordinated way.

Third, collection include would require correlated subquery or grouping support at the query model level. Instead of executing a compiled delegate per outer entity, the provider would need to preserve EF Core’s subquery representation and let the shaped query compiler build the correct collection materialization pipeline. This involves supporting MaterializeCollectionNavigationExpression and ensuring that collection shapers cooperate with tracking and fix-up.

Fourth, the provider would need to integrate fully with EF Core’s tracking hooks. That includes correct usage of key factories, entity finder services, navigation metadata, and tracking behavior flags. Fix-up must occur through the StateManager automatically during materialization rather than through explicit property assignment.

Finally, the provider would need to guarantee that the final expression tree produced by compilation contains only reducible, executable nodes. IncludeExpression and other internal markers must be handled in the shaper phase, not left in the executable pipeline.

In short, a formal pipeline implementation would require full join translation support, collection shaping support, proper handling of EF Core’s internal include expressions, and tight integration with the StateManager’s fix-up system. It is significantly more complex than a LINQ-to-Objects rewriting approach because it requires the provider to implement EF Core’s relational-style shaping semantics rather than delegating navigation loading to post-processing logic.