RetrieveMultiple

Many records with a query, in three scenarios: a condition and an order; several conditions together; and how many rows came back. Every value in a condition is sent as a parameter, never written into the SQL.

Paging, in three scenarios: one page with the total (in the same round trip); every page in a loop on HasNextPage; and a page past the end, which still knows the total.

A query on a service created with only a connection string. A query names its columns and conditions itself, so ARX reads nothing from the database first: it runs as it would with a catalog.

A contact search screen, in three scenarios: a free-text box (Contains, case-insensitive, on two columns with OR); filters the user ticked (an In list); and text with wildcards in it, searched literally.

Reading across tables, in three scenarios: a link entity (a join with its own alias, columns and criteria); a LEFT join that keeps rows without a match; and the short form, a direct join written in the column set (account_id._lk.account.name,account_number).

A report, in two scenarios: accounts per industry with their total and average revenue (aggregates and group by, ordered by an aggregate); and one number for a dashboard tile (a count with no grouping).

Dates in conditions, in three scenarios: a date column against a DateOnly; a timestamptz column against a UTC moment; and "on this day" for a timestamp, comparing only its date part. No hidden offset is added.

A view, read like a table, in two scenarios: the view as it is (no declaration needed: no RecordId); and the view declared with its id, so its rows carry a RecordId and Retrieve by id works.

A function that returns a table, read like a table, in three scenarios: with a named argument; filtered, ordered and paged on top; and with a positional argument. Function reads run read-only: a function that writes is refused unless the source says AllowWrites().

Lookups read back as references with a display name, in three scenarios: a contact's account, named by the account's name (declared in SampleCatalog.cs); an account without one; and the names turned off when a screen doesn't need them (one join fewer).

Read errors turned into messages, in two scenarios: what the database refuses (a table or column that doesn't exist, a value of the wrong type for its column); and what ARX refuses before the database (an id of the wrong type, an operator without the values it needs, a removed column form).

Catching an SQL injection attempt on a search, in four scenarios. The sort field and the filter field of a search screen come from the request: SQL in them is ARX1017 SuspectedInjection, refused before the database. A badly formed name is ARX1003, a well-formed misspelling ARX5002. SQL in a search value, the classic ' OR '1'='1, is a parameter: it matches nothing.

Reads in a batch scope, in two scenarios: an order created in the scope, read back to total it before the order header is updated, all in one transaction; and a read that fails in the scope, undone alone by its savepoint, so the scope carries on.

Exporting a large table, in two scenarios: every row in pages of 500 (each page one round trip, the memory of one page at a time); and why paging without an order is still safe with ARX (the declared id breaks ties).

A security check around a search endpoint (SecurityGuard, in the project root), in four scenarios: a normal search passes; a sort field carrying SQL is logged and blocked after three strikes; a field list checked first keeps unknown names away from ARX; and a false positive (a column label with parentheses) is triaged and mapped, without blocking anyone.

Filter expressions in depth, in five scenarios. An ArxFilterExpression holds conditions and other filter expressions, each with its own operator (And, Or): a tree, as deep as you need. Each child filter becomes one parenthesised group in the SQL, so the tree is the precedence.

1. A tree four levels deep, built by hand and printed by walking it recursively.
2. A saved search (a nested rule from a query builder screen) turned into filters by a recursive function.
3. A link entity with its own nested criteria, applied in the join.
4. Two filters built apart (the user's search and a security rule) joined under And, then under Or.
5. The same three conditions grouped two ways: And and Or change the result.

Link entities in depth, in five scenarios. An ArxLinkEntity joins one table and holds its own columns, criteria, alias, join type, and its own LinkEntities: a tree of joins, as deep as needed.

1. A chain four joins deep (order line → order → account → parent → grandparent), plus a second branch (product → category), printed by walking the tree.
2. Each level with its own join type and criteria.
3. Conditions and order on aliases deep in the tree, in the main criteria.
4. A self-join chain of ancestors, built by a recursive function.
5. A one-to-many link multiplies rows: aggregated per account.

Paging in depth, and how to keep it cheap, in six scenarios. The main choice is the total: it is opt-in (ReturnTotalRecordCount, false by default). Asked for, ARX sends a second statement in the same round trip, SELECT count(*) FROM (the query without its order and paging), 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 (HasNextPage: ARX reads one row more than the page).

1. The same page with and without the total: what each gives back.
2. What the total costs, timed on this machine (not a benchmark; RetrievePagingBenchmarks measures it).
3. "Page 1 of 40": ask for the total on the first page only, then page without it.
4. "Load more": never ask for the total; HasNextPage is enough.
5. One page only: Top (no extra row, no offset); Top together with PageInfo is ARX1001.
6. Deep pages: seek past the last row read (keyset paging) instead of an ever larger OFFSET.

The plans below assume the table has statistics. Right after a bulk load it has none until autovacuum analyses it (usually within a minute) or you run ANALYZE; until then PostgreSQL may scan and sort the whole table even for page 1, so the checks start with ANALYZE product.

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 ArxQueryExpression: criteria, links, order, paging, aggregates. Three scenarios (functions are Advanced8):

1. A view in another schema (reporting.vw_product_catalogue), linked to a table and aggregated.
2. A materialized view: refused with a hint while it was never refreshed; read after a refresh; a snapshot until the next one.
3. A partitioned table: written and read through its parent, PostgreSQL skipping the partitions the criteria rule out; one partition read by its own name.

An inheritance parent (it reads its children's rows too) and a foreign table (postgres_fdw) are read the same way; the tests cover the first.

Functions as row sources, every shape PostgreSQL has, in six scenarios. A function is named with Function (named arguments) or Function (positional), and the rest of the query works on its rows as on a table's. Every argument is a typed parameter. RealCase6 shows a RETURNS TABLE function with named and positional arguments; this sample shows the other shapes:

1. RETURNS SETOF account: rows of a table's type, with a criterion, a link entity and an order.
2. OUT parameters: one row, one column per OUT value.
3. A single value: one row, its column named after the function.
4. RETURNS SETOF record: no column list of its own, so the query declares it with Returns(...).
5. A function that writes: refused by a read (it runs read-only) unless the source says AllowWrites().
6. A procedure: not a row source (ARX8001); procedures are Execute's.

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 PostgreSQL runs the view's query inside the one ARX sends, so criteria, links, order and paging all apply. The sample schema names views vw_ (materialized views mv_, functions fn_, procedures sp_).

1. 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.
2. Links both ways: from a view to a table, and from a table to a view.
3. A view declared with its id and a lookup: Retrieve by id, a RecordId, the lookup read back with its display name, and the _lk direct join from a view's column.
4. A view as a column boundary: a directory without the contacts' private columns; asking it for one is ARX5002.
5. A view on a view: read like any other; PostgreSQL expands both into one query.

RealCase5 is the short version (a view as it is, and declared with its id); Advanced7 reads a view in another schema, and a materialized view.

Retrieve on a schema preloaded at service setup, in four scenarios: a lookup the preload found (account.primary_contact_id, which added contact) read back as a reference with the contact's name; an enum column read as text from the first read; Strict, where reading all columns means the preloaded ones and any other column is refused; and a preload saved as JSON, so the next startup needs no catalog queries.