Link entities in depth: chains, branches, criteria per level, recursion

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.RetrieveMultiple.AdvancedCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new Advanced5_LinkEntitiesInDepth().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.AdvancedCases;

/// <summary>
/// Link entities in depth, in five scenarios. An <see cref="ArxLinkEntity"/> joins one table and holds its own columns,
/// criteria, alias, join type, and its own <see cref="ArxLinkEntity.LinkEntities"/>: a tree of joins, as deep as needed.
/// <list type="number">
/// <item>A chain four joins deep (order line → order → account → parent → grandparent), plus a second branch (product → category), printed by walking the tree.</item>
/// <item>Each level with its own join type and criteria.</item>
/// <item>Conditions and order on aliases deep in the tree, in the main criteria.</item>
/// <item>A self-join chain of ancestors, built by a recursive function.</item>
/// <item>A one-to-many link multiplies rows: aggregated per account.</item>
/// </list>
/// </summary>
public sealed class Advanced5_LinkEntitiesInDepth : ISample
{
    public string Name => "retrieve-multiple-advanced-5-link-entities-in-depth";

    public string Title => "Link entities in depth: chains, branches, criteria per level, recursion";

    public async Task RunAsync(SampleContext dbContext)
    {
        // Accounts in a hierarchy: Shop → Group → Holding; Solo has no parent.
        ArxRecordId? parent = null;
        var accounts = new Dictionary<string, ArxRecordId>();

        foreach (var (number, name) in new[] { ("ACC-R761", "Holding R76"), ("ACC-R762", "Group R76"), ("ACC-R763", "Shop R76") })
        {
            var account = new ArxEntity("account");
            account["name"] = name;
            account["account_number"] = number;
            account["parent_account_id"] = parent == null ? null : new ArxEntityReference("account", parent);
            parent = await dbContext.Service.CreateAsync(account);
            accounts[name] = parent;
        }

        var solo = new ArxEntity("account");
        solo["name"] = "Solo R76";
        solo["account_number"] = "ACC-R764";
        accounts["Solo R76"] = await dbContext.Service.CreateAsync(solo);

        foreach (var (first, account) in new[] { ("Sam", "Shop R76"), ("Sue", "Shop R76"), ("Sol", "Solo R76") })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Links-R76";
            contact["account_id"] = new ArxEntityReference("account", accounts[account]);
            await dbContext.Service.CreateAsync(contact);
        }

        var categories = new Dictionary<string, ArxRecordId>();

        foreach (var name in new[] { "Bikes R76", "Helmets R76" })
        {
            var category = new ArxEntity("product_category");
            category["name"] = name;
            categories[name] = await dbContext.Service.CreateAsync(category);
        }

        var products = new Dictionary<string, ArxRecordId>();

        foreach (var (sku, name, category, price) in new[] { ("SKU-R761", "Road bike", "Bikes R76", 900m), ("SKU-R762", "Kids helmet", "Helmets R76", 35m) })
        {
            var product = new ArxEntity("product");
            product["sku"] = sku;
            product["name"] = name;
            product["category_id"] = new ArxEntityReference("product_category", categories[category]);
            product["unit_price"] = price;
            products[sku] = await dbContext.Service.CreateAsync(product);
        }

        foreach (var (orderNumber, account, lines) in new[]
        {
            ("SO-R761", "Shop R76", new[] { ("SKU-R761", 2), ("SKU-R762", 1) }),
            ("SO-R762", "Solo R76", new[] { ("SKU-R762", 3) }),
        })
        {
            var order = new ArxEntity("sales_order");
            order["order_number"] = orderNumber;
            order["account_id"] = new ArxEntityReference("account", accounts[account]);
            order["ordered_on"] = new DateTime(2026, 11, 8, 10, 0, 0, DateTimeKind.Utc);
            var orderId = await dbContext.Service.CreateAsync(order);

            foreach (var (sku, quantity) in lines)
            {
                var line = new ArxEntity("order_line");
                line["order_id"] = new ArxEntityReference("sales_order", orderId);
                line["product_id"] = new ArxEntityReference("product", products[sku]);
                line["quantity"] = quantity;
                line["unit_price"] = sku == "SKU-R761" ? 900m : 35m;
                await dbContext.Service.CreateAsync(line);
            }
        }

        await Scenario1_AChainFourJoinsDeepAsync(dbContext);

        await Scenario2_EachLevelWithItsOwnCriteriaAsync(dbContext);

        await Scenario3_ConditionsAndOrderOnDeepAliasesAsync(dbContext);

        await Scenario4_AncestorsBuiltRecursivelyAsync(dbContext);

        await Scenario5_AOneToManyLinkAggregatedAsync(dbContext);
    }

    /// <summary>A link with an alias and the columns to read from it.</summary>
    private static ArxLinkEntity Link(string table, string from, string to, ArxJoinOperator join, string alias, params string[] columns)
    {
        var link = new ArxLinkEntity(table, from, to, join) { EntityAlias = alias };
        link.ColumnSet.AddColumns(columns);
        return link;
    }

    /// <summary>
    /// The order lines of this sample with every level of the tree: order, account, parent, grandparent; product, category.
    /// The links a scenario adds criteria to come back through the out parameters.
    /// </summary>
    private static ArxQueryExpression OrderLines(out ArxLinkEntity order, out ArxLinkEntity account, out ArxLinkEntity parent, out ArxLinkEntity category)
    {
        var query = new ArxQueryExpression("order_line");
        query.ColumnSet.AddColumn("quantity");

        // Branch 1: order → account → parent → grandparent (each link nested in the one before).
        order = Link("sales_order", "order_id", "id", ArxJoinOperator.Inner, "ord", "order_number");
        order.LinkCriteria.AddCondition("order_number", ArxConditionOperator.BeginsWith, "SO-R76");
        account = Link("account", "account_id", "id", ArxJoinOperator.Inner, "acc", "name");
        parent = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, "parent", "name");
        var grandparent = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, "grand", "name");
        parent.LinkEntities.Add(grandparent);
        account.LinkEntities.Add(parent);
        order.LinkEntities.Add(account);
        query.LinkEntities.Add(order);

        // Branch 2: product → category.
        var product = Link("product", "product_id", "id", ArxJoinOperator.Inner, "prod", "sku", "name");
        category = Link("product_category", "category_id", "id", ArxJoinOperator.Inner, "cat", "name");
        product.LinkEntities.Add(category);
        query.LinkEntities.Add(product);

        query.AddOrder("ord", "order_number", ArxOrderType.Ascending);
        query.AddOrder("prod", "sku", ArxOrderType.Ascending);
        return query;
    }

    /// <summary>The join tree, printed the way it is built: each link prints its own links, one level deeper.</summary>
    private static void Print(IEnumerable<ArxLinkEntity> links, string parentAlias = "main", int depth = 1)
    {
        foreach (var link in links)
        {
            Show($"{new string(' ', depth * 4)}{link.JoinOperator} JOIN {link.LinkToEntityName} AS {link.EntityAlias} ON {link.EntityAlias}.{link.LinkToAttributeName} = {parentAlias}.{link.LinkFromAttributeName}"
                + (link.LinkCriteria.Conditions.Count + link.LinkCriteria.Filters.Count > 0 ? "  (+ its own criteria)" : string.Empty));
            Print(link.LinkEntities, link.EntityAlias, depth + 1);
        }
    }

    private static string Line(ArxEntity x)
        => $"{x["ord.order_number"]} {x["prod.sku"]} x{x["quantity"]} ({x["cat.name"]}) for {x["acc.name"]} < {x["parent.name"] ?? "-"} < {x["grand.name"] ?? "-"}";

    /// <summary>1. Every line with its order, account, parent and grandparent, and its product and category.</summary>
    private static async Task Scenario1_AChainFourJoinsDeepAsync(SampleContext dbContext)
    {
        Step("1. A chain four joins deep, and a second branch");

        var query = OrderLines(out _, out _, out _, out _);
        Print(query.LinkEntities);

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(query)).Entities)
        {
            Show(Line(row));
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT o.order_number, p.sku, l.quantity, c.name, a.name, pa.name, ga.name
         * FROM order_line l
         * JOIN sales_order o ON o.id = l.order_id AND o.order_number LIKE 'SO-R76%'
         * JOIN account a ON a.id = o.account_id
         * LEFT JOIN account pa ON pa.id = a.parent_account_id
         * LEFT JOIN account ga ON ga.id = pa.parent_account_id
         * JOIN product p ON p.id = l.product_id
         * JOIN product_category c ON c.id = p.category_id
         * ORDER BY o.order_number, p.sku;
         * -- Expect: 3 rows: SO-R761 SKU-R761 2 Bikes R76 Shop R76 Group R76 Holding R76; SO-R761 SKU-R762 1 Helmets R76 Shop R76 Group R76 Holding R76; SO-R762 SKU-R762 3 Helmets R76 Solo R76 NULL NULL.
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. Criteria at two depths: the category (an INNER join, so it filters the lines) must be helmets; the parent (a
    /// LEFT join, so it filters nothing) must begin with "Group", or it reads as empty.
    /// </summary>
    private static async Task Scenario2_EachLevelWithItsOwnCriteriaAsync(SampleContext dbContext)
    {
        Step("2. Each level with its own criteria: INNER filters, LEFT keeps");

        var query = OrderLines(out _, out _, out var parent, out var category);
        category.LinkCriteria.AddCondition("name", ArxConditionOperator.Equal, "Helmets R76");
        parent.LinkCriteria.AddCondition("name", ArxConditionOperator.BeginsWith, "Group");

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(query)).Entities)
        {
            Show(Line(row));
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT o.order_number, p.sku, l.quantity, a.name, pa.name
         * FROM order_line l
         * JOIN sales_order o ON o.id = l.order_id AND o.order_number LIKE 'SO-R76%'
         * JOIN account a ON a.id = o.account_id
         * LEFT JOIN account pa ON pa.id = a.parent_account_id AND pa.name LIKE 'Group%'
         * JOIN product p ON p.id = l.product_id
         * JOIN product_category c ON c.id = p.category_id AND c.name = 'Helmets R76'
         * ORDER BY o.order_number;
         * -- Expect: 2 rows: SO-R761 SKU-R762 1 Shop R76 Group R76; SO-R762 SKU-R762 3 Solo R76 NULL.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. The main criteria and order can name any alias in the tree: the lines of accounts whose grandparent is "Holding
    /// R76", the biggest quantity first. A condition on a LEFT-joined alias in the main criteria drops the rows where it is
    /// NULL (WHERE runs after the joins); in the link's own criteria it wouldn't.
    /// </summary>
    private static async Task Scenario3_ConditionsAndOrderOnDeepAliasesAsync(SampleContext dbContext)
    {
        Step("3. Conditions and order on aliases deep in the tree");

        var query = OrderLines(out _, out _, out _, out _);
        query.Criteria.AddCondition("grand.name", ArxConditionOperator.Equal, "Holding R76");
        query.Orders.Clear();
        query.AddOrder("quantity", ArxOrderType.Descending);

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(query)).Entities)
        {
            Show(Line(row));
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT p.sku, l.quantity
         * FROM order_line l
         * JOIN sales_order o ON o.id = l.order_id
         * JOIN account a ON a.id = o.account_id
         * LEFT JOIN account pa ON pa.id = a.parent_account_id
         * LEFT JOIN account ga ON ga.id = pa.parent_account_id
         * JOIN product p ON p.id = l.product_id
         * WHERE o.order_number LIKE 'SO-R76%' AND ga.name = 'Holding R76'
         * ORDER BY l.quantity DESC;
         * -- Expect: 2 rows: SKU-R761 2, SKU-R762 1 (Solo has no grandparent).
         * ===========================================================
         */
    }

    /// <summary>The account's ancestors, <paramref name="levels"/> deep: each level is a link nested in the one before, built by the same function.</summary>
    private static ArxLinkEntity Ancestors(int level, int levels)
    {
        var link = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, $"up{level}", "name");

        if (level < levels)
        {
            link.LinkEntities.Add(Ancestors(level + 1, levels));
        }

        return link;
    }

    /// <summary>4. A self-join chain built recursively: Shop's parent, grandparent and great-grandparent (there is none).</summary>
    private static async Task Scenario4_AncestorsBuiltRecursivelyAsync(SampleContext dbContext)
    {
        Step("4. Ancestors, built by a recursive function");

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumn("name");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-R763");
        query.LinkEntities.Add(Ancestors(1, 3));
        Print(query.LinkEntities);

        var shop = (await dbContext.Service.RetrieveMultipleAsync(query)).Entities[0];
        Show($"{shop["name"]} < {shop["up1.name"]} < {shop["up2.name"]} < {shop["up3.name"] ?? "(none)"}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT a.name, u1.name, u2.name, u3.name
         * FROM account a
         * LEFT JOIN account u1 ON u1.id = a.parent_account_id
         * LEFT JOIN account u2 ON u2.id = u1.parent_account_id
         * LEFT JOIN account u3 ON u3.id = u2.parent_account_id
         * WHERE a.account_number = 'ACC-R763';
         * -- Expect: 1 row: Shop R76, Group R76, Holding R76, NULL.
         * ===========================================================
         */
    }

    /// <summary>
    /// 5. A link to the many side (account → its contacts) gives one row per contact. Aggregated: contacts per account,
    /// counting the linked ids (accounts without contacts count 0, kept by the LEFT join).
    /// </summary>
    private static async Task Scenario5_AOneToManyLinkAggregatedAsync(SampleContext dbContext)
    {
        Step("5. A one-to-many link, aggregated per account");

        var query = new ArxQueryExpression("account") { AggregateInfo = new ArxAggregateInfo() };
        query.ColumnSet.AddColumn("name");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R76");
        query.LinkEntities.Add(Link("contact", "id", "account_id", ArxJoinOperator.Left, "c"));
        query.AggregateInfo.AddAggregateColumn("contacts", "c.id", ArxAggregateOperator.Count);
        query.AggregateInfo.AddGroupByColumn("name");
        query.AddOrder("contacts", ArxOrderType.Descending);
        query.AddOrder("name", ArxOrderType.Ascending);

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(query)).Entities)
        {
            Show($"{row["name"],-12} {row["contacts"]} contacts");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT a.name, count(c.id) AS contacts
         * FROM account a
         * LEFT JOIN contact c ON c.account_id = a.id
         * WHERE a.account_number LIKE 'ACC-R76%'
         * GROUP BY a.name
         * ORDER BY contacts DESC, a.name;
         * -- Expect: 4 rows: Shop R76 2, Solo R76 1, Group R76 0, Holding R76 0.
         * ===========================================================
         */
    }
}

What every sample is given: the connection string (and the ISample interface the samples implement).

namespace ArxSamples.MSSQL;

/// <summary>One documentation sample: a scenario a real application has, shown end to end.</summary>
public interface ISample
{
    /// <summary>The name to run it alone: <c>dotnet run -- &lt;name&gt;</c>.</summary>
    string Name { get; }

    /// <summary>One line on what it shows.</summary>
    string Title { get; }

    Task RunAsync(SampleContext dbContext);
}

/// <summary>
/// What a sample is given. Every sample gets the connection string; the application's service and catalog
/// (SampleContext.Application.cs) and the plain SqlClient read-back helper (SampleContext.Direct.cs) live in their own
/// files, so a sample needs only the files it uses.
/// </summary>
public sealed partial class SampleContext
{
    public SampleContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }
}

How the samples print: Step for each scenario, Show for what it did, ShowError for an error caught on purpose.

using ARX.SDK;

namespace ArxSamples.MSSQL;

/// <summary>How the samples print: a numbered step for each scenario, then what it did.</summary>
public static class SampleOutput
{
    /// <summary>The start of a scenario: <c>Step("1. By the id Create returned")</c>.</summary>
    public static void Step(string text) => Console.WriteLine($"  > {text}");

    /// <summary>What the scenario did.</summary>
    public static void Show(string text) => Console.WriteLine($"      {text}");

    /// <summary>An ARX error the scenario caught on purpose.</summary>
    public static void ShowError(ArxException exception)
        => Console.WriteLine($"      caught {exception.GetType().Name}: {exception.Message}");
}

The application's ARX service and its schema catalog, created once and shared by the samples.

using ARX.SDK.SQL.Services;
using ARX.SDK.Services;

namespace ArxSamples.MSSQL;

/// <summary>The application's ARX service and its schema catalog, shared by the real and advanced samples.</summary>
public sealed partial class SampleContext
{
    private ArxSchemaCatalog? _catalog;
    private ArxMsSqlDbService? _service;

    /// <summary>The application's schema catalog: table ids, keys and selection sets (SampleCatalog.cs).</summary>
    public ArxSchemaCatalog Catalog => _catalog ??= SampleCatalog.Build();

    /// <summary>
    /// One service for the whole application. It holds no connection: every call opens one from the SqlClient pool and
    /// closes it, unless you pass your own connection or transaction.
    /// </summary>
    public ArxMsSqlDbService Service => _service ??= new ArxMsSqlDbService(ConnectionString, Catalog);
}

The application's schema catalog: every table's id column and keys, and its selection sets.

using ARX.SDK.Services;
using System.Text.Json;
using System.Text.Json.Serialization;

namespace ArxSamples.MSSQL;

/// <summary>An account's industry. Stored in account.industry (smallint) as the enum's number.</summary>
public enum Industry : short
{
    Retail = 1,
    Manufacturing = 2,
    Healthcare = 3,
    Finance = 4,
    Technology = 5
}

/// <summary>
/// The application's schema catalog: every table's id column and keys, and every picklist (selection set),
/// defined once.
/// </summary>
/// <remarks>
/// <para>
/// <b>Ids.</b> Every table the application writes is declared with its one id column: its name, its .NET type, and
/// whether the database generates it (<c>ArxIdColumn.Generated</c>: identity, sequence, default) or the caller supplies
/// it (<c>ArxIdColumn.Supplied</c>: a natural id, a client-generated Guid). A table with no id column, such as a log or a
/// table identified by several columns together, is declared <c>ArxIdColumn.NoIdColumn</c>. ARX never reads ids from
/// the database: the developer knows the schema, and an undeclared table is refused (ARX1016).
/// </para>
/// <para>
/// <b>Keys.</b> Optional business keys: one or more columns whose values identify a record, such as an account number,
/// or a region and a SKU together. Create doesn't use them (the database's unique constraints do the enforcing); Update
/// and Delete will be able to find rows by them. <c>ValidateSchemaAsync</c> checks ids and keys against the database
/// when you ask it to.
/// </para>
/// <para>
/// <b>Selection sets.</b> /// A selection set (picklist, option set) is a column that only accepts a fixed list of codes, each with a display
/// text: an account's status (ACTIVE, ON_HOLD, CLOSED), a contact's preferred channel (E, P, S). The catalog tells ARX,
/// for each set:
/// </para>
/// <list type="bullet">
/// <item><b>Name</b>: what an <see cref="ArxSelectionSetValue"/> refers to, for example <c>new ArxSelectionSetValue("AccountStatus", "ON_HOLD")</c>.</item>
/// <item><b>Kind</b>: what the codes are, matching the column: Integer, String, Character, Boolean, Decimal or Guid.
/// (SQL Server has no enum types: a fixed list of text values is a String set, and a CHECK can guard the column.)</item>
/// <item><b>Multiplicity</b>: Single (one code), or several codes stored as bit flags (Flags), a delimited string
/// (Delimited) or a JSON array (Json). SQL Server has no array columns.</item>
/// <item><b>Options</b>: each code with its display text and order. A retired option (<c>IsActive: false</c>) stays
/// readable in old rows, but can't be written any more.</item>
/// </list>
/// <para>
/// Build the catalog once at startup and give it to the service: <c>new ArxMsSqlDbService(connectionString, SampleCatalog.Build())</c>.
/// ARX checks every <see cref="ArxSelectionSetValue"/> against it in memory, before any SQL is sent, and stores the code
/// itself in the column: no lookup table, no extra query. A plain value (a string or number that isn't an
/// ArxSelectionSetValue) isn't checked.
/// </para>
/// <para>
/// The catalog is plain code, so the options can come from wherever you keep them: written out here, taken from a .NET
/// enum (<c>AddEnum</c>), loaded from JSON (<see cref="SetsJson"/> and <see cref="FromJson"/> below), or loaded at
/// startup from your own option table and passed to <c>new ArxSelectionSetDefinition(name, kind, options)</c>. ARX
/// never reads option tables or files itself.
/// </para>
/// </remarks>
public static class SampleCatalog
{
    /// <summary>
    /// Selection sets kept as JSON, as an application might keep them in a file, a settings store or a database column.
    /// Here the JSON is in the class itself; in an application, read it from wherever you keep it
    /// (<c>File.ReadAllText("selection-sets.json")</c>, configuration, an HTTP call) and pass it to <see cref="FromJson"/>.
    /// "multiplicity" and "active" are optional (Single and true).
    /// </summary>
    public const string SetsJson = """
        [
          {
            "name": "LeadSource",
            "kind": "String",
            "options": [
              { "value": "WEB",       "text": "Website",    "order": 0 },
              { "value": "REFERRAL",  "text": "Referral",   "order": 1 },
              { "value": "EVENT",     "text": "Trade show", "order": 2 },
              { "value": "COLD_CALL", "text": "Cold call",  "order": 3, "active": false }
            ]
          },
          {
            "name": "ContactRating",
            "kind": "Integer",
            "multiplicity": "Single",
            "options": [
              { "value": 1, "text": "Poor" },
              { "value": 2, "text": "Fair" },
              { "value": 3, "text": "Good" },
              { "value": 4, "text": "Very good" },
              { "value": 5, "text": "Excellent" }
            ]
          }
        ]
        """;

    public static ArxSchemaCatalog Build()
    {
        var builder = ArxSchemaCatalog.Create()

            // ---------- tables: id column and keys ----------
            // Generated bigint ids (IDENTITY): the database fills them in. The keys are the unique columns that also
            // identify a record.
            .Table("account", ArxIdColumn.Generated<long>("id")).Key("account", "account_number")
            .Table("contact", ArxIdColumn.Generated<long>("id")).Key("contact", "email")

            // Retrieve: a contact's account_id reads back as a reference to its account, named by the account's
            // name; a contact is named by its first and last names.
            .Lookup("contact", "account_id", "account")
            .DisplayName("account", "name")
            .DisplayName("contact", new[] { "first_name", "last_name" }, " ")

            .Table("product_category", ArxIdColumn.Generated<long>("id")).Key("product_category", "name")
            .Table("product", ArxIdColumn.Generated<long>("id")).Key("product", "sku")
            .Table("sales_order", ArxIdColumn.Generated<long>("id")).Key("sales_order", "order_number")
            .Table("order_line", ArxIdColumn.Generated<long>("id"))
            .Table("data_type_showcase", ArxIdColumn.Generated<long>("id"))

            // A natural id: the caller gives the ISO country code.
            .Table("country", ArxIdColumn.Supplied<string>("code"))

            // A generated uuid id (DEFAULT gen_random_uuid()).
            .Table("device", ArxIdColumn.Generated<Guid>("id")).Key("device", "serial_number")

            // No id column: a price is identified by its region and SKU together, a compound key.
            .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")

            // No primary key at all: the same action can be logged twice.
            .Table("activity_log", ArxIdColumn.NoIdColumn)

            // A partitioned table without an id: a visit is a fact, never updated (Retrieve Advanced8).
            .Table("web_visit", ArxIdColumn.NoIdColumn)

            // ---------- selection sets ----------

            // From a .NET enum: the set is named after the enum ("Industry"), the codes are its numbers (Retail = 1, …),
            // the display texts are its member names. Column: account.industry smallint.
            .AddEnum<Industry>()

            // Written out: String codes with display texts, single choice. Column: account.status varchar(10).
            .Add("AccountStatus", ArxSelectionSetKind.String, ("ACTIVE", "Active"), ("ON_HOLD", "On hold"), ("CLOSED", "Closed"))

            // Multiple choice, stored as delimited text ("ENTERPRISE,PUBLIC"). RESELLER is retired: rows that have it still
            // read back, but new writes can't use it. Column: account.segments nvarchar(200).
            .Add(new ArxSelectionSetDefinition("CustomerSegment", ArxSelectionSetKind.String, new[]
            {
                new ArxSelectionSetOption("SMB", "Small business", 0),
                new ArxSelectionSetOption("ENTERPRISE", "Enterprise", 1),
                new ArxSelectionSetOption("PUBLIC", "Public sector", 2),
                new ArxSelectionSetOption("RESELLER", "Reseller", 3, IsActive: false)
            }, ArxSelectionSetMultiplicity.Delimited))

            // Single-character codes. Column: contact.preferred_channel char(1).
            .Add("ContactChannel", ArxSelectionSetKind.Character, ('E', "Email"), ('P', "Phone"), ('S', "Text message"))

            // A fixed list of text values the column's CHECK also guards (SQL Server has no enum types).
            // Column: data_type_showcase.mood nvarchar(10).
            .Add(new ArxSelectionSetDefinition("Mood", ArxSelectionSetKind.String, new[]
            {
                new ArxSelectionSetOption("happy", "Happy", 0),
                new ArxSelectionSetOption("neutral", "Neutral", 1),
                new ArxSelectionSetOption("sad", "Sad", 2)
            }));

        // Loaded from JSON: LeadSource (contact.lead_source text) and ContactRating (contact.rating smallint).
        foreach (var set in FromJson(SetsJson))
        {
            builder.Add(set);
        }

        return builder.Build();
    }

    /// <summary>Reads selection set definitions from JSON (the shape of <see cref="SetsJson"/>).</summary>
    public static IEnumerable<ArxSelectionSetDefinition> FromJson(string json)
    {
        var options = new JsonSerializerOptions
        {
            PropertyNameCaseInsensitive = true,
            Converters = { new JsonStringEnumConverter() }   // "String", "Integer", "Array", … as names
        };

        var sets = JsonSerializer.Deserialize<List<JsonSet>>(json, options)
            ?? throw new InvalidOperationException("The selection set JSON is empty.");

        foreach (var set in sets)
        {
            yield return new ArxSelectionSetDefinition(
                set.Name,
                set.Kind,
                set.Options.Select(x => new ArxSelectionSetOption(Code(x.Value, set.Kind), x.Text, x.Order, x.Active)),
                set.Multiplicity,
                set.DatabaseTypeName);
        }
    }

    /// <summary>A JSON value as a code of the set's kind: JSON has no char, Guid or 64-bit integer of its own.</summary>
    private static object Code(JsonElement value, ArxSelectionSetKind kind) => kind switch
    {
        ArxSelectionSetKind.Integer => value.GetInt64(),
        ArxSelectionSetKind.Decimal => value.GetDecimal(),
        ArxSelectionSetKind.Boolean => value.GetBoolean(),
        ArxSelectionSetKind.Guid => value.GetGuid(),
        ArxSelectionSetKind.Character => value.GetString()![0],
        _ => value.GetString()!   // String and DatabaseEnum
    };

    private sealed record JsonSet(
        string Name,
        ArxSelectionSetKind Kind,
        List<JsonOption> Options,
        ArxSelectionSetMultiplicity Multiplicity = ArxSelectionSetMultiplicity.Single,
        string? DatabaseTypeName = null);

    private sealed record JsonOption(JsonElement Value, string Text, int Order = 0, bool Active = true);
}

  > 1. A chain four joins deep, and a second branch
          Inner JOIN sales_order AS ord ON ord.id = main.order_id  (+ its own criteria)
              Inner JOIN account AS acc ON acc.id = ord.account_id
                  Left JOIN account AS parent ON parent.id = acc.parent_account_id
                      Left JOIN account AS grand ON grand.id = parent.parent_account_id
          Inner JOIN product AS prod ON prod.id = main.product_id
              Inner JOIN product_category AS cat ON cat.id = prod.category_id
      SO-R761 SKU-R761 x2 (Bikes R76) for Shop R76 < Group R76 < Holding R76
      SO-R761 SKU-R762 x1 (Helmets R76) for Shop R76 < Group R76 < Holding R76
      SO-R762 SKU-R762 x3 (Helmets R76) for Solo R76 < - < -
  > 2. Each level with its own criteria: INNER filters, LEFT keeps
      SO-R761 SKU-R762 x1 (Helmets R76) for Shop R76 < Group R76 < Holding R76
      SO-R762 SKU-R762 x3 (Helmets R76) for Solo R76 < - < -
  > 3. Conditions and order on aliases deep in the tree
      SO-R761 SKU-R761 x2 (Bikes R76) for Shop R76 < Group R76 < Holding R76
      SO-R761 SKU-R762 x1 (Helmets R76) for Shop R76 < Group R76 < Holding R76
  > 4. Ancestors, built by a recursive function
          Left JOIN account AS up1 ON up1.id = main.parent_account_id
              Left JOIN account AS up2 ON up2.id = up1.parent_account_id
                  Left JOIN account AS up3 ON up3.id = up2.parent_account_id
      Shop R76 < Group R76 < Holding R76 < (none)
  > 5. A one-to-many link, aggregated per account
      Shop R76     2 contacts
      Solo R76     1 contacts
      Group R76    0 contacts
      Holding R76  0 contacts