Filter expressions in depth: nesting, recursion, links, And and Or

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.RetrieveMultiple.AdvancedCases;

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

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

The sample, as it is in the repository.

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

namespace ArxSamples.MySQL.Operations.RetrieveMultiple.AdvancedCases;

/// <summary>
/// Filter expressions in depth, in five scenarios. An <see cref="ArxFilterExpression"/> holds conditions and other filter
/// expressions, each with its own operator (And, Or): a tree, as deep as you need. Each child filter becomes one
/// parenthesised group in the SQL, so the tree is the precedence.
/// <list type="number">
/// <item>A tree four levels deep, built by hand and printed by walking it recursively.</item>
/// <item>A saved search (a nested rule from a query builder screen) turned into filters by a recursive function.</item>
/// <item>A link entity with its own nested criteria, applied in the join.</item>
/// <item>Two filters built apart (the user's search and a security rule) joined under And, then under Or.</item>
/// <item>The same three conditions grouped two ways: And and Or change the result.</item>
/// </list>
/// </summary>
public sealed class Advanced4_FilterExpressionsInDepth : ISample
{
    public string Name => "retrieve-multiple-advanced-4-filter-expressions-in-depth";

    public string Title => "Filter expressions in depth: nesting, recursion, links, And and Or";

    private const string LAST_NAME = "Filter-R751";

    public async Task RunAsync(SampleContext dbContext)
    {
        var accountIds = new Dictionary<string, ArxRecordId>();

        foreach (var (number, name, industry, revenue, status) in new[]
        {
            ("ACC-R751", "Filter Retail", Industry.Retail, 500_000m, "ACTIVE"),
            ("ACC-R752", "Filter Bank", Industry.Finance, 5_000_000m, "ACTIVE"),
            ("ACC-R753", "Filter Closed", Industry.Technology, 2_000_000m, "CLOSED"),
        })
        {
            var account = new ArxEntity("account");
            account["name"] = name;
            account["account_number"] = number;
            account["industry"] = new ArxSelectionSetValue("Industry", industry);
            account["annual_revenue"] = revenue;
            account["status"] = new ArxSelectionSetValue("AccountStatus", status);
            accountIds[number] = await dbContext.Service.CreateAsync(account);
        }

        var contacts = new List<ArxEntity>();

        foreach (var (first, title, channel, doNotEmail, account) in new (string, string, char, bool, string?)[]
        {
            ("Ana", "Sales Manager", 'E', false, "ACC-R751"),
            ("Ben", "Engineer", 'P', false, "ACC-R752"),
            ("Cleo", "Account Manager", 'S', true, "ACC-R752"),
            ("Dan", "Buyer", 'E', true, "ACC-R753"),
            ("Zoe", "Intern", 'P', false, null),
            ("Zack", "Store Manager", 'E', false, "ACC-R753"),
        })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = LAST_NAME;
            contact["job_title"] = title;
            contact["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", channel);
            contact["do_not_email"] = doNotEmail;
            contact["account_id"] = account == null ? null : new ArxEntityReference("account", accountIds[account]);
            contacts.Add(contact);
        }

        await dbContext.Service.CreateMultipleAsync(contacts);

        await Scenario1_ATreeFourLevelsDeepAsync(dbContext);

        await Scenario2_ASavedSearchBuiltRecursivelyAsync(dbContext);

        await Scenario3_ALinkEntityWithItsOwnFilterAsync(dbContext);

        await Scenario4_JoiningTwoFiltersAsync(dbContext);

        await Scenario5_AndOrChangeTheResultAsync(dbContext);
    }

    /// <summary>This sample's contacts, by first name; the scenarios add their filters to <see cref="ArxQueryExpression.Criteria"/>.</summary>
    private static ArxQueryExpression Contacts()
    {
        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumns("first_name", "job_title");
        query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, LAST_NAME);
        query.AddOrder("first_name", ArxOrderType.Ascending);
        return query;
    }

    private static async Task<string> FirstNamesAsync(SampleContext dbContext, ArxQueryExpression query)
        => string.Join(", ", (await dbContext.Service.RetrieveMultipleAsync(query)).Entities.Select(x => x["first_name"]));

    /// <summary>
    /// The filter tree, printed the way it is built: a filter has conditions and child filters, and each child is printed
    /// by the same method, one level deeper. That recursion is all there is to the nesting.
    /// </summary>
    private static void Print(ArxFilterExpression filter, int depth = 0)
    {
        var indent = new string(' ', depth * 4);
        Show($"{indent}{filter.FilterOperator.ToString().ToUpperInvariant()} (level {depth + 1})");

        foreach (var condition in filter.Conditions)
        {
            Show($"{indent}    {condition.AttributeName} {condition.Operator} {string.Join(", ", condition.Values.Select(v => v is ArxSelectionSetValue choice ? $"{choice.SetName} {choice.Value}" : v))}");
        }

        foreach (var child in filter.Filters)
        {
            Print(child, depth + 1);
        }
    }

    /// <summary>
    /// 1. last_name AND ( (title contains "manager" AND (channel = E OR do_not_email = false))
    ///                     OR (first name begins with "Z" AND (title = "Intern" OR title contains "store")) ).
    /// </summary>
    private static async Task Scenario1_ATreeFourLevelsDeepAsync(SampleContext dbContext)
    {
        Step("1. A tree four levels deep");

        var query = Contacts();

        var either = query.Criteria.AddFilter(ArxLogicalOperator.Or);               // level 2

        var managers = either.AddFilter(ArxLogicalOperator.And);                     // level 3
        managers.AddCondition("job_title", ArxConditionOperator.Contains, "manager");
        var reachable = managers.AddFilter(ArxLogicalOperator.Or);                   // level 4
        reachable.AddCondition("preferred_channel", ArxConditionOperator.Equal, "E");
        reachable.AddCondition("do_not_email", ArxConditionOperator.Equal, false);

        var zNames = either.AddFilter(ArxLogicalOperator.And);                       // level 3
        zNames.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
        var juniorOrStore = zNames.AddFilter(ArxLogicalOperator.Or);                 // level 4
        juniorOrStore.AddCondition("job_title", ArxConditionOperator.Equal, "Intern");
        juniorOrStore.AddCondition("job_title", ArxConditionOperator.Contains, "store");

        Print(query.Criteria);
        Show($"found: {await FirstNamesAsync(dbContext, query)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name
         * FROM contact
         * WHERE last_name = 'Filter-R751'
         *   AND ( (job_title LIKE '%manager%' AND (preferred_channel = 'E' OR do_not_email = 0))
         *      OR (first_name LIKE 'Z%' AND (job_title = 'Intern' OR job_title LIKE '%store%')) )
         * ORDER BY first_name;
         * -- Expect: 3 rows, Ana, Zack, Zoe (Cleo is a manager, but neither by e-mail nor willing to get one).
         * ===========================================================
         */
    }

    /// <summary>A rule from a "build your own search" screen: a group (And/Or) of conditions and groups, saved as data.</summary>
    private sealed record Rule(ArxLogicalOperator Operator, (string Attribute, ArxConditionOperator Operator, object Value)[] Conditions, Rule[] Groups);

    /// <summary>The rule as a filter expression: the conditions, then each group by the same function, as deep as the rule goes.</summary>
    private static ArxFilterExpression ToFilter(Rule rule)
    {
        var filter = new ArxFilterExpression(rule.Operator);

        foreach (var (attribute, op, value) in rule.Conditions)
        {
            filter.AddCondition(attribute, op, value);
        }

        foreach (var group in rule.Groups)
        {
            filter.AddFilter(ToFilter(group));
        }

        return filter;
    }

    /// <summary>
    /// 2. A saved search five levels deep: (manager AND (e-mail OR (do-not-email AND (first name B… OR C…)))) OR first name
    /// is Dan. The recursive ToFilter turns any depth into filters.
    /// </summary>
    private static async Task Scenario2_ASavedSearchBuiltRecursivelyAsync(SampleContext dbContext)
    {
        Step("2. A saved search, built recursively");

        var none = Array.Empty<Rule>();
        var saved = new Rule(ArxLogicalOperator.Or,
            new[] { ("first_name", ArxConditionOperator.Equal, (object)"Dan") },
            new[]
            {
                new Rule(ArxLogicalOperator.And,
                    new[] { ("job_title", ArxConditionOperator.Contains, (object)"manager") },
                    new[]
                    {
                        new Rule(ArxLogicalOperator.Or,
                            new[] { ("preferred_channel", ArxConditionOperator.Equal, (object)"E") },
                            new[]
                            {
                                new Rule(ArxLogicalOperator.And,
                                    new[] { ("do_not_email", ArxConditionOperator.Equal, (object)true) },
                                    new[]
                                    {
                                        new Rule(ArxLogicalOperator.Or,
                                            new[] { ("first_name", ArxConditionOperator.BeginsWith, (object)"B"), ("first_name", ArxConditionOperator.BeginsWith, (object)"C") },
                                            none),
                                    }),
                            }),
                    }),
            });

        var query = Contacts();
        query.Criteria.AddFilter(ToFilter(saved));

        Print(query.Criteria);
        Show($"found: {await FirstNamesAsync(dbContext, query)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name
         * FROM contact
         * WHERE last_name = 'Filter-R751'
         *   AND ( first_name = 'Dan'
         *      OR (job_title LIKE '%manager%'
         *          AND (preferred_channel = 'E'
         *               OR (do_not_email = 1 AND (first_name LIKE 'B%' OR first_name LIKE 'C%')))) )
         * ORDER BY first_name;
         * -- Expect: 4 rows, Ana, Cleo, Dan, Zack (Cleo through the deepest group).
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. A link entity with its own filter tree: contacts of accounts that are Retail, OR (a million or more AND active).
    /// The link's criteria go in the join (<c>ON … AND (…)</c>); on an INNER join they also drop the contacts whose account
    /// doesn't match.
    /// </summary>
    private static async Task Scenario3_ALinkEntityWithItsOwnFilterAsync(SampleContext dbContext)
    {
        Step("3. A link entity with its own filter");

        var query = Contacts();
        var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Inner);
        account.EntityAlias = "acc";
        account.ColumnSet.AddColumn("name");

        // The link's filter is an ArxFilterExpression too: its conditions refer to the account's columns.
        account.LinkCriteria = new ArxFilterExpression(ArxLogicalOperator.Or);
        account.LinkCriteria.AddCondition("industry", ArxConditionOperator.Equal, new ArxSelectionSetValue("Industry", Industry.Retail));
        var bigAndActive = account.LinkCriteria.AddFilter(ArxLogicalOperator.And);
        bigAndActive.AddCondition("annual_revenue", ArxConditionOperator.GreaterEqual, 1_000_000m);
        bigAndActive.AddCondition("status", ArxConditionOperator.Equal, "ACTIVE");

        Print(account.LinkCriteria);
        var rows = await dbContext.Service.RetrieveMultipleAsync(query);
        Show($"found: {string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]} ({x["acc.name"]})"))}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.first_name, a.name
         * FROM contact c
         * JOIN account a ON a.id = c.account_id
         *  AND (a.industry = 1 OR (a.annual_revenue >= 1000000 AND a.status = 'ACTIVE'))
         * WHERE c.last_name = 'Filter-R751'
         * ORDER BY c.first_name;
         * -- Expect: 3 rows, Ana (Filter Retail), Ben (Filter Bank), Cleo (Filter Bank).
         * ===========================================================
         */
    }

    /// <summary>
    /// 4. Two filters built apart and joined: the user's search (first name A… OR Z…) and a security rule (the account isn't
    /// closed, OR there is no account). The rule refers to the joined account as <c>acc.status</c>. Joined under the
    /// criteria (And): both must hold. Joined in an Or group: either is enough.
    /// </summary>
    private static async Task Scenario4_JoiningTwoFiltersAsync(SampleContext dbContext)
    {
        Step("4. Two filters joined: under And, then under Or");

        ArxFilterExpression UserSearch()
        {
            var search = new ArxFilterExpression(ArxLogicalOperator.Or);
            search.AddCondition("first_name", ArxConditionOperator.BeginsWith, "A");
            search.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
            return search;
        }

        ArxFilterExpression SecurityRule()
        {
            // NotEqual skips NULL (SQL's rule), so "no account" is asked for on its own.
            var rule = new ArxFilterExpression(ArxLogicalOperator.Or);
            rule.AddCondition("acc.status", ArxConditionOperator.NotEqual, "CLOSED");
            rule.AddCondition("acc.id", ArxConditionOperator.Null);
            return rule;
        }

        ArxQueryExpression WithAccount()
        {
            var query = Contacts();
            query.AddLink("account", "account_id", "id", ArxJoinOperator.Left).EntityAlias = "acc";
            return query;
        }

        var both = WithAccount();
        both.Criteria.AddFilter(UserSearch());
        both.Criteria.AddFilter(SecurityRule());
        Show($"search AND rule: {await FirstNamesAsync(dbContext, both)}");

        var either = WithAccount();
        var orGroup = either.Criteria.AddFilter(ArxLogicalOperator.Or);
        orGroup.AddFilter(UserSearch());
        orGroup.AddFilter(SecurityRule());
        Show($"search OR rule:  {await FirstNamesAsync(dbContext, either)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.first_name,
         *        (c.first_name LIKE 'A%' OR c.first_name LIKE 'Z%') AS search,
         *        (a.status <> 'CLOSED' OR a.id IS NULL) AS rule
         * FROM contact c
         * LEFT JOIN account a ON a.id = c.account_id
         * WHERE c.last_name = 'Filter-R751'
         * ORDER BY c.first_name;
         * -- Expect: 6 rows; search AND rule true for Ana, Zoe; search OR rule true for Ana, Ben, Cleo, Zack, Zoe (Dan: neither).
         * ===========================================================
         */
    }

    /// <summary>5. The same three conditions, two groupings: (e-mail AND willing) OR Z…, against e-mail AND (willing OR Z…).</summary>
    private static async Task Scenario5_AndOrChangeTheResultAsync(SampleContext dbContext)
    {
        Step("5. And and Or: the grouping is the meaning");

        var first = Contacts();
        var any = first.Criteria.AddFilter(ArxLogicalOperator.Or);
        var emailAndWilling = any.AddFilter(ArxLogicalOperator.And);
        emailAndWilling.AddCondition("preferred_channel", ArxConditionOperator.Equal, "E");
        emailAndWilling.AddCondition("do_not_email", ArxConditionOperator.Equal, false);
        any.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
        Show($"(e-mail AND willing) OR Z…: {await FirstNamesAsync(dbContext, first)}");

        var second = Contacts();
        second.Criteria.AddCondition("preferred_channel", ArxConditionOperator.Equal, "E");
        var willingOrZ = second.Criteria.AddFilter(ArxLogicalOperator.Or);
        willingOrZ.AddCondition("do_not_email", ArxConditionOperator.Equal, false);
        willingOrZ.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
        Show($"e-mail AND (willing OR Z…): {await FirstNamesAsync(dbContext, second)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name,
         *        ((preferred_channel = 'E' AND do_not_email = 0) OR first_name LIKE 'Z%') AS first_grouping,
         *        (preferred_channel = 'E' AND (do_not_email = 0 OR first_name LIKE 'Z%')) AS second_grouping
         * FROM contact
         * WHERE last_name = 'Filter-R751'
         * ORDER BY first_name;
         * -- Expect: 6 rows; first grouping true for Ana, Zack, Zoe; second for Ana, Zack (Zoe prefers the phone).
         * ===========================================================
         */
    }
}

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

namespace ArxSamples.MySQL;

/// <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 MySqlConnector 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.MySQL;

/// <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.MySQL.Services;
using ARX.SDK.Services;

namespace ArxSamples.MySQL;

/// <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 ArxMySqlDbService? _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 MySqlConnector pool and
    /// closes it, unless you pass your own connection or transaction.
    /// </summary>
    public ArxMySqlDbService Service => _service ??= new ArxMySqlDbService(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.MySQL;

/// <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.
/// (A fixed list of text values is a String set, whether the column is an ENUM or a VARCHAR that a CHECK guards.)</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). MySQL 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 ArxMySqlDbService(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 (AUTO_INCREMENT): 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 Guid id the application gives (CHAR(36)): MySQL can only hand back generated integer ids.
            .Table("device", ArxIdColumn.Supplied<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 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 VARCHAR(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, in a MySQL ENUM column, which allows only its labels.
            // Column: data_type_showcase.mood ENUM('happy', 'neutral', 'sad').
            .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 VARCHAR(50)) 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 tree four levels deep
      AND (level 1)
          last_name Equal Filter-R751
          OR (level 2)
              AND (level 3)
                  job_title Contains manager
                  OR (level 4)
                      preferred_channel Equal E
                      do_not_email Equal False
              AND (level 3)
                  first_name BeginsWith Z
                  OR (level 4)
                      job_title Equal Intern
                      job_title Contains store
      found: Ana, Zack, Zoe
  > 2. A saved search, built recursively
      AND (level 1)
          last_name Equal Filter-R751
          OR (level 2)
              first_name Equal Dan
              AND (level 3)
                  job_title Contains manager
                  OR (level 4)
                      preferred_channel Equal E
                      AND (level 5)
                          do_not_email Equal True
                          OR (level 6)
                              first_name BeginsWith B
                              first_name BeginsWith C
      found: Ana, Cleo, Dan, Zack
  > 3. A link entity with its own filter
      OR (level 1)
          industry Equal Industry Retail
          AND (level 2)
              annual_revenue GreaterEqual 1000000
              status Equal ACTIVE
      found: Ana (Filter Retail), Ben (Filter Bank), Cleo (Filter Bank)
  > 4. Two filters joined: under And, then under Or
      search AND rule: Ana, Zoe
      search OR rule:  Ana, Ben, Cleo, Zack, Zoe
  > 5. And and Or: the grouping is the meaning
      (e-mail AND willing) OR Z…: Ana, Zack, Zoe
      e-mail AND (willing OR Z…): Ana, Zack