Paging in depth: the total is opt-in, ask once, load more, Top, seek paging

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

The sample, as it is in the repository.

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

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.AdvancedCases;

/// <summary>
/// Paging in depth, and how to keep it cheap, in six scenarios. The main choice is the total: it is opt-in
/// (<see cref="ArxPagingInfo.ReturnTotalRecordCount"/>, false by default). Asked for, ARX sends a second statement in the
/// same round trip, <c>SELECT count(*) FROM (the query without its order and paging)</c>, and the database reads every
/// matching row to count them, not only the page. Not asked for, the page still knows whether a next one exists
/// (<see cref="ArxEntityCollection.HasNextPage"/>: ARX reads one row more than the page).
/// <list type="number">
/// <item>The same page with and without the total: what each gives back.</item>
/// <item>What the total costs, timed on this machine (not a benchmark; <c>RetrievePagingBenchmarks</c> measures it).</item>
/// <item>"Page 1 of 40": ask for the total on the first page only, then page without it.</item>
/// <item>"Load more": never ask for the total; HasNextPage is enough.</item>
/// <item>One page only: Top (no extra row, no offset); Top together with PageInfo is ARX1001.</item>
/// <item>Deep pages: seek past the last row read (keyset paging) instead of an ever larger OFFSET.</item>
/// </list>
/// The plans below assume the table's statistics are current. SQL Server updates them on its own when enough rows
/// change and a query next needs them; until then it may plan from stale numbers, so the checks start with
/// <c>UPDATE STATISTICS product</c>.
/// </summary>
public sealed class Advanced6_PagingInDepth : ISample
{
    public string Name => "retrieve-multiple-advanced-6-paging-in-depth";

    public string Title => "Paging in depth: the total is opt-in, ask once, load more, Top, seek paging";

    private const int PRODUCTS = 20_000;

    private const int PAGE_SIZE = 500;

    private const string PREFIX = "PGN-R77-";

    public async Task RunAsync(SampleContext dbContext)
    {
        var products = Enumerable.Range(1, PRODUCTS).Select(i =>
        {
            var product = new ArxEntity("product");
            product["sku"] = $"{PREFIX}{i:D5}";
            product["name"] = $"Paged product {i}";
            product["unit_price"] = 1m + i % 50;
            return product;
        }).ToList();

        await dbContext.Service.CreateMultipleAsync(products);

        await Scenario1_WithAndWithoutTheTotalAsync(dbContext);

        await Scenario2_WhatTheTotalCostsAsync(dbContext);

        await Scenario3_AskForTheTotalOnceAsync(dbContext);

        await Scenario4_LoadMoreWithoutATotalAsync(dbContext);

        await Scenario5_OnePageOnlyWithTopAsync(dbContext);

        await Scenario6_SeekInsteadOfOffsetAsync(dbContext);
    }

    /// <summary>The sample's products, by SKU, one page: only the columns a list screen shows.</summary>
    private static ArxQueryExpression Page(int pageNumber, bool total, int pageSize = PAGE_SIZE)
    {
        var query = new ArxQueryExpression("product");
        query.ColumnSet.AddColumns("sku", "unit_price");
        query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, PREFIX);
        query.AddOrder("sku", ArxOrderType.Ascending);
        query.PageInfo = new ArxPagingInfo { Count = pageSize, PageNumber = pageNumber, ReturnTotalRecordCount = total };
        return query;
    }

    private static string Describe(ArxEntityCollection page)
        => $"{page.Entities.Count} rows ({page.Entities[0]["sku"]} to {page.Entities[^1]["sku"]}), page {page.CurrentPage}, "
           + $"total {page.TotalRecordCount}, pages {page.TotalPageCount}, next page: {page.HasNextPage}";

    /// <summary>
    /// 1. Page 2, twice. With the total: 20000 rows in 40 pages. Without it: TotalRecordCount and TotalPageCount stay 0
    /// (not counted, not "no rows"), and HasNextPage is still right.
    /// </summary>
    private static async Task Scenario1_WithAndWithoutTheTotalAsync(SampleContext dbContext)
    {
        Step("1. The same page with and without the total");

        Show("with:    " + Describe(await dbContext.Service.RetrieveMultipleAsync(Page(2, total: true))));
        Show("without: " + Describe(await dbContext.Service.RetrieveMultipleAsync(Page(2, total: false))));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT min(sku), max(sku), count(*)
         * FROM (SELECT sku FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 500 ROWS FETCH NEXT 500 ROWS ONLY) AS page;
         * -- Expect: PGN-R77-00501, PGN-R77-01000, 500: page 2 both times.
         * SELECT count(*) FROM product WHERE sku LIKE 'PGN-R77-%';
         * -- Expect: 20000, the total the first read reported (the statement ARX adds when asked).
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. The total's cost: the page reads 501 rows, the count reads all 20000 that match. Timed here over 20 reads of page
    /// 1 each way; the numbers are this machine's and only show the direction (the benchmark is
    /// <c>RetrievePagingBenchmarks</c>, categories Page and PageAndTotal).
    /// </summary>
    private static async Task Scenario2_WhatTheTotalCostsAsync(SampleContext dbContext)
    {
        Step("2. What the total costs (timed on this machine; not a benchmark)");

        const int READS = 20;

        async Task<double> TimeAsync(bool total)
        {
            await dbContext.Service.RetrieveMultipleAsync(Page(1, total)); // warm up
            var watch = Stopwatch.StartNew();

            for (var i = 0; i < READS; i++)
            {
                await dbContext.Service.RetrieveMultipleAsync(Page(1, total));
            }

            return watch.Elapsed.TotalMilliseconds / READS;
        }

        var without = await TimeAsync(total: false);
        var with = await TimeAsync(total: true);
        Show($"page 1 without the total: {without:N2} ms a read; with it: {with:N2} ms a read");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * UPDATE STATISTICS product;
         * SET STATISTICS IO ON;
         * SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 0 ROWS FETCH NEXT 501 ROWS ONLY;
         * SELECT count(*) FROM (SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%') AS arx_count;
         * -- Expect (Messages tab, with the actual execution plan on): the page is a seek on product_sku_key that stops
         * -- after 501 rows, a few logical reads; the count reads all 20000 rows.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. A grid that shows "page 1 of 40": the total is asked for on page 1 and kept; pages 2 to 40 don't ask again
    /// (the count would be the same 20000 rows read 39 more times). Rows added meanwhile show up on later pages, and the
    /// page count shown is the one from page 1: refresh it when the user goes back to page 1.
    /// </summary>
    private static async Task Scenario3_AskForTheTotalOnceAsync(SampleContext dbContext)
    {
        Step("3. \"Page 1 of 40\": the total on the first page only");

        var first = await dbContext.Service.RetrieveMultipleAsync(Page(1, total: true));
        var pageCount = first.TotalPageCount;
        var read = first.Entities.Count;
        var pageNumber = 1;
        var page = first;

        while (page.HasNextPage)
        {
            page = await dbContext.Service.RetrieveMultipleAsync(Page(++pageNumber, total: false));
            read += page.Entities.Count;
        }

        Show($"page 1 of {pageCount} ({first.TotalRecordCount} products); read {read} rows in {pageNumber} pages, counted once");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*), CEILING(count(*) / 500.0)
         * FROM product
         * WHERE sku LIKE 'PGN-R77-%';
         * -- Expect: 20000, 40: the total and the page count page 1 reported, and what the loop read.
         * ===========================================================
         */
    }

    /// <summary>
    /// 4. A "load more" list or an infinite scroll shows no page count, so it never asks for one: each page is one
    /// statement, and HasNextPage decides whether to show the button. Here: the first three loads.
    /// </summary>
    private static async Task Scenario4_LoadMoreWithoutATotalAsync(SampleContext dbContext)
    {
        Step("4. \"Load more\": no total at all");

        for (var load = 1; load <= 3; load++)
        {
            var page = await dbContext.Service.RetrieveMultipleAsync(Page(load, total: false, pageSize: 50));
            Show($"load {load}: {page.Entities[0]["sku"]} to {page.Entities[^1]["sku"]}; show \"Load more\": {page.HasNextPage}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT min(sku), max(sku)
         * FROM (SELECT sku FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 0 ROWS FETCH NEXT 150 ROWS ONLY) AS loaded;
         * -- Expect: PGN-R77-00001, PGN-R77-00150: the three loads of 50.
         * ===========================================================
         */
    }

    /// <summary>
    /// 5. "The five cheapest" is one page and never a next one: Top reads exactly five rows (PageInfo would read six, to
    /// know about a next page). Top and PageInfo together are ARX1001: one says how many rows, the other which page.
    /// </summary>
    private static async Task Scenario5_OnePageOnlyWithTopAsync(SampleContext dbContext)
    {
        Step("5. One page only: Top");

        var query = new ArxQueryExpression("product") { Top = 5 };
        query.ColumnSet.AddColumns("sku", "unit_price");
        query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, PREFIX);
        query.AddOrder("unit_price", ArxOrderType.Ascending);
        query.AddOrder("sku", ArxOrderType.Ascending);

        var cheapest = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", cheapest.Entities.Select(x => $"{x["sku"]} {x["unit_price"]:N2}")));

        try
        {
            query.PageInfo = new ArxPagingInfo { Count = 5 };
            await dbContext.Service.RetrieveMultipleAsync(query);
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.InvalidArgument)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT sku, unit_price
         * FROM product
         * WHERE sku LIKE 'PGN-R77-%'
         * ORDER BY unit_price, sku
         * OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
         * -- Expect: PGN-R77-00050, PGN-R77-00100, PGN-R77-00150, PGN-R77-00200, PGN-R77-00250, all 1.00.
         * ===========================================================
         */
    }

    /// <summary>
    /// 6. OFFSET makes the database read and throw away every row before the page: page 40 reads 20000 rows to return
    /// 500. Seek paging remembers the last SKU read and asks for the rows after it, always as page 1, so each page
    /// reads only its own rows through the index on the order column (here the unique index on sku). The order must be
    /// unique (add the id after a column with repeats, and seek on both), and the page can't be jumped to by number:
    /// it suits exports and "load more", not "go to page 37". Rows added meanwhile aren't read twice or skipped.
    /// </summary>
    private static async Task Scenario6_SeekInsteadOfOffsetAsync(SampleContext dbContext)
    {
        Step("6. Deep pages: seek past the last row instead of OFFSET");

        var byOffset = await dbContext.Service.RetrieveMultipleAsync(Page(40, total: false));

        string? last = null;
        ArxEntityCollection page;
        var pages = 0;

        do
        {
            var query = Page(1, total: false);

            if (last != null)
            {
                query.Criteria.AddCondition("sku", ArxConditionOperator.GreaterThan, last);
            }

            page = await dbContext.Service.RetrieveMultipleAsync(query);
            last = (string)page.Entities[^1]["sku"]!;
            pages++;
        }
        while (page.HasNextPage);

        Show($"page 40 by OFFSET: {byOffset.Entities[0]["sku"]} to {byOffset.Entities[^1]["sku"]}");
        Show($"page {pages} by seek:   {page.Entities[0]["sku"]} to {page.Entities[^1]["sku"]}, the same rows");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * UPDATE STATISTICS product;
         * SET STATISTICS IO ON;
         * SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 19500 ROWS FETCH NEXT 501 ROWS ONLY;
         * SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%' AND sku > 'PGN-R77-19500' ORDER BY sku OFFSET 0 ROWS FETCH NEXT 501 ROWS ONLY;
         * -- Expect: both return PGN-R77-19501 to PGN-R77-20000 (500 rows); the OFFSET plan reads the index through all
         * -- 20000 rows (more logical reads), the seek plan starts at sku > 'PGN-R77-19500' and reads 500.
         * ===========================================================
         */
    }
}

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. The same page with and without the total
      with:    500 rows (PGN-R77-00501 to PGN-R77-01000), page 2, total 20000, pages 40, next page: True
      without: 500 rows (PGN-R77-00501 to PGN-R77-01000), page 2, total 0, pages 0, next page: True
  > 2. What the total costs (timed on this machine; not a benchmark)
      page 1 without the total: 4.52 ms a read; with it: 36.53 ms a read
  > 3. "Page 1 of 40": the total on the first page only
      page 1 of 40 (20000 products); read 20000 rows in 40 pages, counted once
  > 4. "Load more": no total at all
      load 1: PGN-R77-00001 to PGN-R77-00050; show "Load more": True
      load 2: PGN-R77-00051 to PGN-R77-00100; show "Load more": True
      load 3: PGN-R77-00101 to PGN-R77-00150; show "Load more": True
  > 5. One page only: Top
      PGN-R77-00050 1.00, PGN-R77-00100 1.00, PGN-R77-00150 1.00, PGN-R77-00200 1.00, PGN-R77-00250 1.00
      caught ArxValidationException: ARX1001 InvalidArgument: The query sets both Top and PageInfo. Use PageInfo to page, or Top for the first rows.
  > 6. Deep pages: seek past the last row instead of OFFSET
      page 40 by OFFSET: PGN-R77-19501 to PGN-R77-20000
      page 40 by seek:   PGN-R77-19501 to PGN-R77-20000, the same rows