Read other row sources: a view in a schema, a materialized view, a partitioned table

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.RetrieveMultiple.AdvancedCases;

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

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

The sample, as it is in the repository.

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

namespace ArxSamples.PostgreSql.Operations.RetrieveMultiple.AdvancedCases;

/// <summary>
/// RetrieveMultiple reads more than tables: whatever name the query is given, PostgreSQL resolves it, so a view, a
/// materialized view, a view in another schema or a partitioned table is read with the same <see cref="ArxQueryExpression"/>:
/// criteria, links, order, paging, aggregates. Three scenarios (functions are <c>Advanced8</c>):
/// <list type="number">
/// <item>A view in another schema (<c>reporting.vw_product_catalogue</c>), linked to a table and aggregated.</item>
/// <item>A materialized view: refused with a hint while it was never refreshed; read after a refresh; a snapshot until
/// the next one.</item>
/// <item>A partitioned table: written and read through its parent, PostgreSQL skipping the partitions the criteria rule
/// out; one partition read by its own name.</item>
/// </list>
/// An inheritance parent (it reads its children's rows too) and a foreign table (<c>postgres_fdw</c>) are read the same
/// way; the tests cover the first.
/// </summary>
public sealed class Advanced7_ReadOtherRowSources : ISample
{
    public string Name => "retrieve-multiple-advanced-7-read-other-row-sources";

    public string Title => "Read other row sources: a view in a schema, a materialized view, a partitioned table";

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

        foreach (var (number, name) in new[] { ("ACC-R781", "Summit Supplies"), ("ACC-R782", "Harbor Hardware") })
        {
            var account = new ArxEntity("account");
            account["name"] = name;
            account["account_number"] = number;
            accounts[number] = await dbContext.Service.CreateAsync(account);
        }

        var category = new ArxEntity("product_category");
        category["name"] = "Tools R78";
        var categoryId = await dbContext.Service.CreateAsync(category);

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

        foreach (var (sku, name, price, active) in new[] { ("SKU-R781", "Hammer", 25m, true), ("SKU-R782", "Saw", 40m, true), ("SKU-R783", "Old drill", 90m, false) })
        {
            var product = new ArxEntity("product");
            product["sku"] = sku;
            product["name"] = name;
            product["category_id"] = new ArxEntityReference("product_category", categoryId);
            product["unit_price"] = price;
            product["is_active"] = active;
            products[sku] = await dbContext.Service.CreateAsync(product);
        }

        foreach (var (orderNumber, account, total, lines) in new[]
        {
            ("SO-R781", "ACC-R781", 180m, new[] { ("SKU-R781", 4, 25m), ("SKU-R782", 2, 40m) }),
            ("SO-R782", "ACC-R781", 200m, Array.Empty<(string, int, decimal)>()),
            ("SO-R783", "ACC-R782", 115m, new[] { ("SKU-R781", 1, 25m), ("SKU-R783", 1, 90m) }),
        })
        {
            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, 9, 0, 0, DateTimeKind.Utc);
            order["total"] = total;
            var orderId = await dbContext.Service.CreateAsync(order);

            foreach (var (sku, quantity, price) in lines)
            {
                var line = new ArxEntity("order_line");
                line["order_id"] = new ArxEntityReference("sales_order", orderId);
                line["product_id"] = new ArxEntityReference("product", products[sku]);
                line["quantity"] = quantity;
                line["unit_price"] = price;
                await dbContext.Service.CreateAsync(line);
            }
        }

        await Scenario1_AViewInAnotherSchemaAsync(dbContext);

        await Scenario2_AMaterializedViewAsync(dbContext, accounts["ACC-R782"]);

        await Scenario3_APartitionedTableAsync(dbContext, accounts);
    }

    /// <summary>
    /// 1. <c>reporting.vw_product_catalogue</c> (active products with their category's name), named with its schema, then
    /// used like any table: a criterion, a LEFT link to <c>order_line</c> (a table), units sold per product, the best
    /// seller first. The inactive Old drill isn't in the view, so it isn't in the result.
    /// </summary>
    private static async Task Scenario1_AViewInAnotherSchemaAsync(SampleContext dbContext)
    {
        Step("1. A view in another schema, linked to a table and aggregated");

        var query = new ArxQueryExpression("reporting.vw_product_catalogue") { AggregateInfo = new ArxAggregateInfo() };
        query.ColumnSet.AddColumns("sku", "name");
        query.Criteria.AddCondition("category", ArxConditionOperator.Equal, "Tools R78");
        query.LinkEntities.Add(new ArxLinkEntity("order_line", "id", "product_id", ArxJoinOperator.Left) { EntityAlias = "l" });
        query.AggregateInfo.AddAggregateColumn("units", "l.quantity", ArxAggregateOperator.Sum);
        query.AggregateInfo.AddGroupByColumns("sku", "name");
        query.AddOrder("units", ArxOrderType.Descending);

        var rows = await dbContext.Service.RetrieveMultipleAsync(query);

        foreach (var row in rows.Entities)
        {
            Show($"{row["sku"]} {row["name"],-6} {row["units"]} sold   (entity name: {row.EntityName})");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT v.sku, v.name, sum(l.quantity) AS units
         * FROM reporting.vw_product_catalogue v
         * LEFT JOIN order_line l ON l.product_id = v.id
         * WHERE v.category = 'Tools R78'
         * GROUP BY v.sku, v.name
         * ORDER BY units DESC;
         * -- Expect: 2 rows: SKU-R781 Hammer 5, SKU-R782 Saw 2 (no Old drill: inactive).
         * ===========================================================
         */
    }

    private static ArxQueryExpression SalesSummary()
    {
        var query = new ArxQueryExpression("mv_account_sales_summary");
        query.ColumnSet.AddColumns("name", "orders", "revenue");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R78");
        query.AddOrder("revenue", ArxOrderType.Descending);
        return query;
    }

    /// <summary>
    /// 2. <c>mv_account_sales_summary</c> stores its result. Created <c>WITH NO DATA</c>, it can't be read until it is
    /// refreshed: ARX8001 with the command to run. After a refresh (a deployment step or a scheduled job, not ARX's: the
    /// sample runs it directly), it reads like a table. It's a snapshot: an order created afterwards shows in the live
    /// tables at once, and in the view after the next refresh.
    /// </summary>
    private static async Task Scenario2_AMaterializedViewAsync(SampleContext dbContext, ArxRecordId harbor)
    {
        Step("2. A materialized view: never refreshed, refreshed, a snapshot");

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

        await dbContext.Direct.ExecuteAsync("REFRESH MATERIALIZED VIEW mv_account_sales_summary");

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(SalesSummary())).Entities)
        {
            Show($"after the refresh: {row["name"],-15} orders {row["orders"]}, revenue {row["revenue"]:N2}");
        }

        var order = new ArxEntity("sales_order");
        order["order_number"] = "SO-R784";
        order["account_id"] = new ArxEntityReference("account", harbor);
        order["ordered_on"] = new DateTime(2026, 11, 8, 15, 0, 0, DateTimeKind.Utc);
        order["total"] = 500m;
        await dbContext.Service.CreateAsync(order);

        var snapshot = (await dbContext.Service.RetrieveMultipleAsync(SalesSummary())).Entities.Single(x => (string)x["name"]! == "Harbor Hardware");

        var live = new ArxQueryExpression("sales_order") { AggregateInfo = new ArxAggregateInfo() };
        live.Criteria.AddCondition("account_id", ArxConditionOperator.Equal, harbor.AsInt64());
        live.AggregateInfo.AddAggregateColumn("orders", "id", ArxAggregateOperator.Count);
        live.AggregateInfo.AddAggregateColumn("revenue", "total", ArxAggregateOperator.Sum);
        var now = (await dbContext.Service.RetrieveMultipleAsync(live)).Entities[0];

        Show($"a new order for Harbor: the view still says {snapshot["orders"]} order, {snapshot["revenue"]:N2}; the tables say {now["orders"]} orders, {now["revenue"]:N2}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, orders, revenue
         * FROM mv_account_sales_summary
         * WHERE account_number LIKE 'ACC-R78%'
         * ORDER BY revenue DESC;
         * -- Expect: Summit Supplies 2 380.00, Harbor Hardware 1 115.00 (the snapshot, until REFRESH MATERIALIZED VIEW).
         * SELECT count(*), sum(total)
         * FROM sales_order
         * WHERE order_number IN ('SO-R783', 'SO-R784');
         * -- Expect: 2, 615.00: Harbor's orders now.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. <c>web_visit</c> is split into one partition per half year. Created through the parent (PostgreSQL puts each
    /// row in its partition), read through the parent with a date range: only the second half's partition is scanned.
    /// A partition is also a table of its own, read by its own name.
    /// </summary>
    private static async Task Scenario3_APartitionedTableAsync(SampleContext dbContext, Dictionary<string, ArxRecordId> accounts)
    {
        Step("3. A partitioned table");

        var visits = new[]
        {
            ("ACC-R781", "/pricing", new DateOnly(2026, 3, 10)),
            ("ACC-R781", "/pricing", new DateOnly(2026, 8, 2)),
            ("ACC-R781", "/contact", new DateOnly(2026, 9, 15)),
            ("ACC-R782", "/pricing", new DateOnly(2026, 10, 1)),
        }.Select(x =>
        {
            var visit = new ArxEntity("web_visit");
            visit["account_id"] = new ArxEntityReference("account", accounts[x.Item1]);
            visit["page"] = x.Item2;
            visit["visited_on"] = x.Item3;
            return visit;
        }).ToList();

        await dbContext.Service.CreateMultipleAsync(visits);

        var query = new ArxQueryExpression("web_visit");
        query.ColumnSet.AddColumns("page", "visited_on");
        query.Criteria.AddCondition("visited_on", ArxConditionOperator.GreaterEqual, new DateOnly(2026, 7, 1));
        query.Criteria.AddCondition("visited_on", ArxConditionOperator.LessThan, new DateOnly(2026, 10, 1));
        var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Inner);
        account.EntityAlias = "acc";
        account.ColumnSet.AddColumn("name");
        account.LinkCriteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R78");
        query.AddOrder("visited_on", ArxOrderType.Ascending);

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(query)).Entities)
        {
            Show($"July to September: {row["visited_on"]:yyyy-MM-dd} {row["page"],-9} {row["acc.name"]}");
        }

        var firstHalf = new ArxQueryExpression("web_visit_2026_h1");
        firstHalf.ColumnSet.AddColumns("page", "visited_on");
        firstHalf.Criteria.AddCondition("account_id", ArxConditionOperator.In, accounts.Values.Select(x => (object)x.AsInt64()).ToArray());

        foreach (var row in (await dbContext.Service.RetrieveMultipleAsync(firstHalf)).Entities)
        {
            Show($"the first-half partition alone: {row["visited_on"]:yyyy-MM-dd} {row["page"]}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT v.tableoid::regclass AS partition, v.visited_on, v.page, a.name
         * FROM web_visit v
         * JOIN account a ON a.id = v.account_id AND a.account_number LIKE 'ACC-R78%'
         * ORDER BY v.visited_on;
         * -- Expect: 4 rows: web_visit_2026_h1 2026-03-10 /pricing; web_visit_2026_h2 2026-08-02 /pricing,
         * -- 2026-09-15 /contact (both Summit Supplies), 2026-10-01 /pricing (Harbor Hardware).
         * EXPLAIN (COSTS OFF)
         * SELECT page FROM web_visit WHERE visited_on >= '2026-07-01' AND visited_on < '2026-10-01';
         * -- Expect: a scan of web_visit_2026_h2 only (the first half's partition is pruned).
         * ===========================================================
         */
    }
}

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

namespace ArxSamples.PostgreSql;

/// <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 Npgsql 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.PostgreSql;

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

namespace ArxSamples.PostgreSql;

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

/// <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, Guid, or a
/// PostgreSQL enum type (DatabaseEnum).</item>
/// <item><b>Multiplicity</b>: Single (one code), or several codes stored as an array (Array), bit flags (Flags), a
/// delimited string (Delimited) or a JSON array (Json).</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 ArxPgDbService(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 (bigserial): 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 text.
            .Add("AccountStatus", ArxSelectionSetKind.String, ("ACTIVE", "Active"), ("ON_HOLD", "On hold"), ("CLOSED", "Closed"))

            // Multiple choice, stored as an array of codes. RESELLER is retired: rows that have it still read back,
            // but new writes can't use it. Column: account.segments text[].
            .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.Array))

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

            // The labels of a PostgreSQL enum type: the value is written with a cast to that type.
            // Column: data_type_showcase.mood showcase_mood.
            .Add(new ArxSelectionSetDefinition("Mood", ArxSelectionSetKind.DatabaseEnum, new[]
            {
                new ArxSelectionSetOption("happy", "Happy", 0),
                new ArxSelectionSetOption("neutral", "Neutral", 1),
                new ArxSelectionSetOption("sad", "Sad", 2)
            }, databaseTypeName: "showcase_mood"));

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

The plain Npgsql read-back helper, for the samples that show what PostgreSQL stored.

namespace ArxSamples.PostgreSql;

/// <summary>Plain Npgsql outside the SDK, for the samples that show what PostgreSQL stored.</summary>
public sealed partial class SampleContext
{
    private PGDirectHelper? _direct;

    /// <summary>Plain Npgsql outside the SDK, used only to reset the tables and to show what was stored.</summary>
    public PGDirectHelper Direct => _direct ??= new PGDirectHelper(ConnectionString);
}

Plain Npgsql, not part of ARX: reads rows back so a sample can show what PostgreSQL stored.

using ARX.SDK.Services;
using Npgsql;

namespace ArxSamples.PostgreSql;

/// <summary>
/// Plain Npgsql, outside ARX.SDK: resets the sample tables, reads rows back so a sample can show what PostgreSQL
/// actually stored (printing is SampleOutput.cs). It isn't part of the SDK and isn't how an application uses ARX;
/// it only lets the samples verify and show their results. Reading through ARX (Retrieve, RetrieveMultiple) gets its
/// own samples once those operations are hardened.
/// </summary>
public sealed class PGDirectHelper
{
    private readonly string _connectionString;

    public PGDirectHelper(string connectionString)
    {
        _connectionString = connectionString;
    }

    /// <summary>The same helper pointed at another database on the same server.</summary>
    public PGDirectHelper ForDatabase(string database)
        => new PGDirectHelper(new NpgsqlConnectionStringBuilder(_connectionString) { Database = database }.ConnectionString);

    /// <summary>The connection string of <see cref="ForDatabase"/>'s database, for a second ARX service.</summary>
    public string ConnectionStringFor(string database)
        => new NpgsqlConnectionStringBuilder(_connectionString) { Database = database }.ConnectionString;

    /// <summary>Creates <paramref name="database"/> on the server if it doesn't exist yet (setup for the two-database sample).</summary>
    public async Task EnsureDatabaseExistsAsync(string database)
    {
        await using var connection = await OpenConnectionAsync();

        await using (var exists = new NpgsqlCommand("SELECT count(*) FROM pg_database WHERE datname = $1", connection))
        {
            exists.Parameters.Add(new NpgsqlParameter { Value = database });

            if ((long)(await exists.ExecuteScalarAsync())! > 0)
            {
                return;
            }
        }

        // CREATE DATABASE takes no parameters; the name is the sample's own constant, quoted.
        await using var create = new NpgsqlCommand($"CREATE DATABASE \"{database.Replace("\"", "\"\"")}\"", connection);
        await create.ExecuteNonQueryAsync();
    }

    /// <summary>Drops and recreates the sample tables (Schema/sample-schema.sql).</summary>
    public async Task ResetSchemaAsync()
    {
        var sql = await File.ReadAllTextAsync(Path.Combine(AppContext.BaseDirectory, "Schema", "sample-schema.sql"));
        await ExecuteAsync(sql);
    }

    public async Task ExecuteAsync(string sql)
    {
        await using var connection = await OpenConnectionAsync();
        await using var command = new NpgsqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    public async Task<NpgsqlConnection> OpenConnectionAsync()
    {
        var connection = new NpgsqlConnection(_connectionString);
        await connection.OpenAsync();
        return connection;
    }

    /// <summary>Counts rows, optionally with a WHERE clause using $1, $2, … for <paramref name="parameters"/>.</summary>
    public async Task<long> CountAsync(string table, string where = "true", params object[] parameters)
    {
        await using var connection = await OpenConnectionAsync();
        await using var command = new NpgsqlCommand($"SELECT count(*) FROM {table} WHERE {where}", connection);

        foreach (var parameter in parameters)
        {
            command.Parameters.Add(new NpgsqlParameter { Value = parameter });
        }

        return (long)(await command.ExecuteScalarAsync())!;
    }

    /// <summary>Reads one row by id as column → value (null for SQL NULL).</summary>
    public async Task<Dictionary<string, object?>> RowAsync(string table, long id)
    {
        await using var connection = await OpenConnectionAsync();
        await using var command = new NpgsqlCommand($"SELECT * FROM {table} WHERE id = $1", connection);
        command.Parameters.Add(new NpgsqlParameter { Value = id });
        await using var reader = await command.ExecuteReaderAsync();

        if (!await reader.ReadAsync())
        {
            throw new InvalidOperationException($"No {table} row with id {id}.");
        }

        var row = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);

        for (var i = 0; i < reader.FieldCount; i++)
        {
            row[reader.GetName(i)] = reader.IsDBNull(i) ? null : reader.GetValue(i);
        }

        return row;
    }

    /// <summary>
    /// Reads columns of one row as PostgreSQL prints them (<c>column::text</c>, in a UTC session), which shows the stored
    /// value exactly, whatever its type.
    /// </summary>
    public async Task<Dictionary<string, string?>> TextRowAsync(string table, long id, params string[] columns)
    {
        await using var connection = await OpenConnectionAsync();
        await using (var zone = new NpgsqlCommand("SET TIME ZONE 'UTC'", connection))
        {
            await zone.ExecuteNonQueryAsync();
        }

        var select = string.Join(", ", columns.Select(x => $"{x}::text"));
        await using var command = new NpgsqlCommand($"SELECT {select} FROM {table} WHERE id = $1", connection);
        command.Parameters.Add(new NpgsqlParameter { Value = id });
        await using var reader = await command.ExecuteReaderAsync();

        if (!await reader.ReadAsync())
        {
            throw new InvalidOperationException($"No {table} row with id {id}.");
        }

        var row = new Dictionary<string, string?>(StringComparer.OrdinalIgnoreCase);

        for (var i = 0; i < columns.Length; i++)
        {
            row[columns[i]] = reader.IsDBNull(i) ? null : reader.GetString(i);
        }

        return row;
    }
}

  > 1. A view in another schema, linked to a table and aggregated
      SKU-R781 Hammer 5 sold   (entity name: reporting.vw_product_catalogue)
      SKU-R782 Saw    2 sold   (entity name: reporting.vw_product_catalogue)
  > 2. A materialized view: never refreshed, refreshed, a snapshot
      caught ArxDatabaseException: ARX8001 NotSupported: RetrieveMultiple on 'mv_account_sales_summary' isn't possible on this object: materialized view "mv_account_sales_summary" has not been populated (Use the REFRESH MATERIALIZED VIEW command.).
      after the refresh: Summit Supplies orders 2, revenue 380.00
      after the refresh: Harbor Hardware orders 1, revenue 115.00
      a new order for Harbor: the view still says 1 order, 115.00; the tables say 2 orders, 615.00
  > 3. A partitioned table
      July to September: 2026-08-02 /pricing  Summit Supplies
      July to September: 2026-09-15 /contact  Summit Supplies
      the first-half partition alone: 2026-03-10 /pricing