Skip to content
Prague
Improve this page

Joins

Prague supports compile-time-safe joins across [DataCache] types. The generator wires up the call surface based on [DataCacheForeignKey<T>] declarations; the runtime executes the join through one of four resolver families chosen by the relationship shape. The sample domain has four relationships — Order → Customer (many-to-one), Shipment → Order (one-to-one), OrderLine → Order and OrderLine → Product (many-to-one) — and every block on this page is compiled and asserted against them in samples/Prague.Samples/Concepts/Joins.cs.

Declaring a relationship

Place [DataCacheForeignKey<T>(JoinType)] on the property that holds the foreign key. Order points at Customer:

public enum OrderStatus {
    Pending,
    Paid,
    Shipped,
    Cancelled,
}

[DataCache]                                                     // emits OrderCache; [DataCache("OrdersCache")] renames it
[DataCacheTopic("orders.[v:env]")]                              // Kafka topic; [v:env] resolves from KafkaCachesOptions.Vars
[DataCacheSort("ByCreatedAt", "CreatedAtUnixMs:-1", "OrderId:1")] // emits OrderCache.ByCreatedAtComparer (newest first)
public partial class Order {                                    // must be partial
    [DataCacheKey]
    public required string OrderId { get; init; }

    [DataCacheForeignKey<Customer>(DataCacheJoinType.ManyToOne)]   // many orders → one customer; also indexes CustomerId
    [DataCacheGlobalLastUpdateIndex<CustomerActivityIndex>]        // every order write stamps its customer's activity
    public required int CustomerId { get; init; }

    [DataCacheIndex(DataCacheIndexType.Range)]                     // sorted — Gte / Lt / … queries
    public required long CreatedAtUnixMs { get; init; }

    [DataCacheIndex(DataCacheIndexType.Many)]                      // 1:N — many orders share a status
    public required OrderStatus Status { get; init; }

    [DataCacheHasValueIndex]                                       // key set: WithSettledAtUnixMs() = settled (non-null)
    public long? SettledAtUnixMs { get; init; }

    public required decimal Total { get; init; }
}

The generator emits:

  • A symmetric many-index on Order.CustomerId (OrderCache.CustomerIdIndex), so WithCustomerId(...) works too.
  • A forward JoinWithCustomer() method on Order's query builder; the slot is Customer?.
  • A reverse JoinWithOrder() method on Customer's query builder; the slot is QueryResults<Order>.
  • InnerJoinWith{Other} variants of both that drop lefts with no match.

Which cache gets which method depends on the declaration:

Declaration Emitted on Result slot
OneToOne on a scalar FK both caches TRight?
OneToMany on a scalar FK the target cache only QueryResults<TRight>
ManyToOne on a scalar FK both caches forward TRight?, reverse QueryResults<TRight>
ManyToOne on a List<TKey> both caches QueryResults<TRight>

DataCacheJoinType values:

Value Meaning
OneToOne Unique FK — one-to-one in both directions.
ManyToOne Canonical: this entity holds the FK; many of us point at one of them.
OneToMany Legacy alias; declared from the FK target's perspective. Prefer ManyToOne.

Shipment declares the one-to-one and OrderLine two more many-to-ones:

// docs: the 1:1 side of Order — OrderCache.JoinWithShipment() yields Shipment? and
// ShipmentCache.JoinWithOrder() yields Order?. Used for outer-vs-inner join examples.
namespace Prague.Samples.Models;

using Prague.Core;

[DataCache]
public partial class Shipment {
    [DataCacheKey]
    public required string ShipmentId { get; init; }

    [DataCacheForeignKey<Order>(DataCacheJoinType.OneToOne)]       // one shipment per order
    public required string OrderId { get; init; }

    [DataCacheIndex(DataCacheIndexType.Many)]
    public required string Carrier { get; init; }

    public required long ShippedAtUnixMs { get; init; }
}

// docs: the 1:N side of Order (OrderCache.JoinWithOrderLine() yields QueryResults<OrderLine>) and a
// second forward ManyToOne join to Product — the chained-join and inner-join examples.
namespace Prague.Samples.Models;

using Prague.Core;

[DataCache]
public partial class OrderLine {
    [DataCacheKey]
    public required string LineId { get; init; }                  // "ORD-001/1"

    [DataCacheForeignKey<Order>(DataCacheJoinType.ManyToOne)]      // many lines → one order
    public required string OrderId { get; init; }

    [DataCacheForeignKey<Product>(DataCacheJoinType.ManyToOne)]    // many lines → one product
    public required int ProductId { get; init; }

    public required int Quantity { get; init; }

    public required decimal UnitPrice { get; init; }
}

Dual form

If you cannot place the attribute on the FK property (you control the target but not the type holding the key), place it on the target's primary key and name the FK property with the second constructor argument: [DataCacheForeignKey<OrderLine>(DataCacheJoinType.OneToMany, nameof(OrderLine.OrderId))] on Order.OrderId is treated exactly like the direct form on OrderLine.OrderId with the inverted cardinality.

Selector form

When the FK property type doesn't match the target's PK type (a compound key, a tuple, a wider integer), use [DataCacheForeignKey<T, TSelector>] and supply a readonly struct that implements IForeignKeySelector<TFk, TPk>; the generator calls its static Select at join time to turn the property value into the target's key. ShipmentLeg stores its place in a route as (ShipmentId, Sequence) and joins to Shipment through the first half:

// docs: the selector-form foreign key in joins.md. A shipment travels in numbered hops; the leg stores
// its place in the route as the compound (ShipmentId, Sequence), not as a bare shipment id, so the
// generated join cannot compare the property to Shipment's primary key directly. The selector hands
// it the ShipmentId half. ManyToOne: several legs resolve to the same shipment. Forward join only:
// ShipmentLegCache.JoinWithShipment() yields Shipment?; the selector side gets no reverse join.
// Also the home of the has-not-value verb: WithoutArrivedAtUnixMs() in index-types.md.
namespace Prague.Samples.Models;

using Prague.Core;

public readonly struct ShipmentLegShipmentKey : IForeignKeySelector<(string ShipmentId, int Sequence), string> {
    public static string Select((string ShipmentId, int Sequence) hop) => hop.ShipmentId;
}

[DataCache]
public partial class ShipmentLeg {
    [DataCacheKey]
    public required string LegId { get; init; }                   // "SHP-1/1"

    [DataCacheForeignKey<Shipment, ShipmentLegShipmentKey>(DataCacheJoinType.ManyToOne)]   // (ShipmentId, Sequence) → Shipment.ShipmentId
    public required (string ShipmentId, int Sequence) Hop { get; init; }

    public required string FromHub { get; init; }

    public required string ToHub { get; init; }

    [DataCacheHasNotValueIndex]                                    // key set: WithoutArrivedAtUnixMs() = still in flight
    public long? ArrivedAtUnixMs { get; init; }
}

using var rows = legs.Query()
    .JoinWithShipment()                          // ShipmentLegShipmentKey.Select(Hop) → Shipment.ShipmentId
    .ExecutePooled();

Rules of the selector form: ManyToOne goes on a non-key property (as above) and emits the FK index as usual, keyed by the property's own type; OneToOne is the only cardinality allowed on the [DataCacheKey] property itself (CACHE040 otherwise). Both emit the forward join only — there is no ShipmentCache.JoinWithShipmentLeg(). One limitation to know: when two left rows select the same target key, only one of them receives the right row today, so keep the selector one-to-one per query (legs of distinct shipments, as in the sample).

Calling joins

Use the generated methods directly. The outer join keeps every left row and leaves the slot null when nothing matches:

using var rows = _orders.Query()
    .WithCustomerId(42)
    .JoinWithCustomer()                          // outer: rows are kept even without a customer
    .ExecutePooled();

foreach (var row in rows)
    Console.WriteLine($"{row.Left.OrderId} by {row.Right?.Name ?? "unknown"}");

InnerJoinWithCustomer() drops the orders whose customer is missing:

using var rows = _orders.Query()
    .InnerJoinWithCustomer()                     // ORD-404 points at a customer that does not exist: dropped
    .ExecutePooled();

The reverse side of a many-to-one yields the many as a nested QueryResults<T>:

using var rows = _customers.Query()
    .WithId(42)
    .JoinWithOrder()                             // reverse side of the ManyToOne: QueryResults<Order>
    .ExecutePooled();

foreach (var row in rows)
    Console.WriteLine($"{row.Left.Name} has {row.Right.Count} orders");

When only the number of rights matters, CountJoinWith{X} yields the count as int without materialising the rights; InnerCountJoinWith{X} drops zero counts. Both take the same filter overloads as JoinWith{X}; the nested continuation form has no count variant:

using var rows = _customers.Query()
    .CountJoinWithOrder()                        // int per customer: no Order is materialised
    .ExecutePooled();

foreach (var row in rows)
    Console.WriteLine($"{row.Left.Name} has {row.Right} orders");

using var buyers = _customers.Query()
    .InnerCountJoinWithOrder()                   // drops customers with no orders
    .ExecutePooled();

A one-to-one yields TRight? from either side; a one-to-many yields QueryResults<T> from the one side:

using var withShipment = _orders.Query().WithOrderId("ORD-001").JoinWithShipment().ExecutePooled();   // Shipment?
using var withOrder = _shipments.Query().WithCarrier("DHL").JoinWithOrder().ExecutePooled();           // Order?

using var rows = _orders.Query()
    .WithCustomerId(42)
    .JoinWithOrderLine()                         // QueryResults<OrderLine> per order
    .ExecutePooled();

Caches created through DataCacheRegistryBuilder or AddKafkaCaches are joined automatically; stand-alone new XxxCache() instances need leftCache.AddJoinedCaches(rightCache, …) once before joining — the generated signature takes exactly the caches the left's foreign keys point at:

var orders = new OrderCache();
var customers = new CustomerCache();
var lines = new OrderLineCache();
var shipments = new ShipmentCache();
orders.AddJoinedCaches(customers, lines, shipments);   // once, before the first join; the registry does this for you

Chained joins are supported up to 15 slots per query:

using var rows = _orders.Query()
    .WithCustomerId(42)
    .JoinWithCustomer()
    .JoinWithShipment()
    .ExecutePooled();                            // JoinResult<Order, Customer?, Shipment?>

foreach (var row in rows)
    Console.WriteLine($"{row.Left.OrderId}: {row.Right?.Name}, shipped by {row.Right2?.Carrier ?? "nobody yet"}");

Each JoinWith{X} appends a typed slot; the result element type is JoinResult<Order, Customer?, Shipment?>. The fields are Left, Right (the first joined slot — there is no Right1), Right2, Right3, … Pooled join results rent one shared buffer; a single Dispose() on the outer QueryResults returns everything.

Filtering the right side

Every JoinWith{X} accepts an optional filter lambda that builds a query against the right cache; for zero-allocation static lambdas, pass an argument through the TArg overload:

using var goldOnly = _orders.Query()
    .JoinWithCustomer(q => q.WithTier(CustomerTier.Gold))                        // the slot is null unless the customer is Gold
    .ExecutePooled();

using var goldOnlyNoCapture = _orders.Query()
    .JoinWithCustomer(static (q, tier) => q.WithTier(tier), CustomerTier.Gold)   // static lambda + argument: no closure
    .ExecutePooled();

The filter callback receives a non-executable builder: the narrowing methods (WithXxx, UseIndex, .Or) and Where are visible, Sort and Execute* are not. The callback runs once per query, not per row. A right row that fails the filter leaves the slot null on an outer join and drops the left on an inner one.

using var rows = _orders.Query()
    .JoinWithCustomer(q => q.WithCountry("GB").Where(c => c.Name.Length <= 3))   // a residual predicate on the right rows
    .ExecutePooled();

Or inside the filter

The same .Or(b1, b2) clause from the Query Engine works inside a join filter, with the same rules — AND within a branch, union across branches, nesting for three or more disjuncts:

using var rows = _orders.Query()
    .JoinWithCustomer(q => q.Or(                 // attach the customer only if Country = GB OR Tier = Gold
        b => b.WithCountry("GB"),
        b => b.WithTier(CustomerTier.Gold)))
    .ExecutePooled();

Internally the join's right-side narrowing runs on a paired-core builder, so the filter operates on a bitmap over the join's paired candidate set — no per-branch candidate-set allocations, SIMD-vectorized cross-branch union, mark-then-prune within branches.

Sorting after joins

Sort / SortBounded can be applied either before or after a join. After a sorter, only the outer, unfiltered JoinWith{T}() binds — InnerJoinWith{T} and the filter overloads must come before it:

using var top = _orders.Query()
    .SortBounded(OrderCache.ByCreatedAtComparer)
    .JoinWithCustomer()                          // after a sorter only the outer, unfiltered JoinWith{T}() binds
    .ExecutePooled(skip: 0, take: 2);

Sorting before the join compares left values and the join fills the sorted rows (SortBounded then pages before joining, so only the page is joined). Sorting after the join compares JoinResult<…> rows and always runs the classic full sort.

Resolver families (informational)

The generator picks a resolver based on the relationship shape; the call site is identical:

  • PK-to-PK — left PK equals right PK. No FK declaration needed; useful for cross-cache views of the same key.
  • Right-unique-index — right entity carries a unique FK back to the left (Order → Shipment: Shipment.OrderId is the unique FK).
  • Left-symmetric-index — left entity carries a symmetric many FK (ManyToOne); join walks the index's reverse map.
  • Left-symmetric-unique-index — bijective 1:1 via a symmetric unique FK on the left (Shipment → Order).

All four use a paired-execution core that intersects candidate keys with index hits and dispatches a single bulk read into the right cache. Pairs are guarded by a leak-safety contract that returns rented arrays exactly once. Lower-level JoinOne(...) / InnerJoinOne(...) / JoinMany(...) / InnerJoinMany(...) extensions exist for the raw cache; pass the cache wrapper, not .Query().

Next

Prague documentation