Table of Contents

Relationships and single-query loading (JoinInto)

A relationship is declared explicitly on the mapping metadata (HasMany/HasOne or [Relationship]); JoinInto then loads a declared navigation in a single round trip and stitches the denormalized rows back onto deduplicated parents: a one-to-many collection through one LEFT JOIN (or INNER JOIN), a one-to-one nullable reference through one join, and a many-to-many collection through a junction with two flat joins (a derived link join plus the child join).

Prerequisites: Quickstart · Joins · Eager loading child collections

Overview

nextorm resolves a graph from declared metadata, not from a mapper convention: an entity without declared relationships is mapped by its columns as usual, and a navigation property is excluded from the column mapping only when it participates in a declared relationship. This page covers the metadata model and JoinInto, the explicit single-query relationship loader. Implicit navigation queries are supported for declared relationships — a reference navigation as a scalar chain or a presence check, a reference hop used inside a correlated predicate or scalar subquery, and a collection navigation through exactly four direct terminals (see Implicit navigation queries) — not inferred by convention. The correlated-predicate form joins the hop on the outer command and reads the joined alias from the nested one (LEFT JOIN plus a correlated EXISTS/scalar subquery, no APPLY); it is a predicate/scalar surface, not correlated eager loading, which stays reserved and rejected (EagerLoadMode.Correlated). Navigation after a temp-table/TVP boundary is not available (fail-closed), and a table-valued parameter is parameter-only (no query root).

JoinInto and LoadWith are two ways to fill a parent collection and share one assignment contract: JoinInto is one denormalized query over all parents, and LoadWith defaults to a split query with one extra child statement per key chunk (or one denormalized query when the builder passes EagerLoadMode.SingleQuery to LoadWith). JoinInto fills one declared navigation; a multi-level (level 2+) graph is expressed by nesting LoadWith child queries, where each level runs in one graph-wide mode (see Nested graphs). A third form returns the per-parent collections inside a projected result instead of filling the entity — the direct Select(p => new { p.Id, p.Comments }) member and the AsEntityBuilder<T>() … ToList() adapter — documented in Collection-valued projection slots.

LoadWith reads the same declared metadata when it is used without key selectors: the collection and reference LoadWith(navigation) / LoadWith(navigation, childQuery) overloads take the navigation's ordered key components from its declaration — one predicate per component, the component at position i on one side against the component at position i on the other — instead of requiring explicit selectors; the explicit-key overload remains the form for an entity pair with no declared relationship (see Declared-metadata form). The declaration is therefore the single producer for both loaders: HasMany, HasOne, HasOneToOne, HasManyThrough and [Relationship] are the only source of a relationship, and neither loader infers one from a convention.

Declaring a relationship

A relationship is declared on each side independently and symmetrically, at mapping time with From<T>. The kind is derived from the navigation shape — a collection is a one-to-many, a reference is a many-to-one — and the inverse is not inferred.

Fluent (HasMany/HasOne)

The principal side declares the collection navigation and the foreign key on the dependent type:

ctx.From<Order>(b => b.HasMany(o => o.Items, i => i.OrderId));

The dependent side declares the reference navigation and the foreign key on its own type:

ctx.From<OrderItem>(b => b.HasOne(i => i.Order, i => i.OrderId));

The principal key is taken from the existing key metadata of the principal type ([Key], EntityPropertyBuilder<T>.Key(), or the Id/<TypeName>Id convention), so the two sides converge as a one-to-many/many-to-one pair. The default principal key is the whole key in entity-metadata order — every key component, never only the first one.

HasMany, HasOne, HasOneToOne and HasManyThrough expose ten declaration overloads (A–J). All ten share one selector parser and build the same internal relationship descriptor. The ten signatures are:

// A — 1:N, navigation + explicit foreign key + explicit principal key
public EntityMetadataBuilder<T> HasMany<TChild>(
    Expression<Func<T, ICollection<TChild>>> navigation,
    Expression<Func<TChild, object?>> foreignKey,
    Expression<Func<T, object?>> principalKey) where TChild : class;

// B — 1:N, no navigation, explicit foreign key + explicit principal key
public EntityMetadataBuilder<T> HasMany<TChild>(
    Expression<Func<TChild, object?>> foreignKey,
    Expression<Func<T, object?>> principalKey) where TChild : class;

// C — N:1, navigation + explicit foreign key + explicit principal key
public EntityMetadataBuilder<T> HasOne<TChild>(
    Expression<Func<T, TChild?>> navigation,
    Expression<Func<T, object?>> foreignKey,
    Expression<Func<TChild, object?>> principalKey) where TChild : class;

// D — N:1, no navigation, explicit foreign key + explicit principal key
public EntityMetadataBuilder<T> HasOne<TChild>(
    Expression<Func<T, object?>> foreignKey,
    Expression<Func<TChild, object?>> principalKey) where TChild : class;

// E — 1:1, navigation + explicit parent key + explicit child foreign key
public EntityMetadataBuilder<T> HasOneToOne<TChild>(
    Expression<Func<T, TChild?>> navigation,
    Expression<Func<T, object?>> parentKey,
    Expression<Func<TChild, object?>> childForeignKey) where TChild : class;

// F — M:N, navigation + all four explicit key lists
public EntityMetadataBuilder<T> HasManyThrough<TChild, TJunction>(
    Expression<Func<T, IEnumerable<TChild>>> navigation,
    Expression<Func<T, object?>> parentKey,
    Expression<Func<TJunction, object?>> junctionParentForeignKey,
    Expression<Func<TChild, object?>> childKey,
    Expression<Func<TJunction, object?>> junctionChildForeignKey)
    where TChild : class where TJunction : class;

// G — 1:N, no navigation; principal = the full key of T
public EntityMetadataBuilder<T> HasMany<TChild>(
    Expression<Func<TChild, object?>> foreignKey) where TChild : class;

// H — N:1, no navigation; principal = the full key of TChild
public EntityMetadataBuilder<T> HasOne<TChild>(
    Expression<Func<T, object?>> foreignKey) where TChild : class;

// I — 1:1 with the child foreign key only; parent key = the full key of T
public EntityMetadataBuilder<T> HasOneToOne<TChild, TKey>(
    Expression<Func<T, TChild?>> navigation,
    Expression<Func<TChild, TKey>> childForeignKey) where TChild : class;

// J — M:N with the two junction foreign keys only; both entity keys = full keys
public EntityMetadataBuilder<T> HasManyThrough<TChild, TJunction>(
    Expression<Func<T, IEnumerable<TChild>>> navigation,
    Expression<Func<TJunction, object?>> junctionParentForeignKey,
    Expression<Func<TJunction, object?>> junctionChildForeignKey)
    where TChild : class where TJunction : class;

When each form is required: A, C, E and I infer TChild from the navigation; I additionally infers TKey from the child foreign key, so a composite anonymous key needs no explicit type argument. The key-only forms B, D, G and H cannot infer TChild from an untyped lambda — call them with an explicit <TChild> (a typed selector variable can still infer it, but the public form is explicit). F and J require explicit <TChild, TJunction>: TJunction appears only inside untyped lambdas, and C# cannot supply a partial generic-argument list. Use A–F when the key must be explicit — a different component order, a non-key principal property, or a reversed role; use G–J when the full primary key is the principal key. To customise only one side of a many-to-many relationship use F with all four lists; there is no partially ordered overload.

Ordered key selectors

Every key selector — fluent or attribute — is resolved by one shared parser, so all declarations accept exactly the same forms:

  • a direct scalar mapped property — c => c.ParentId;
  • a flat anonymous object of direct scalar mapped properties — c => new { c.TenantId, c.ParentId }.

The order is the contract. Components are paired positionally between the two sides: the first foreign-key component pairs with the first principal-key component, the second with the second, and so on. They are never matched by name, sorted by key metadata, or matched by type. Anonymous-member aliases are ignored: new { Parent = c.ParentId } selects ParentId. Boxing and nullable conversions around a single component are unwrapped before the underlying mapped CLR types are compared.

A null selector (or a selector without a body) throws ArgumentNullException naming the parameter. Structural rejections throw NotSupportedException naming the relationship, the component position and the component count: field access, a nested member path, a computation, a method call, an index access, an array or collection initializer, a tuple literal, DTO or object construction (new Dto { ... }), a nested anonymous object, an empty anonymous object, and the same property selected twice.

Default and explicit principal keys

An overload without a principalKey parameter uses the whole primary key of the principal entity in entity-metadata order — the members flagged IsKey in the order IEntityMetadata.Properties lists them. There is no first-key fallback and no name matching: a foreign key that selects fewer components than the full principal key is an arity error when the metadata is resolved, not a silent use of the first key. An explicit principal key may select mapped non-key properties (an alternate logical key) and any component order; the two lists must still have the same length and a matching nullable-unwrapped type at every position. HasManyThrough applies the same rule to both legs: each entity key and the junction foreign key that references it must have the same component count and the same positional types.

The first registration for a type wins, like a query filter: a later From<T>(...) configuration for the same type is ignored. A property that participates in a declared relationship is excluded from IEntityMetadata.Properties and is reachable only through IEntityMetadata.Relationships; mapping it as a column throws InvalidOperationException. Members that are not part of a declared relationship (including a reference-typed member with no declaration) are mapped as columns.

Attribute ([Relationship])

The same metadata can be declared declaratively on the navigation property:

public sealed class Order
{
    public int Id { get; set; }

    [Relationship(ForeignKey = nameof(OrderItem.OrderId))]
    public ICollection<OrderItem> Items { get; set; } = new List<OrderItem>();
}

ForeignKey names the single foreign-key property on the dependent side; the kind is inferred from the navigation shape, exactly as for the fluent API. A composite relationship uses the two arrays instead:

public sealed class Order
{
    public int TenantId { get; set; }
    public int Id { get; set; }

    [Relationship(
        ForeignKeys = new[] { nameof(OrderItem.TenantId), nameof(OrderItem.OrderId) },
        PrincipalKeys = new[] { nameof(Order.TenantId), nameof(Order.Id) })]
    public ICollection<OrderItem> Items { get; set; } = new List<OrderItem>();
}

Exactly one of ForeignKey and ForeignKeys must be set. ForeignKeys is an ordered list; the component pairing is positional, so the names on the two sides may differ. PrincipalKeys is optional: when omitted, the principal key is the whole key of the principal entity in entity-metadata order, and an explicit list may select mapped non-key properties. Every name must name a mapped property; an empty or missing foreign-key form, an empty array, a duplicate name, an unmapped or missing member, an arity mismatch, or a positional type mismatch is rejected with NotSupportedException when the metadata is resolved. The attribute declares one-to-many and many-to-one relationships only — one-to-one and many-to-many are fluent-only.

The attribute and a fluent declaration on the same navigation produce the same model, and the fluent declaration wins when both are present; a shadowed attribute is not validated. [Relationship] and the fluent overloads share one selector language and one set of validation rules.

Validation timing

Validation is staged:

  • At declaration — a null selector throws ArgumentNullException, and the selector syntax (scalar property or flat anonymous object only) is checked by the shared parser.
  • When the relationship metadata is resolved — everything that needs the other type: mapped-property resolution, the default full principal key, arity, duplicate components and positional nullable-unwrapped types. Registration order does not matter: From<T>() does not eagerly validate a cross-reference that is not registered yet, and the related type may be configured later.
  • Before SQL and before a cached plan — a relationship is validated when it is first consumed, and validation completes before any SQL is generated and before a plan is inserted into the plan cache. An invalid or partially resolved declaration never produces SQL and never caches a plan; correcting the declaration afterwards is not defeated by a cached failure.

The same structural validation applies to manually supplied IRelationshipMetadata and junction lists: at least one component per list, equal arity on every leg, no duplicate components, every component resolving by semantic mapped-member identity to a configured mapped property, and pairwise nullable-unwrapped CLR types. A custom property-metadata instance does not have to be the same object as the configured one; a valid escape-hatch mapping — including an explicit non-key principal key — keeps working, while a malformed list throws NotSupportedException before SQL and before a cache insert.

Inverse participates in demand-time validation only: a direct navigation needs no inverse, and an unrelated declaration does not fail. When the requested operation needs the inverse, an absent inverse resolves to null, while an ambiguous one — two different navigation properties exposing the same canonical mapping — throws NotSupportedException naming both conflicting candidates.

Loading with JoinInto

JoinInto declares a join that fills a declared navigation — a child collection (one-to-many), a nullable reference (one-to-one) or a junction-backed many-to-many collection — when the query is enumerated by a list terminal. It returns a copy of the builder, so the source builder is unchanged and declarations chain:

Note. The former child-collection scenario (issue #40) — loading children from a parent+child join — is expressed today by JoinInto into a declared collection property, as shown below. Projecting children into an arbitrary (anonymous) shape, the historical NORM.ChildCollection(...) form, is not provided: declare the collection and load it with JoinInto, or select the child rows with an explicit join and materialize them yourself.

var orders = ctx.From<Order>()
    .JoinInto(ctx.From<OrderItem>(), (o, i) => o.Id == i.OrderId, o => o.Items)
    .ToList();

Overloads

JoinInto comes in three families. Two of them differ by one argument — the child source is either supplied as an EntityBuilder<TChild> or resolved from the mapping of TChild — and take an explicit (parent, child) => … predicate as the join condition; both fill the same navigations and share one contract, so choose by whether you have a child source in hand. The third family takes a navigation only and derives the join condition from the declared relationship metadata: see Metadata-driven joins.

Source-less

The five source-less forms take no child builder — the child source is the mapping of TChild:

// collection, LEFT (the default)
public EntityBuilder<TEntity> JoinInto<TChild>(
    Expression<Func<TEntity, TChild, bool>> predicate,
    Expression<Func<TEntity, ICollection<TChild>>> collection,
    Action<JoinOptions>? options = null);

// collection, explicit join kind: Inner or Left only
public EntityBuilder<TEntity> JoinInto<TChild>(
    Expression<Func<TEntity, TChild, bool>> predicate,
    Expression<Func<TEntity, ICollection<TChild>>> collection,
    JoinType joinType,
    Action<JoinOptions>? options = null);

// reference, LEFT (the default)
public EntityBuilder<TEntity> JoinInto<TChild>(
    Expression<Func<TEntity, TChild, bool>> predicate,
    Expression<Func<TEntity, TChild?>> navigation,
    Action<JoinOptions>? options = null)
    where TChild : class;

// reference, explicit join kind
public EntityBuilder<TEntity> JoinInto<TChild>(
    Expression<Func<TEntity, TChild, bool>> predicate,
    Expression<Func<TEntity, TChild?>> navigation,
    JoinType joinType,
    Action<JoinOptions>? options = null)
    where TChild : class;

// fallback without declared metadata: explicit parent/child keys, always LEFT
public EntityBuilder<TEntity> JoinInto<TChild, TKey>(
    Expression<Func<TEntity, TChild, bool>> predicate,
    Expression<Func<TEntity, ICollection<TChild>>> collection,
    Expression<Func<TEntity, TKey>> parentKey,
    Expression<Func<TChild, TKey>> childKey,
    Action<JoinOptions>? options = null)
    where TKey : notnull;

The same five shapes as calls:

// 1. collection, LEFT
var orders = ctx.From<Order>()
    .JoinInto<OrderItem>((o, i) => o.Id == i.OrderId, o => o.Items)
    .ToList();

// 2. collection, explicit kind
var orders = ctx.From<Order>()
    .JoinInto<OrderItem>((o, i) => o.Id == i.OrderId, o => o.Items, JoinType.Inner)
    .ToList();

// 3. reference, LEFT — TChild : class
var orders = ctx.From<Order>()
    .JoinInto<Invoice>((o, i) => o.Id == i.OrderId, o => o.Invoice)
    .ToList();

// 4. reference, explicit kind — TChild : class
var orders = ctx.From<Order>()
    .JoinInto<Invoice>((o, i) => o.Id == i.OrderId, o => o.Invoice, JoinType.Left)
    .ToList();

// 5. explicit keys — always LEFT
var orders = ctx.From<Order>()
    .JoinInto<OrderItem, int>((o, i) => o.Id == i.OrderId, o => o.Items, o => o.Id, i => i.OrderId)
    .ToList();

Spell the child type argument explicitly, as above: inference from the predicate lambda is not a documented contract. The two reference forms require a reference-type child (where TChild : class); the key form requires a non-nullable key type and is always LEFT — that is the only join kind its signature exposes. The collection and reference forms resolve their keys from the declared relationship, and the key form is symmetrical to the LoadWith fallback for an entity pair without relationship metadata.

Explicit source

The explicit-source family is the same five shapes with the child source as the first argument:

var orders = ctx.From<Order>()
    .JoinInto(ctx.From<OrderItem>(), (o, i) => o.Id == i.OrderId, o => o.Items)
    .ToList();

var orders = ctx.From<Order>()
    .JoinInto(ctx.From<OrderItem>(), (o, i) => o.Id == i.OrderId, o => o.Items, JoinType.Inner)
    .ToList();

var orders = ctx.From<Order>()
    .JoinInto(ctx.From<Invoice>(), (o, i) => o.Id == i.OrderId, o => o.Invoice)
    .ToList();

var orders = ctx.From<Order>()
    .JoinInto(ctx.From<Invoice>(), (o, i) => o.Id == i.OrderId, o => o.Invoice, JoinType.Left,
        j => j.SuppressCartesianWarning())
    .ToList();

var orders = ctx.From<Order>()
    .JoinInto(ctx.From<OrderItem>(), (o, i) => o.Id == i.OrderId, o => o.Items, o => o.Id, i => i.OrderId)
    .ToList();

A source-less call is exactly this call with a bare ctx.From<TChild>() source: the source-less form resolves that source itself and then delegates, forwarding the predicate, the join kind and the options unchanged. The SQL, the plan and the results are the same, so for a bare source either family can be used interchangeably. Keep the explicit form when the child source is not the plain entity mapping — a raw table or CTE name, for example, exists only on a child builder.

JoinInto is LEFT by default: a parent with no children is kept with an empty collection. The explicit-kind overloads accept only JoinType.Inner and JoinType.Left; Inner drops childless parents.

Resolution and errors

The child source is resolved once, while the join is declared:

  • a configured mapping wins over convention — when From<Child>(…) has already configured the child mapping (the table name, for instance), the source-less form joins that mapping, exactly as a bare explicit ctx.From<Child>() would;
  • an unresolvable child type fails at the JoinInto(...) call, with the same exception type and message as From<TChild>() itself; no builder is returned, so the failure does not surface later at ToList();
  • a null predicate throws ArgumentNullException with paramName: "predicate" before the child source is resolved and before the options callback runs, so neither a broken child mapping nor a throwing callback can mask the argument error;
  • a null navigation or key selector behaves exactly as in the explicit-source form: the same guards in the same order, the same exceptions.

The source-less form adds no second resolution path and no fallback table; everything the rest of this page says about keys, materialization, paging, Where and terminals applies to both families.

Options

Every one of the ten forms takes a trailing Action<JoinOptions>? options argument. The callback runs exactly once per JoinInto call, and an exception thrown inside it propagates unchanged. Omitting the argument or passing null means no callback at all. The options object is not retained: its values are copied onto the resulting join.

var orders = ctx.From<Order>()
    .JoinInto<OrderItem>((o, i) => o.Id == i.OrderId, o => o.Items, JoinType.Left,
        j => j.SuppressCartesianWarning())
    .ToList();

The same parameter carries the local relationship configurations described below — JoinOptions.OneToOne and JoinOptions.ManyToMany — and the join modifiers of the underlying join.

Child filters

"Source-less" describes the source, not a filter: the child source is the mapping of TChild and carries no condition. Neither family applies a child-source filter to the join today — a child builder that carries a Where does not add a condition to the join, and only the (parent, child) => … predicate reaches the ON clause. Filtering a child source is tracked as #309.

To narrow the child set, write the condition into the predicate, which is the ON condition — (o, i) => o.Id == i.OrderId && i.Active pairs only matching active children. With Left a parent whose children are all filtered out is kept with an empty collection; with Inner it is dropped. Do not read a Where on the child source as the equivalent of that predicate: the join ignores it today.

Metadata-driven joins

The navigation-first family takes only a navigation. The ON condition is synthesized from the declared relationship metadata, so the caller never restates the keys:

// reference navigation (one-to-one) — TChild : class, always LEFT
public EntityBuilder<TEntity> JoinInto<TChild>(
    Expression<Func<TEntity, TChild?>> navigation,
    Expression<Func<TEntity, TChild, bool>>? filter = null,
    Action<JoinOptions>? options = null)
    where TChild : class;

// collection navigation (one-to-many or many-to-many) — always LEFT
public EntityBuilder<TEntity> JoinInto<TChild>(
    Expression<Func<TEntity, ICollection<TChild>>> collection,
    Expression<Func<TEntity, TChild, bool>>? filter = null,
    Action<JoinOptions>? options = null);

var orders = ctx.From<Order>()
    .JoinInto(o => o.Invoice)                     // reference: fills Order.Invoice
    .ToList();

var orders = ctx.From<Order>()
    .JoinInto(o => o.Items)                       // collection: fills Order.Items
    .ToList();

var orders = ctx.From<Order>()
    .JoinInto(o => o.Items, (o, i) => i.Active)   // additional filter, still a LEFT join
    .ToList();

Inference. The child type comes from the navigation, so no explicit type argument is written. A single reference navigation binds the reference form and assigns that member; a declared ICollection<T> binds the collection form and fills it. A concrete collection navigation (List<T>, HashSet<T>, any other ICollection<T> implementation) is routed to the collection form as well: overload resolution alone would prefer the reference form for a concrete type, so the collection overload carries an explicit overload-resolution priority. That is a deliberate rule of this API, not an incidental binding.

ON. One equality per declared key component, conjoined in declaration order, is read from IRelationshipMetadata: parent.<PrincipalKey[i]> == child.<ForeignKey[i]>. A composite key contributes every component, positionally; nothing is matched by column name, and a default principal key is never reduced to its first component. A many-to-many navigation keeps its junction synthesis — the parent→junction edge pairs the parent key with the junction's parent foreign key componentwise, the junction→child edge pairs the child key with the junction's child foreign key — and the optional filter is appended to that child edge.

The second argument is a filter, not a key restatement. When supplied it becomes one more conjunct inside the same ON condition; it never replaces the declared-key equality and is never moved into a WHERE. Omitting it, or passing null, means no additional restriction. Because these are LEFT joins, a reference whose only candidate is filtered out stays null, a collection whose children are all filtered out stays empty, and the parent row is preserved either way. Key restatement is unnecessary, so an arbitrary child condition is the normal use: (o, i) => i.Active narrows the children while the declared keys still decide which parent they belong to.

Boundary with the ten predicate forms. The five JoinInto overloads without a navigation and their five source-less counterparts keep their signatures, their explicit join kinds and their predicate-as-ON semantics — the caller's (parent, child) => … remains the whole join condition and no declared key is synthesized into it. The metadata-driven semantics described here belong only to the two navigation-first overloads. Choose the navigation-first forms when the relationship is declared and the keys should come from metadata; keep the predicate forms when the join condition is genuinely yours (a non-declared pair, an extra correlation, an explicit Inner/Left choice).

One-to-one reference

A one-to-one relationship is declared with the principal key on the parent and the unique foreign key on the child; the foreign-key uniqueness is a declaration-time trust — the core neither inspects the schema or the data nor imposes a uniqueness constraint, so it is not validated at build time:

ctx.From<Order>(b => b.HasOneToOne(o => o.Invoice, o => o.Id, i => i.OrderId));

Loading uses the reference-navigation JoinInto overloads, which assign the single joined child to the parent's reference member:

var orders = ctx.From<Order>()
    .JoinInto(ctx.From<Invoice>(), (o, i) => o.Id == i.OrderId, o => o.Invoice)
    .ToList();

The reference renders the same flat join as the collection form; the joined child is materialized from the paired row:

select t1.id, t1.name, t2.id, t2.order_id, t2.total
 from order as t1
 left join invoice as t2 on t1.id = t2.order_id

JoinOptions.OneToOne<TParent, TChild, TKey>(parentKey, childForeignKey) configures the relationship locally through a JoinInto options lambda (the same two selectors) when it is not declared, fully replacing the declared metadata for that call:

var orders = ctx.From<Order>()
    .JoinInto(
        ctx.From<Invoice>(),
        (o, i) => o.Id == i.OrderId,
        o => o.Invoice,
        j => j.OneToOne<Order, Invoice, int>(o => o.Id, i => i.OrderId))
    .ToList();

The LEFT/INNER semantics mirror the collection loaders: with Left (the default) a parent without a matching child keeps the reference null, and with Inner a parent without a matching child is excluded. The build-time uniqueness trust above is backed only per parent at load time: a parent that matches more than one distinct non-null child identity throws InvalidOperationException at materialization, while repeated rows of the same child identity (including cartesian repeats caused by a neighbouring join) and keyless/null-identity repeats are tolerated and collapse to the first child. The foreign-key uniqueness itself is never validated against the schema or the data, so a non-unique foreign key is accepted when the relationship is declared. The navigation member must be settable — a read-only reference throws NotSupportedException.

Many-to-many through a junction

A many-to-many relationship is declared with the explicit junction (link) entity — the core never infers it. HasManyThrough takes the collection navigation, the keys on both principal sides and the two junction foreign keys that reference them:

ctx.From<Post>(b => b.HasManyThrough<Tag, TagLink, int, int>(
    p => p.Tags,      // collection navigation on the principal
    p => p.Id,        // parent key
    l => l.PostId,    // junction FK referencing the parent key
    t => t.Id,        // child key
    l => l.TagId));   // junction FK referencing the child key

Each junction foreign key must match the type of the key it references. Loading uses the collection JoinInto overload with a local JoinOptions.ManyToMany configuration (the same four selectors), which fully replaces the declared metadata for that call:

var posts = ctx.From<Post>()
    .JoinInto(
        ctx.From<Tag>(),
        (p, t) => t.Active,
        p => p.Tags,
        j => j.ManyToMany<Post, Tag, TagLink, int, int>(
            p => p.Id, l => l.PostId, t => t.Id, l => l.TagId))
    .ToList();

The junction is projected as a derived row_number() link subquery, joined flat to the parent and then to the child; the user predicate is appended to the child ON:

select t1.id, t1.title, t2.parent_key, t2.child_key, t2.occurrence, t3.id, t3.name
 from post as t1
 left join (
    select j.post_id as parent_key, j.tag_id as child_key,
           row_number() over (partition by j.post_id, j.tag_id order by j.tag_id) as occurrence
 from tag_link as j
) as t2 on t1.id = t2.parent_key
 left join tag as t3 on t3.id = t2.child_key and t3.active

The (parent, child) => ... predicate is an extra filter over the joined rows; the join keys come from the junction configuration, not from the predicate. Use (p, t) => true when no filter is needed.

The LEFT/INNER semantics are the same as for a one-to-many collection: with Left a parent without a junction row is kept with an empty collection, and a junction row that references a missing child contributes no element; with Inner a parent without a matching child is excluded. Multiplicity differs in one place: duplicate (parent, child) junction rows are preserved as distinct elements — the same child appears once per junction row — whereas a one-to-many join collapses duplicate child rows by child identity.

When a query carries two or more collection navigations (one-to-one is excluded) the preparation emits the JoinInto.MultipleCollections warning once per plan, because the intermediate row count multiplies into a cartesian product. Pass JoinOptions.SuppressCartesianWarning() through a JoinInto options lambda (j => j.SuppressCartesianWarning()) to silence it; the warning is informational and does not change the result.

A junction whose parent and/or child foreign key spans several components is fully supported: the join and the occurrence partition use every declared component, so changing any component (not only the first) changes the linked child, and repeated junction occurrences stay distinct. The single-query dispatcher (EagerLoadMode.SingleQuery) handles such a junction too. Declare the components with the composite fluent overloads (A–J) or with ForeignKeys/PrincipalKeys on the attribute; the legacy ForeignKey form remains for a single component. A many-to-many JoinInto takes two projection slots, so it counts double against the arity cap.

One round trip

For a list terminal the join is part of the query and the whole graph is read with one statement:

select t1.id, t1.name, t2.id, t2.order_id, t2.name
 from order as t1
 left join order_item as t2 on t1.id = t2.order_id

The rows are a denormalized (parent, child) stream; after the query runs, the list terminal:

  1. deduplicates parents by their principal key (the first row's parent instance wins),
  2. groups children by the foreign-key value,
  3. assigns each child collection through the shared eager-load assignment rules (below).

Parent order is the first-occurrence order of the rows; child order follows the statement, so add an OrderBy when the order matters. Duplicate child rows are dropped by the child identity.

Parent deduplication and child identity both need a mapped key on the respective side. When the parent (or the child) declares no key, a plain JoinInto query falls back to reference identity: on a SQL provider every denormalized row materializes a fresh instance, so repeated rows are not collapsed and a cartesian product (two child collections) stays duplicated. Single-query eager loading (LoadWith with EagerLoadMode.SingleQuery) has no such fallback — it requires a mapped parent key and rejects a keyless parent — so give the parent and the child a mapped key ([Key], Key(), or the Id/<TypeName>Id convention), or use the explicit-key overload with a keyed child, for reliable deduplication. See Limitations.

Assignment rule

For every parent the loader decides how to fill the collection, identical to LoadWith:

Current value of collection Member settable Behaviour
non-null either cleared and refilled
null yes a fresh list is assigned
null no (read-only) NotSupportedException

A read-only collection is supported as long as it is initialized with an empty collection and never reassigned:

public sealed class Order
{
    public int Id { get; set; }
    public ICollection<OrderItem> Items { get; } = new List<OrderItem>();
}

Where semantics

A Where after JoinInto is applied to the joined query — it filters the denormalized rows, like a SQL WHERE over the two tables:

  • with Left, a predicate that is not null-safe (p => p.Items.First()... or a raw condition on the child) can drop the parent entirely when no child matches, because the SQL LEFT JOIN ... WHERE semantics apply;
  • with Inner it is an ordinary filter.

A Where written before JoinInto filters the parent source. The predicate passed to JoinInto is the ON condition and only pairs rows; it never removes a LEFT parent.

JoinInto folds a child source's Where predicate into the join's ON condition, preserving unmatched parents for LEFT joins; unsupported child-query modifiers throw NotSupportedException before execution. A derived (As) child source is supported when its only modifier is its own Where; any other modifier on or inside it is rejected.

Parent paging

Limit/Offset (Take/Page) constrain parents, not denormalized rows. On a SQL provider the parent source is selected in a subquery with the limit and the join wraps around it, so paging a parent does not truncate its children. On the in-memory provider the limit is applied to the deduplicated parent list.

select t1.id, t1.name, t2.id, t2.order_id, t2.name
 from (select id, name
 from order limit 10 offset 20) as t1
 left join order_item as t2 on t1.id = t2.order_id

Multiple collections

Each JoinInto adds its own join. Two child collections on the same parent produce a cartesian product of rows in the denormalized stream; parent deduplication and independent per-collection grouping keep the result correct, but the intermediate row count multiplies. When two or more collection navigations are present, preparation emits the JoinInto.MultipleCollections warning once per plan; pass JoinOptions.SuppressCartesianWarning() through a JoinInto options lambda to silence it. Declare several collections only when the parent sets are small, or load the second collection with a separate query (the LoadWith loader is the alternative — split-query by default, single-query with EagerLoadMode.SingleQuery).

JoinInto cannot be combined with any other join on the same builder: once the builder carries a JoinInto, adding Join, LeftJoin, CrossJoin, SemiJoin, AntiJoin (or any other explicit join) throws NotSupportedException, and vice versa. Declare the JoinInto collections on a plain entity source, so the non-list terminals know exactly which joins to drop. Mixing the two would leave the parent-only command unable to tell an explicit join from a JoinInto.

Join modifiers

The join modifiers of the underlying join are available. On ClickHouse, JoinOptions.WithStrictness may be passed to JoinInto(...) through its trailing lambda: j => j.WithStrictness(Any) keeps only the first matching child per parent, so the loaded collection is truncated to at most one element; the other strictness modifiers (All, Asof) and JoinOptions.Global pass through unchanged. An option passed to a JoinInto preserves the parent-only terminal behaviour — the non-list terminals still exclude the join.

Terminal boundary

The join is part of the command and is stitched only by the stitching terminals:

ToCommand() keeps the join but returns only the denormalized (parent, child) rows, so a bare enumeration may repeat a parent once per matching child. Every other terminal built on the parent command — First, Count, Any, ToHashSet, ToEnumerable, ToAsyncEnumerable, Prepare — excludes the join and returns the parent only, with the collections not filled. A Select projection or an As derived source is rejected with NotSupportedException: Select would keep the join without stitching (repeating parents, never grouping children) and As would drop the stitching metadata. Materialize with ToList/ToListAsync when the collections must be filled.

In-memory parity

JoinInto works on the in-memory provider: the join and the grouping run as delegates, and the LEFT/INNER semantics, parent deduplication, ordering and empty collections match the SQL providers. The in-memory provider pages the deduplicated parents instead of wrapping a parent subquery.

NotSupportedException boundaries

The following are rejected with NotSupportedException:

  • an undeclared relationship, when the collection overload is used without relationship metadata — declare it with HasMany/HasOne/HasManyThrough/[Relationship] or use the explicit-key overload; the reference overload likewise requires a declared one-to-one (HasOneToOne) or a local JoinOptions.OneToOne configuration;
  • JoinInto applied to a derived (As) or joined projection source;
  • a Select projection or an As derived source applied to a builder carrying a JoinInto — both drop the stitching contract (repeated parents or lost metadata), so materialize with ToList/ToListAsync instead;
  • JoinInto combined with another join on the same builder (adding any explicit join once a JoinInto is declared throws, and adding a JoinInto once an explicit join exists throws) — declare JoinInto on a plain entity source only;
  • a join kind other than Inner/Left;
  • explicit-key selectors that select different property types, or that are not a simple property/field access (a computed key has no stable plan identity);
  • a read-only collection member that is null at runtime;
  • more than seven child collections on one query (the projection arity cap).

Key arity is not one of these boundaries: a many-to-many relationship under EagerLoadMode.SingleQuery and a composite principal, foreign or junction key on the relationship are supported on the explicit JoinInto path, which joins and groups on every declared component (see Many-to-many through a junction). The component lists come from the relationship metadata: declare them with the composite fluent overloads (A–J) or with ForeignKeys/PrincipalKeys, and JoinInto consumes every component in order.

The relationship metadata itself fails closed too: an FK property that is not mapped on the dependent type, a principal type with no key, an empty or duplicated component list, a mismatched arity between the two sides (including a single-column foreign key against a composite default principal key), a null component, a component without a CLR member, or an FK/principal-key type mismatch (compared after unwrapping nullable) throws NotSupportedException when the keys are resolved — before any SQL is generated and before a plan is inserted into the plan cache. The same structural validation applies to manually supplied IRelationshipMetadata (see Validation timing).

Relationship metadata

The metadata model is exposed through IEntityMetadata.Relationships; each entry is an IRelationshipMetadata with Kind (RelationshipKind), DeclaringType, RelatedType, Navigation, IsCollection, ForeignKey, PrincipalKey and Inverse. ForeignKey and PrincipalKey are ordered IReadOnlyList<IPropertyMetadata> component lists — the order is the declaration order (or, for a default principal key, entity-metadata order), and the component at position i on one side pairs with the component at position i on the other. Inverse points at the other side's metadata when both sides are declared and agree on the full ordered foreign key and types, and is null otherwise — it is never inferred. Inverse is resolved lazily on its first read and cached: register the related type (and the declaring type) before reading it, because a read taken before the other side is registered caches null and will not be refreshed by a later registration. Two different navigations that expose the same canonical mapping make the inverse ambiguous, and reading it throws NotSupportedException naming both candidates. A composite relationship is declared through the public producers — the composite HasMany/HasOne/HasOneToOne/HasManyThrough overloads or [Relationship(ForeignKeys = …, PrincipalKeys = …)] — and both JoinInto and the metadata-driven LoadWith consume every declared component in position order; a single-component declaration keeps the one-column form.

See also

Source: tests/nextorm.sqlite.tests/Joins/JoinInto/JoinIntoSqlGenerationTests.cs:23, tests/nextorm.sqlite.tests/Joins/JoinInto/JoinIntoExecutionTests.cs:43, tests/nextorm.core.tests/JoinInto/InMemory/JoinIntoInMemoryTests.cs:31, tests/nextorm.core.tests/Metadata/Relationships/RelationshipMetadataTests.cs:173.

Edit this page