/DB

Joins

Query across up to four tables with Join / LeftJoin / RightJoin / FullOuterJoin / NaturalJoin / CrossJoin, with typed tuples, OrderBy, projection, and aggregates.

updated 6 Jun 20264 min readv0.3.2View as Markdown

Overview

The generated Query() builder extends into a join against another table. The result is an async stream of typed tuples: (T1? , T2?) for two tables, (T1?, T2?, T3?) for three, up to four tables. Elements are nullable: an outer-join miss yields null for the unmatched side (see Outer joins).

await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user!.Username} last login: {login!.LastSeenAt}");
}

Attach the connection or transaction with .WithConnection() / .WithTransaction(), and materialize with ExecuteAsync() (stream), ToListAsync(), or FirstOrDefaultAsync().

Join types

Method SQL
.Join<TJoin>(on) INNER JOIN
.LeftJoin<TJoin>(on) LEFT JOIN
.RightJoin<TJoin>(on) RIGHT JOIN
.FullOuterJoin<TJoin>(on) FULL OUTER JOIN
.NaturalJoin<TJoin>() NATURAL JOIN (no ON clause)
.CrossJoin<TJoin>() CROSS JOIN (no ON clause)

Each on is an Expression<Func<…, bool>> with one parameter per table joined so far.

Inner join

await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }

Generated SQL. Every column of every table is selected and aliased a{N}_column, with each table aliased a0, a1, and so on:

SELECT
    a0."id" AS a0_id, a0."username" AS a0_username, ...,
    a1."id" AS a1_id, a1."user_id" AS a1_user_id, ...
FROM "users" a0
INNER JOIN "user_logins" a1 ON a0."id" = a1."user_id"

Outer joins

LeftJoin / RightJoin / FullOuterJoin return null for the side that didn't match (not a zeroed-out instance), so you can tell "no match" from a real row of defaults. (Detection uses the table's primary key: a row whose PK comes back NULL is a miss.)

await foreach (var (user, profile) in User.Query()
    .LeftJoin<UserProfile>((u, p) => u.Id == p.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine(profile is null ? $"{user!.Username}: (no profile)" : $"{user!.Username}: {profile.Bio}");
}
NOTE
Because elements are nullable, null-check (or !) the side that can be absent. Inner/cross joins never produce nulls, but the tuple type is nullable for all join kinds.

Filtering the driving table

A predicate on Query() filters the driving table before the join:

await foreach (var (user, login) in User.Query(u => u.IsActive)
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }

Filtering across tables with WHERE

.Where(...) takes one parameter per joined table and references any of them:

await foreach (var (user, login) in User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .Where((u, l) => u.IsActive && l.LastSeenAt > cutoff)
    .WithConnection(conn)
    .ExecuteAsync())
{ ... }

Three and four tables

Chain .Join (and the outer/cross variants) for a third and fourth table; each ON receives a parameter per table joined so far:

var rows = await Order.Query()
    .Join<User>((o, u) => o.UserId == u.Id)
    .Join<Product>((o, u, p) => o.ProductId == p.Id)
    .Where((o, u, p) => p.InStock)
    .WithConnection(conn)
    .ToListAsync();   // List<(Order?, User?, Product?)>

Four tables is the maximum. For five or more, use a .sql procedure.

Sorting

.OrderBy / .OrderByDesc take an object?[] of columns from any joined table:

var rows = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .OrderByDesc((u, l) => new object?[] { l.LastSeenAt })
    .WithConnection(conn)
    .ToListAsync();

Projection to a typed result

.Select(...) maps each tuple into your own type instead of returning (T1?, T2?, …):

var summaries = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .WithConnection(conn)
    .Select((u, l) => new LoginSummary(u!.Username, l!.LastSeenAt))
    .ToListAsync();

The projection runs client-side (a compiled delegate, AOT-safe) over each materialized tuple. It gives you typed results but still fetches every column. The projector's parameters are nullable, so outer-join misses surface as null inside Select too.

Aggregates over a join

Aggregate without materializing rows. CountAsync() returns long; SumAsync/AvgAsync/MinAsync/MaxAsync take a column selector and return T? (null when no rows match):

long n = await User.Query()
    .Join<UserLogin>((u, l) => u.Id == l.UserId)
    .Where((u, l) => u.IsActive)
    .WithConnection(conn)
    .CountAsync();

decimal? total = await Order.Query()
    .Join<User>((o, u) => o.UserId == u.Id)
    .WithConnection(conn)
    .SumAsync<decimal>((o, u) => o.Total);

Limit, Offset, and transactions

.Limit() / .Offset() are available on every join builder, and .WithTransaction(tx) enlists the join in a transaction instead of .WithConnection().

Limitations

  • Up to four tables. For more, use a .sql procedure.
  • Projection is client-side. .Select(...) gives typed results but doesn't reduce the columns fetched (all columns of all tables are still selected).