Views in depth: computed criteria, links both ways, declared ids and lookups, column boundaries

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 Advanced9_ViewsInDepth().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

namespace ArxSamples.MySQL.Operations.RetrieveMultiple.AdvancedCases;

/// <summary>
/// Views in depth, in five scenarios. A view is a saved query with a name: ARX reads it by that name like a table, and
/// MySQL runs the view's query inside the one ARX sends, so criteria, links, order and paging all apply. The sample
/// schema names views <c>vw_</c> (materialized views <c>mv_</c>, functions <c>fn_</c>, procedures <c>sp_</c>).
/// <list type="number">
/// <item>A computed column as a criterion: what a view gives that a table can't (here, accounts with two or more contacts),
/// ordered and paged with the total.</item>
/// <item>Links both ways: from a view to a table, and from a table to a view.</item>
/// <item>A view declared with its id and a lookup: Retrieve by id, a RecordId, the lookup read back with its display name,
/// and the <c>_lk</c> direct join from a view's column.</item>
/// <item>A view as a column boundary: a directory without the contacts' private columns; asking it for one is ARX5002.</item>
/// <item>A view on a view: read like any other; MySQL expands both into one query.</item>
/// </list>
/// RealCase5 is the short version (a view as it is, and declared with its id); Advanced7 reads a view in another schema,
/// and what stands in for a materialized view, which MySQL doesn't have.
/// </summary>
public sealed class Advanced9_ViewsInDepth : ISample
{
    public string Name => "retrieve-multiple-advanced-9-views-in-depth";

    public string Title => "Views in depth: computed criteria, links both ways, declared ids and lookups, column boundaries";

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

        foreach (var (number, name, contacts) in new[]
        {
            ("ACC-R801", "Alpha Views", new[] { "Ann", "Al", "Ash" }),
            ("ACC-R802", "Beta Views", new[] { "Bea", "Bo" }),
            ("ACC-R803", "Gamma Views", new[] { "Gus" }),
        })
        {
            var account = new ArxEntity("account");
            account["name"] = name;
            account["account_number"] = number;
            accounts[number] = await dbContext.Service.CreateAsync(account);

            foreach (var first in contacts)
            {
                var contact = new ArxEntity("contact");
                contact["first_name"] = first;
                contact["last_name"] = "Views-R80";
                contact["job_title"] = first == contacts[0] ? "Buyer" : "Engineer";
                contact["email"] = $"{first.ToLowerInvariant()}[email protected]";
                contact["account_id"] = new ArxEntityReference("account", accounts[number]);
                await dbContext.Service.CreateAsync(contact);
            }
        }

        foreach (var (orderNumber, account) in new[] { ("SO-R801", "ACC-R801"), ("SO-R802", "ACC-R803") })
        {
            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, 12, 0, 0, DateTimeKind.Utc);
            await dbContext.Service.CreateAsync(order);
        }

        await Scenario1_AComputedColumnAsACriterionAsync(dbContext);

        await Scenario2_LinksBothWaysAsync(dbContext);

        await Scenario3_DeclaredWithItsIdAndALookupAsync(dbContext);

        await Scenario4_AColumnBoundaryAsync(dbContext);

        await Scenario5_AViewOnAViewAsync(dbContext);
    }

    /// <summary>
    /// 1. <c>vw_account_overview.contacts</c> is a count the view computes. A table has no such column; on the view it's a
    /// criterion like any other (the HAVING of the view's GROUP BY, written as a plain condition), and an order. One
    /// account per page, with the total.
    /// </summary>
    private static async Task Scenario1_AComputedColumnAsACriterionAsync(SampleContext dbContext)
    {
        Step("1. A computed column as a criterion, ordered and paged");

        var query = new ArxQueryExpression("vw_account_overview");
        query.ColumnSet.AddColumns("name", "contacts");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R80");
        query.Criteria.AddCondition("contacts", ArxConditionOperator.GreaterEqual, 2);
        query.AddOrder("contacts", ArxOrderType.Descending);

        for (var pageNumber = 1; ; pageNumber++)
        {
            query.PageInfo = new ArxPagingInfo { Count = 1, PageNumber = pageNumber, ReturnTotalRecordCount = pageNumber == 1 };
            var page = await dbContext.Service.RetrieveMultipleAsync(query);
            var row = page.Entities.Single();
            Show($"page {pageNumber}: {row["name"]}, {row["contacts"]} contacts" + (pageNumber == 1 ? $" ({page.TotalRecordCount} accounts in {page.TotalPageCount} pages)" : string.Empty));

            if (!page.HasNextPage)
            {
                break;
            }
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, contacts
         * FROM vw_account_overview
         * WHERE account_number LIKE 'ACC-R80%' AND contacts >= 2
         * ORDER BY contacts DESC;
         * -- Expect: 2 rows: Alpha Views 3, Beta Views 2 (Gamma has 1).
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. From the view to a table: each key account's buyers (a LEFT link to contact with its own criterion). From a
    /// table to the view: each order with its account's number of contacts, which the orders table doesn't have.
    /// </summary>
    private static async Task Scenario2_LinksBothWaysAsync(SampleContext dbContext)
    {
        Step("2. Links both ways: view to table, table to view");

        var fromView = new ArxQueryExpression("vw_account_overview");
        fromView.ColumnSet.AddColumn("name");
        fromView.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R80");
        var buyers = fromView.AddLink("contact", "id", "account_id", ArxJoinOperator.Left);
        buyers.EntityAlias = "buyer";
        buyers.ColumnSet.AddColumn("first_name");
        buyers.LinkCriteria.AddCondition("job_title", ArxConditionOperator.Equal, "Buyer");
        fromView.AddOrder("name", ArxOrderType.Ascending);

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(fromView)).Entities)
        {
            Show($"view → table: {row["name"],-12} buyer {row["buyer.first_name"]}");
        }

        var toView = new ArxQueryExpression("sales_order");
        toView.ColumnSet.AddColumn("order_number");
        toView.Criteria.AddCondition("order_number", ArxConditionOperator.BeginsWith, "SO-R80");
        var overview = toView.AddLink("vw_account_overview", "account_id", "id", ArxJoinOperator.Inner);
        overview.EntityAlias = "acc";
        overview.ColumnSet.AddColumns("name", "contacts");
        toView.AddOrder("order_number", ArxOrderType.Ascending);

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(toView)).Entities)
        {
            Show($"table → view: {row["order_number"]} for {row["acc.name"]}, contacts: {row["acc.contacts"]}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT v.name, c.first_name
         * FROM vw_account_overview v
         * LEFT JOIN contact c ON c.account_id = v.id AND c.job_title = 'Buyer'
         * WHERE v.account_number LIKE 'ACC-R80%'
         * ORDER BY v.name;
         * -- Expect: 3 rows: Alpha Views Ann, Beta Views Bea, Gamma Views Gus.
         * SELECT o.order_number, v.name, v.contacts
         * FROM sales_order o
         * JOIN vw_account_overview v ON v.id = o.account_id
         * WHERE o.order_number LIKE 'SO-R80%'
         * ORDER BY o.order_number;
         * -- Expect: 2 rows: SO-R801 Alpha Views 3, SO-R802 Gamma Views 1.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. A view has no primary key MySQL could report, so ARX knows its id only when the developer declares it (as
    /// for any table). Declared here with its id and its account_id lookup: Retrieve by id works, the row has a
    /// RecordId, and account_id reads back as a reference with the account's display name. The <c>_lk</c> direct join
    /// works from a view's column too.
    /// </summary>
    private static async Task Scenario3_DeclaredWithItsIdAndALookupAsync(SampleContext dbContext)
    {
        Step("3. A view declared with its id and a lookup");

        var service = new ArxMySqlDbService(dbContext.ConnectionString, ArxSchemaCatalog.Create()
            .Table("account", ArxIdColumn.Generated<long>("id")).DisplayName("account", "name")
            .Table("vw_contact_directory", ArxIdColumn.Supplied<long>("id")).Lookup("vw_contact_directory", "account_id", "account")
            .Build());

        var query = new ArxQueryExpression("vw_contact_directory");
        query.ColumnSet.AddColumns("first_name", "account_id", "account_id._lk.account.account_number");
        query.Criteria.AddCondition("first_name", ArxConditionOperator.Equal, "Bea");
        var bea = (await service.RetrieveMultipleAsync(query)).Entities.Single();
        var account = (ArxEntityReference)bea["account_id"]!;
        Show($"{bea["first_name"]}: RecordId on column {bea.RecordId!.Column}, account_id → {account.EntityName} '{account.Name}', number {bea["account_id.account_number"]}");

        var again = await service.RetrieveAsync("vw_contact_directory", bea.RecordId, new ArxColumnSet("first_name", "job_title"));
        Show($"Retrieve by that id: {again!["first_name"]}, {again["job_title"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT d.first_name, d.job_title, a.name, a.account_number
         * FROM vw_contact_directory d
         * JOIN account a ON a.id = d.account_id
         * WHERE d.first_name = 'Bea' AND d.last_name = 'Views-R80';
         * -- Expect: 1 row: Bea, Buyer, Beta Views, ACC-R802.
         * ===========================================================
         */
    }

    /// <summary>
    /// 4. <c>vw_contact_directory</c> leaves out email, phone, birthday and notes. Asking it for email is ARX5002 (no such
    /// column), from the database itself: code that reads the directory can't get them, whatever its query says. Give a
    /// role SELECT on the view and not on the table, and the boundary holds outside ARX too.
    /// </summary>
    private static async Task Scenario4_AColumnBoundaryAsync(SampleContext dbContext)
    {
        Step("4. A view as a column boundary");

        var query = new ArxQueryExpression("vw_contact_directory");
        query.ColumnSet.AddColumns("first_name", "email");
        query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Views-R80");

        try
        {
            await dbContext.Service.RetrieveMultipleAsync(query);
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.UndefinedAttribute)
        {
            ShowError(error);
        }

        query.ColumnSet = new ArxColumnSet("first_name", "job_title");
        Show($"the directory's own columns: {(await dbContext.Service.RetrieveMultipleAsync(query)).Entities.Count} contacts, first name and job title");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT column_name
         * FROM information_schema.columns
         * WHERE table_schema = DATABASE() AND table_name = 'vw_contact_directory'
         * ORDER BY ordinal_position;
         * -- Expect: id, first_name, last_name, job_title, account_id (no email).
         * ===========================================================
         */
    }

    /// <summary>
    /// 5. <c>vw_key_accounts</c> is a view on <c>vw_account_overview</c> (accounts with two or more contacts). ARX reads it
    /// like the others; MySQL expands both views into one query over the tables (the grouped one as an
    /// aggregate step over contact).
    /// </summary>
    private static async Task Scenario5_AViewOnAViewAsync(SampleContext dbContext)
    {
        Step("5. A view on a view");

        var query = new ArxQueryExpression("vw_key_accounts");
        query.ColumnSet.AddColumns("name", "contacts");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R80");
        query.AddOrder("name", ArxOrderType.Ascending);

        Show("key accounts: " + string.Join(", ", (await dbContext.Service.RetrieveMultipleAsync(query)).Entities.Select(x => $"{x["name"]} ({x["contacts"]})")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, contacts
         * FROM vw_key_accounts
         * WHERE account_number LIKE 'ACC-R80%'
         * ORDER BY name;
         * -- Expect: 2 rows: Alpha Views 3, Beta Views 2.
         * EXPLAIN FORMAT=TREE SELECT name FROM vw_key_accounts WHERE account_number LIKE 'ACC-R80%';
         * -- Expect: a plan over account and contact: MySQL merges the plain view into the query and builds the grouped one
         * -- (vw_account_overview) as a derived table, an aggregate over contact.
         * ===========================================================
         */
    }
}

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 computed column as a criterion, ordered and paged
      page 1: Alpha Views, 3 contacts (2 accounts in 2 pages)
      page 2: Beta Views, 2 contacts
  > 2. Links both ways: view to table, table to view
      view → table: Alpha Views  buyer Ann
      view → table: Beta Views   buyer Bea
      view → table: Gamma Views  buyer Gus
      table → view: SO-R801 for Alpha Views, contacts: 3
      table → view: SO-R802 for Gamma Views, contacts: 1
  > 3. A view declared with its id and a lookup
      Bea: RecordId on column id, account_id → account 'Beta Views', number ACC-R802
      Retrieve by that id: Bea, Buyer
  > 4. A view as a column boundary
      caught ArxDatabaseException: ARX5002 UndefinedAttribute: RetrieveMultiple on 'vw_contact_directory' refers to a column the table doesn't have: Unknown column 'email' in 'field list'. If it's a column the schema catalog declares (the id, a key, a lookup), check its name there: ValidateSchemaAsync() lists every declared column the table doesn't have.
      the directory's own columns: 6 contacts, first name and job title
  > 5. A view on a view
      key accounts: Alpha Views (3), Beta Views (2)