/DB

Joins

Query across two tables with Join, LeftJoin, RightJoin, FullOuterJoin, NaturalJoin, and CrossJoin.

updated 3 May 20262 min readv0.1.82

Overview

The generated Query() method can be extended with a join to a second table. The result is an async enumerable of (T Left, TJoin Right) value tuples.

Syntax

Call a join method on an existing query builder, then supply a connection and execute:

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}");
}

The join method takes one argument: an expression Expression<Func<T, TJoin, bool>> for the ON condition. The connection (or transaction) is attached separately via .WithConnection() / .WithTransaction().

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)

Inner join

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}");
}

Generated SQL:

SELECT
    t."id" AS t_id, t."username" AS t_username, ...,
    j."id" AS j_id, j."user_id" AS j_user_id, ...
FROM "users" t
INNER JOIN "user_logins" j ON t."id" = j."user_id"

All columns from both tables are selected. The generator aliases them with t_ (main table) and j_ (join table) prefixes to avoid name collisions.

Left join

Use LeftJoin when the joined record may not exist. The TJoin result will be a default instance when there is no match:

await foreach (var (user, profile) in User.Query()
    .LeftJoin<UserProfile>((u, p) => u.Id == p.UserId)
    .WithConnection(conn)
    .ExecuteAsync())
{
    Console.WriteLine($"{user.Username}: {profile?.Bio ?? "(no profile)"}");
}

Filtering the main table before joining

Pass a predicate to Query() to filter the driving table before the join:

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

Filtering across both tables with WHERE

Use .Where() on the join builder to add a condition that references both tables. The expression receives two parameters — one for each table:

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

Limit and Offset

.Limit() and .Offset() are available on the join builder:

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

Using a transaction

Pass a DbTransaction via .WithTransaction() instead of .WithConnection():

await foreach (var (user, order) in User.Query()
    .Join<Order>((u, o) => u.Id == o.UserId)
    .WithTransaction(tx)
    .ExecuteAsync())
{ ... }

Limitations

  • Join queries select all columns from both tables; column projection is not supported on join builders.
  • Only two-table joins are supported. For more complex joins, use Procedure mapping with a raw .sql file.
  • OrderBy / OrderByDesc are not available on the join builder. Sort in C# using LINQ after materializing, or use a procedure.