EF Core Joins and Includes

EF Core: Joins, Include and Split Queries

QueryForge's EF Core surface is nothing but extension methods on IQueryable<T>, so everything EF Core can build — Join, navigation properties, Include/ThenInclude, AsSplitQuery — composes with it. This page covers how, and the three interactions that are not obvious.

---

The rule everything follows

public static Task<QueryResult<TModel>> ToQueryResultAsync<TModel>(
    this IQueryable<TModel> source, Query query, CancellationToken ct = default);

QueryForge never replaces your query; it appends ApplyFilter → ApplySort → ApplyPaging → ApplyProjection and executes the result. EF Core sees one expression tree and emits one statement.

Because ExpressionCompiler.ResolveProperty<TModel> resolves readable instance properties of TModel, one sentence predicts every behaviour below:

A column is queryable if and only if it is a direct property of TModel.

Which is why the shape you hand to ToQueryResultAsync matters more than anything else on this page.

---

Pattern A — project the join into a flat DTO

The pattern for any endpoint where the client drives the query.

public class OrderRow                          // named type, parameterless ctor, settable properties
{
    public int OrderId { get; set; }
    public string Number { get; set; } = "";
    public string? CustomerName { get; set; }  // from the join
    public string? Tier { get; set; }          // from the join
    public decimal Total { get; set; }
    public DateTime PlacedOn { get; set; }
}

app.MapPost("/api/orders/query", async (Query query, AppDbContext db, ITenant tenant) =>
{
    var rows = db.Orders
        .Where(o => o.TenantId == tenant.Id)        // survives everything the client sends
        .Select(o => new OrderRow
        {
            OrderId      = o.OrderId,
            Number       = o.Number,
            CustomerName = o.Customer.Name,         // EF turns this into a JOIN
            Tier         = o.Customer.Tier,
            Total        = o.Total,
            PlacedOn     = o.PlacedOn
        });

    return await rows.ToQueryResultAsync<OrderRow>(query);
});

TModel is now OrderRow, so CustomerName is a genuine column — filterable, sortable, groupable and projectable:

{ "criteria": { "groups": [ { "conditions": [
      { "columnName": "CustomerName", "operator": 2, "value": "acme" } ] } ] },
  "sortColumns": [ { "columnName": "Tier" } ],
  "groupByColumns": [ { "columnName": "CustomerName" } ] }

All of it is pushed down into the same statement — the LIKE lands on Customers.Name in SQL, not in memory:

SELECT o.OrderId, o.Number, c.Name, c.Tier, o.Total, o.PlacedOn
FROM Orders o INNER JOIN Customers c ON c.Id = o.CustomerId
WHERE o.TenantId = @__tenant AND c.Name LIKE @__p0
ORDER BY (CASE WHEN c.Tier IS NULL THEN 0 ELSE 1 END), c.Tier
OFFSET 0 ROWS FETCH NEXT 12 ROWS ONLY

The CASE is QueryForge's null-rank key, pinning nulls first ascending so every engine agrees. See Query Semantics.

Explicit Join, and why the DTO must be named

var rows = db.Orders.Join(db.Customers,
    o => o.CustomerId,
    c => c.Id,
    (o, c) => new OrderRow                      // ← named, NOT anonymous
    {
        OrderId = o.OrderId, CustomerName = c.Name, Total = o.Total
    });

BuildProjection opens with:

if (selectColumns.Count == 0 || typeof(TModel).GetConstructor(Type.EmptyTypes) is null)
    return null;

An anonymous type has no parameterless constructor, so the projection is skipped and selectColumns is silently ignored. Filtering and sorting still work — they only need readable properties — so it fails quietly in exactly one dimension. Use a named DTO.

---

Left joins

var rows = db.Orders
    .GroupJoin(db.Customers, o => o.CustomerId, c => c.Id, (o, cs) => new { o, cs })
    .SelectMany(x => x.cs.DefaultIfEmpty(), (x, c) => new OrderRow
    {
        OrderId      = x.o.OrderId,
        CustomerName = c != null ? c.Name : null,
        Total        = x.o.Total
    });

EF Core 10's operator does the same more directly:

db.Orders.LeftJoin(db.Customers, o => o.CustomerId, c => c.Id,
    (o, c) => new OrderRow { OrderId = o.OrderId, CustomerName = c.Name, Total = o.Total });

Unmatched rows now carry null, which QueryForge treats as first-class: {"columnName":"CustomerName","operator":0,"value":null} becomes IS NULL and returns exactly the unmatched orders.

Declare left-joined value-type columns as nullable — int?, decimal?, DateTime?. Null placement is pinned by an extra ordering term that BuildNullRank emits only for nullable types, and for a value type that means Nullable<T>. Reference types including string are always nullable to the CLR, so they get the term either way and need nothing special.

---

Pattern B — Include for shape, QueryForge for the page

await db.Orders
    .Include(o => o.OrderItems).ThenInclude(i => i.Product)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .AsNoTracking()
    .ToQueryResultAsync<Order>(query);

This works, and SelectColumns is handled for you: ApplyProjection walks the query's expression chain, finds the Include, and skips the projection rather than applying it.

That behaviour exists because the alternative is silent data loss. EF Core drops every Include from a query whose final shape is a constructed instance rather than the tracked entity, so projecting would return rows with empty navigations — a 200 response, no warning, and the included data simply gone. The projection is the input that cannot be honoured, so it is the one that gets dropped, which is the rule the rest of QueryForge follows for an input it cannot apply.

The detection walks the source chain, so it still finds the include through the AsSplitQuery, AsNoTracking, Where and OrderBy that follow it, and through ThenInclude.

It stops at a Select you wrote yourself, because that projection has already cost you the includes — so Pattern A keeps SelectColumns working even if an Include appears earlier in the chain:

db.Orders
    .Include(o => o.Lines)                       // already moot: the Select below drops it
    .Select(o => new OrderRow { /* … */ })
    .ToQueryResultAsync<OrderRow>(query);        // selectColumns still narrows OrderRow

The trade-off to know: on an Include endpoint, selectColumns does nothing. If you need a narrowed payload and related data, use Pattern A.

What is queryable here: Order's own scalar properties. "OrderItems.Product.Name" is a dotted path, does not resolve, and is dropped.

---

AsSplitQuery

Two sibling collection includes produce a cartesian product — 10 orders × 5 items × 3 payments = 150 rows to carry 18 entities. AsSplitQuery() issues three statements instead.

	Single query (default)	Split query
Round trips	one	one per collection include
Row duplication	cartesian across sibling collections	none
Consistency	one snapshot	rows can change between statements unless in a transaction
Best for	one include, or a single-level chain	several collection includes, or large collections

Set it globally if that suits the application:

options.UseSqlServer(cs, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
// then .AsSingleQuery() per query to opt back out

The paging interaction

ApplyPaging emits Skip/Take on the root query, and EF then fetches children by the root keys in separate statements. If the root ordering is not deterministic, those statements can disagree about which page they are on — you can get children belonging to parents you did not return.

So with a split query, a unique tie-breaker stops being advice and becomes required:

query.SortColumns = [.. query.SortColumns, new SortDescriptor("OrderId")];

With a single query an unstable sort is merely flaky; see Cross-Provider Parity.

---

What resolves, and what is dropped

columnName	Resolves?	Behaviour
"Total" — scalar	yes	filter, sort, group, project
"CustomerName" — DTO property from a join	yes	the reason Pattern A exists
"Customer.Name" — dotted path	no	dropped
"Customer" — reference navigation	as a filter only	sort and group drop it
"OrderItems" — collection navigation	as a filter only	sort and group drop it

ResolveProperty answers "is this a property of the entity", which is right for a filter and wrong for an ordering — so sorting and grouping go through ResolveOrderableProperty, which additionally requires the column to be a single value. Both kinds of navigation are excluded, for different reasons:

• A collection navigation cannot be translated at all. OrderBy(c => c.Orders) throws InvalidOperationException, so before this was guarded a caller could take an endpoint down with one column name.
• A reference navigation does translate — EF Core orders by the related key — which is worse in the grouping case than a failure would be. The group key becomes the whole related entity, so HierarchyNode.Key would serialize every column of it, including ones the caller was never offered.

Filtering is deliberately left alone: a value that cannot convert to an entity type already fails to bind and drops the condition, and Equals against null is a meaningful, translatable test for whether a related row exists.

{ "columnName": "Customer", "operator": 1, "value": null }

is a legitimate "has a customer" filter.

Allow-list anyway

The guard stops a crash and a leak; it does not make every column a good idea to expose. Allow-list what a caller may sort and group by — it also keeps sorts on columns you have indexed:

query.Validate(rules =>
{
    rules.Sort(c    => c.Allow("OrderId", "Number", "PlacedOn", "Total", "CustomerName"));
    rules.GroupBy(c => c.Allow("Status", "CustomerName"));
}, QueryValidationMode.SilentStrip);

---

Choosing a pattern

	Pattern A — flat DTO	Pattern B — Include
Client filters or sorts on joined data	✅	❌ root scalars only
Client needs nested objects back	❌ flat shape	✅
SelectColumns honoured	✅	❌ skipped, by design
AsSplitQuery relevant	no — no collections	✅
Fetches only the needed columns	✅	❌ whole entities
Best for	data grids, exports, search	detail views, "load one order fully"

For a QueryForge endpoint — arbitrary filters arriving from a client — Pattern A is almost always right. Include suits detail endpoints where the shape is fixed and the client is not driving the query.

Tenant isolation still applies

Everything on this page composes after whatever restriction you apply first, so the pattern in Recipes is unaffected:

await db.Orders
    .Where(o => o.TenantId == tenant.Id)
    .Include(o => o.Lines)
    .AsNoTracking()
    .ToQueryResultAsync<Order>(query);