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 TIMESTAMP column against a UTC moment; and "on this day" for a moment, 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.

Functions as row sources, on MySQL, in three scenarios: a function as a query's row source is refused before anything is sent, since MySQL functions return one value, never rows (ARX8001); the same rows read from the table, filtered, ordered and paged; and the same rows from a procedure, called through Execute. On PostgreSQL and SQL Server this sample reads a table-valued function.

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's statistics are current. MySQL 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 ANALYZE TABLE product.

RetrieveMultiple reads more than tables: whatever name the query is given, MySQL resolves it, so a view, a view in another schema or a table without an id is read with the same ArxQueryExpression: criteria, links, order, paging, aggregates. Three scenarios (functions are Advanced8):

1. A view in another database, MySQL's schema (arx_samples_reporting.vw_product_catalogue), linked to a table and aggregated.
2. A summary view: it runs its query at each read, so a new order shows at once.
3. A table with no id (web_visit), read with a date range and a link.

MySQL has no materialized views and no inheritance; a partitioned table is read like any table: partitions are invisible to statements.

Functions as row sources on MySQL, in five scenarios. MySQL functions return one value, never rows, so Function is refused before anything is sent (ARX8001), and each shape a table-valued function has elsewhere has its MySQL form:

1. A table's rows, filtered and linked: read the table itself.
2. One row of totals: a procedure's result set, through Execute.
3. A scalar function: its value through Execute; as a row source, refused.
4. The refusal still checks the names first: SQL in a function's name is ARX1017, not ARX8001.
5. A procedure: not a row source either; 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 MySQL 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; MySQL 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 what stands in for a materialized view, which MySQL doesn't have.

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; a column of fixed text values (a CHECK lists them; an ENUM column works the same way) and its preloaded type; 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.