Retrieve · PostgreSQL in C#

What a read does


Retrieve	One record by id (`RetrieveAsync("account", id, columns)`), or by a key or columns (`RetrieveAsync(entity, new ArxRetrieveOptions { Match = ArxMatch.Key("email") })`). Null when nothing matches; ARX2003 when a key or columns match more than one row
What it reads from	A table, a view, a materialized view, a partitioned or inherited table, a foreign table, or a function that returns rows (`ArxRowSource.Function("fn", ("p_arg", value))`). Procedures and cursors are Execute's. The samples name views `vw_`, materialized views `mv_`, functions `fn_` and procedures `sp_`
Columns	The ones asked for, plus the declared id (so `RecordId` is set); none means all. `column._lk.table.c1,c2` joins the referenced table directly (columns `column.c1`); a link entity's columns come back as `alias.column`
Values read back	`date` → `DateOnly`, `time` → `TimeOnly`, `timestamptz` → UTC `DateTime`, `jsonb` → `ArxJsonB`, enums → their label, arrays → `T[]` (`T?[]` with NULLs); NULL is a null attribute, `""` stays `""`
Lookups	A column declared with `.Lookup(...)` reads back as an `ArxEntityReference`, its `Name` filled from the referenced table's `.DisplayName(...)`
Functions	On a connection ARX opens itself, read in a read-only transaction: a function that writes is refused (ARX6001) unless the source says `.AllowWrites()`. Inside an `ArxTransaction` or a batch scope, or on a connection you pass in, the read runs in that transaction as it is, so a function that writes does write: ARX can't make your transaction read-only. Grant the database user only what the function needs
Security	A name carrying SQL (a sort field, a filter field, a table from a request) is ARX1017, refused before the database; a name that breaks the name rules (a space, a hyphen) is ARX1003, and a well-formed name the table doesn't have (a misspelling) comes back from the database as ARX5002
Without a catalog	By id or key, the table is read from the database first to find the row (one more round trip per call, so much slower than with a catalog: meant for simple CRUD and small applications). The values come back as they would with a catalog, so without one a read sets no `RecordId` (ask for the id column) and a lookup column is its plain value (Basic4)

Reference

Value types read back

PostgreSQL column	Reads back as	Notes
`text`, `varchar(n)`, `char(n)`	`string`	`char(n)` keeps its padding spaces; `""` stays `""`
`smallint` / `integer` / `bigint`	`short` / `int` / `long`
`numeric(p, s)`	`decimal`	More than 28 significant digits: `ARX4001 ValueOutOfRange`. `NaN`, `Infinity` and `-Infinity`, which a `numeric` can hold and a `decimal` can't, read back as a `double` (from 0.1.0-preview.5; the read failed before)
`real` / `double precision`	`float` / `double`
`boolean`	`bool`
`uuid`	`Guid`
`timestamptz`	`DateTime`, Kind `Utc`	The instant, in UTC whatever the session's time zone
`timestamp`	`DateTime`, Kind `Unspecified`	The wall-clock time as stored
`date` / `time` / `interval`	`DateOnly` / `TimeOnly` / `TimeSpan`	An interval with months or years: `ARX4004 DataTypeMismatch` (a `TimeSpan` can't hold one)
`bytea`	`byte[]`
`json`, `jsonb`	`ArxJsonB`	`SerializedValue` holds the JSON text
An array (`text[]`, `int[]`, `date[]`, …)	`T[]` (`string[]`, `int[]`, `DateOnly[]`, …)	With NULL elements, a value type reads as `T?[]` (`int?[]`)
An enum type	`string`	Its label, from the first read when the schema is preloaded
A composite type	`string`	Its text form
A domain	Its base type
A declared lookup (`.Lookup(...)`)	`ArxEntityReference`	`Name` from the referenced table's `.DisplayName(...)`
A selection set column	Its stored code	`entity.GetSelectionSet(column, set, catalog)` gives the option
NULL	`null`	The attribute is present, with a null value

RealCase1_EveryDataTypeReadBack writes one row of every type with Create and prints each value's .NET type as it reads back.

Aggregates

Aggregate	On PostgreSQL	Reads back as
Count, CountBig, CountDistinct, CountBigDistinct	`count(*)`, or of the attribute when one is named	`long`
Sum	of `smallint` or `integer`, a `bigint`; of `bigint` or `numeric`, a `numeric`; of floating point, a `double precision`	`long`, `decimal` or `double`
Avg	of integers or `numeric`, a `numeric` (2 and 3 give 2.5); of floating point, a `double precision`	`decimal` or `double`
Min, Max	as they are	the column's type
StDev, StDevP, Var, VarP	`stddev_samp`, `stddev_pop`, `var_samp`, `var_pop`, in the same types as Avg	`decimal` or `double`

Error codes a read can raise

Code	When
ARX1001 InvalidArgument	An id of the wrong type or one that can't exist; a NoIdColumn table read by id; an operator without the values it needs (Between with one value, Equal with NULL, NULL in an In list); paging values below 1; Top with PageInfo; an alias used twice or reserved; a link asking for all columns
ARX1002, ARX1003, ARX1004	A missing table name; an invalid name; a column asked for twice
ARX1016 IdNotDeclared	Retrieve by id or key on an undeclared table; a `_lk` join on an undeclared table without `~column`
ARX1017 SuspectedInjection	A table, column, alias, condition, order, group-by, aggregate, function or argument name carrying SQL
ARX2003 MultipleRecordsMatched	Retrieve by a key or columns found more than one row
ARX4001 ValueOutOfRange	A `numeric` larger than a .NET `decimal` can hold
ARX4004 DataTypeMismatch	A value of the wrong type for its column; Contains on a number; an interval with months; no function with those argument types
ARX5001, ARX5002	A table or column that doesn't exist; a selected column that isn't grouped
ARX6001 PermissionDenied	No SELECT permission; a function that writes, read without `AllowWrites()` on a connection ARX opens
ARX7003 Timeout	`CommandTimeout` exceeded
ARX8001 NotSupported	A removed column form (`._opt`, `ignoredefault.`); a materialized view never refreshed; a `SETOF record` function without declared columns; a procedure

The samples

Basics

The least code that works.

1Retrieve one recordStandalone

The smallest read: one record, in three scenarios. The usual way first (the id CreateAsync returned, and the columns you want), then a text id, then an id nobody has: Retrieve returns null, not an error.

2Choose the columns to readStandalone

Which columns come back, in three scenarios: the ones you ask for (plus the id); all of them; and how an empty value differs from a NULL one.

3Find a record by id, key or columnsStandalone

The three ways to find one record, in four scenarios: by id, by a declared key, by any columns, and what happens when columns match more than one row.

4Retrieve without a catalogStandalone

One record read on a service created with only a connection string, in two scenarios: by the id, which ARX finds from the table's primary key; and by a key, the unique account number.

Real cases

What an application does: a small CRM and product catalogue.

1Every data type, written and read backProgressive

What each PostgreSQL type reads back as, in one scenario: a row of data_type_showcase is written with Create and read with Retrieve, and each attribute's .NET type is printed.

Advanced cases

Dependency injection, transactions, batch scopes and setting the service up.

1Read your own writes in an ARX transactionAdvanced

Reads inside an ARX transaction, in two scenarios: a read sees the transaction's own uncommitted writes, another connection doesn't; and a check that reads before it commits, rolling back when the check fails.