# Retrieve with ARX.SDK on SQL Server

## 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 (in any schema: `reporting.vw_product_catalogue`), or a table-valued function (`ArxRowSource.Function("fn", value)`, arguments by position). Procedures and scalar functions are Execute's. The samples name views `vw_`, 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`, `datetime2` → `DateTime` (Kind `Unspecified`), `datetimeoffset` → `DateTimeOffset` with its offset; 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(...)` |
| **Isolation** | Reads on connections ARX opens are `READ COMMITTED`, whatever another user of the pooled connection left set. Under it a row another transaction changed but hasn't committed makes the read wait (until the commit or the command timeout); a database with `READ_COMMITTED_SNAPSHOT` on reads the last committed version instead |
| **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

| SQL Server column | Reads back as | Notes |
|---|---|---|
| `nvarchar(n)`, `varchar(n)`, `nchar(n)`, `nvarchar(max)` | `string` | `nchar(n)` keeps its padding spaces; `""` stays `""` |
| `tinyint` / `smallint` / `int` / `bigint` | `byte` / `short` / `int` / `long` | |
| `decimal(p, s)`, `money` | `decimal` | More than 28 significant digits: `ARX4001 ValueOutOfRange` |
| `real` / `float` | `float` / `double` | |
| `bit` | `bool` | |
| `uniqueidentifier` | `Guid` | |
| `datetimeoffset` | `DateTimeOffset` | The clock time and the offset it was stored with |
| `datetime2`, `datetime`, `smalldatetime` | `DateTime`, Kind `Unspecified` | The clock time as stored: SQL Server keeps no zone |
| `date` / `time` | `DateOnly` / `TimeOnly` | |
| `varbinary(n)`, `varbinary(max)`, `rowversion` | `byte[]` | |
| JSON in `nvarchar(max)` | `string` | SQL Server 2022 has no JSON type: the text as stored |
| `sql_variant` | the value's own type | |
| An alias type (`CREATE TYPE … FROM nvarchar(320)`) | 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 SQL Server | Reads back as |
|---|---|---|
| Count, CountBig, CountDistinct, CountBigDistinct | `COUNT_BIG(*)`, or of the attribute when one is named | `long` |
| Avg | `AVG(column * 1.0)`: SQL Server's own AVG of integers is an integer (2 and 3 give 2) | `decimal` |
| Sum, Min, Max | as they are (a Sum of `int` past 2³¹ is an overflow error, ARX4001, not a wrong value) | the column's type |

### 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 |
| ARX1008 InvalidConnection | A connection with an open transaction, given without that transaction |
| 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 `decimal` larger than a .NET `decimal` can hold; an aggregate that overflows |
| ARX4003 InvalidValueFormat | Text compared with a number column that doesn't convert (`'forty'` against an `int`) |
| ARX4004 DataTypeMismatch | A value of a type the column can't be compared with; a function given more arguments than it has |
| ARX5001, ARX5002 | A table, view or function that doesn't exist (a scalar function read as a table, too); a column that doesn't exist, or a selected column that isn't grouped |
| ARX6001 PermissionDenied | No SELECT permission |
| ARX7003 Timeout | `CommandTimeout` exceeded (a read waiting for another transaction's lock, too) |
| ARX8001 NotSupported | A removed column form (`._opt`, `ignoredefault.`); paging a source with no order and no declared id; a function with named arguments or declared result columns; full-text search on a column without a full-text index; a procedure |

## The samples

Every Retrieve sample, in reading order. Each scenario shows the code (verified against SQL Server) and the SQL that checks what it did. `service` is the application's `ArxMsSqlDbService`; values marked "In scope" come from earlier in the sample. What the samples' code refers to beyond the packages (SampleCatalog.cs, `dbContext`) is in the sample application file, arx-sql-sample-application.md.

### Retrieve one record

Basics 1 of Retrieve.

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.

Usings:

```csharp
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

#### 1. By the id Create returned

The usual way: the table, the id Create returned, and the columns to read.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);

var account = new ArxEntity("account");
account["name"] = "Northwind Traders";
account["account_number"] = "ACC-R101";
account["employee_count"] = 120;
ArxRecordId id = await service.CreateAsync(account);

ArxEntity read = await service.RetrieveAsync("account", id, new ArxColumnSet("name", "employee_count")) ?? throw new InvalidOperationException("The account record isn't there.");

// The id is always read too, so read.RecordId can update or delete this record later.
Console.WriteLine($"{read["name"]}, {read["employee_count"]} employees, RecordId {read.RecordId}");
```

Defined in the sample, used above:

```csharp
private static ArxMsSqlDbService Service(string connectionString) => new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Build());
```

Check it in the database:

```sql
SELECT name, employee_count
FROM account
WHERE account_number = 'ACC-R101';
```

Expect: 1 row, Northwind Traders, 120: what Retrieve returned.

#### 2. A text id

A text id: a country by its code. Without columns, every column is read.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);

var iceland = new ArxEntity("country");
iceland["code"] = "IS";
iceland["name"] = "Iceland";
await service.CreateAsync(iceland);

ArxEntity read = await service.RetrieveAsync("country", "IS") ?? throw new InvalidOperationException("The country record isn't there.");

Console.WriteLine($"{read["code"]} is {read["name"]} ({read.Attributes.Count} columns)");
```

Check it in the database:

```sql
SELECT code, name
FROM country
WHERE code = 'IS';
```

Expect: 1 row, IS, Iceland.

#### 3. An id nobody has

An id that doesn't exist: null, so "is it there?" needs no try/catch.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);

ArxEntity? read = await service.RetrieveAsync("account", 999_999_999L);

Console.WriteLine($"{(read == null ? "null" : "found")}");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE id = 999999999;
```

Expect: 0.

### Choose the columns to read

Basics 2 of Retrieve.

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.

Usings:

```csharp
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var service = new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build());
var account = new ArxEntity("account");
account["name"] = "Columns Ltd";
account["account_number"] = "ACC-R301";
account["website"] = "";
ArxRecordId id = await service.CreateAsync(account);
```

#### 1. Some columns

The columns asked for; the id comes too, so the entity can be updated or deleted later.

In scope: `service` (ArxMsSqlDbService), `id` (ArxRecordId).

```csharp
var read = await service.RetrieveAsync("account", id, new ArxColumnSet("name", "account_number")) ?? throw new InvalidOperationException("The account record isn't there.");

Console.WriteLine($"{string.Join(", ", read.Attributes.Select(x => x.Key))}");
```

Check it in the database:

```sql
SELECT id, name, account_number
FROM account
WHERE account_number = 'ACC-R301';
```

Expect: 1 row, Columns Ltd: the three attributes Retrieve returned.

#### 2. All columns

No column set (or `ArxColumnSet.All`): every column of the table.

In scope: `service` (ArxMsSqlDbService), `id` (ArxRecordId).

```csharp
var read = await service.RetrieveAsync("account", id) ?? throw new InvalidOperationException("The account record isn't there.");

Console.WriteLine($"{read.Attributes.Count} attributes, status {read["status"]}");
```

Check it in the database:

```sql
SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'account';
```

Expect: 17, the number of attributes Retrieve returned.

#### 3. Empty and NULL

An empty website is "", a missing one is null, and both are attributes: nothing is dropped.

In scope: `service` (ArxMsSqlDbService), `id` (ArxRecordId).

```csharp
var read = await service.RetrieveAsync("account", id, new ArxColumnSet("website", "annual_revenue")) ?? throw new InvalidOperationException("The account record isn't there.");

Console.WriteLine($"website \"{read["website"]}\", annual_revenue {(read["annual_revenue"] == null ? "null" : read["annual_revenue"])}, both present: {read.Contains("website") && read.Contains("annual_revenue")}");
```

Check it in the database:

```sql
SELECT CASE WHEN website = '' THEN 1 ELSE 0 END AS empty_website,
       CASE WHEN annual_revenue IS NULL THEN 1 ELSE 0 END AS no_revenue
FROM account
WHERE account_number = 'ACC-R301';
```

Expect: 1 row, 1, 1.

### Find a record by id, key or columns

Basics 3 of Retrieve.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var service = new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("contact", ArxIdColumn.Generated<long>("id")).Key("contact", "email")
    .Build());
var contacts = new List<ArxEntity>();
foreach (var (first, email) in new[] { ("Ines", "ines.r401@example.com"), ("Ivo", "ivo.r401@example.com") })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Find-R401";
    contact["email"] = email;
    contacts.Add(contact);
}
var ids = await service.CreateMultipleAsync(contacts);
```

#### 1. By id

By id, through an entity: the same as RetrieveAsync("contact", id).

In scope: `service` (ArxMsSqlDbService), `id` (ArxRecordId).

```csharp
var match = new ArxEntity("contact") { RecordId = id };

var read = await service.RetrieveAsync(match, new ArxRetrieveOptions { Columns = new ArxColumnSet("first_name") }) ?? throw new InvalidOperationException("The record isn't there.");

Console.WriteLine($"{read["first_name"]}");
```

Check it in the database:

```sql
SELECT first_name
FROM contact
WHERE email = 'ines.r401@example.com';
```

Expect: 1 row, Ines.

#### 2. By a declared key

By the declared key: the email is unique, so it finds one record.

In scope: `service` (ArxMsSqlDbService).

```csharp
var match = new ArxEntity("contact");
match["email"] = "ivo.r401@example.com";

var read = await service.RetrieveAsync(match, new ArxRetrieveOptions { Match = ArxMatch.Key("email"), Columns = new ArxColumnSet("first_name") }) ?? throw new InvalidOperationException("The record isn't there.");

Console.WriteLine($"{read["first_name"]}");
```

Check it in the database:

```sql
SELECT first_name
FROM contact
WHERE email = 'ivo.r401@example.com';
```

Expect: 1 row, Ivo.

#### 3. By any columns

By any columns: the first name and last name together, no declaration needed.

In scope: `service` (ArxMsSqlDbService).

```csharp
var match = new ArxEntity("contact");
match["first_name"] = "Ines";
match["last_name"] = "Find-R401";

var read = await service.RetrieveAsync(match, new ArxRetrieveOptions { Match = ArxMatch.Columns("first_name", "last_name"), Columns = new ArxColumnSet("email") }) ?? throw new InvalidOperationException("The record isn't there.");

Console.WriteLine($"{read["email"]}");
```

Check it in the database:

```sql
SELECT email
FROM contact
WHERE first_name = 'Ines' AND last_name = 'Find-R401';
```

Expect: 1 row, ines.r401@example.com.

#### 4. Several rows

A Retrieve answers one record: columns that match two rows are ARX2003. Use RetrieveMultiple for many.

In scope: `service` (ArxMsSqlDbService).

```csharp
var match = new ArxEntity("contact");
match["last_name"] = "Find-R401";

try
{
    await service.RetrieveAsync(match, new ArxRetrieveOptions { Match = ArxMatch.Columns("last_name") });
}
catch (ArxException error) when (error.Code == ArxErrorCode.MultipleRecordsMatched)
{
    Console.WriteLine($"{error.Code}");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE last_name = 'Find-R401';
```

Expect: 2: that's why one record couldn't be returned.

### Retrieve without a catalog

Basics 4 of Retrieve.

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.

Good to know:

A read by the id reads the table first, which makes it much slower than on a service with a catalog (Create Basic6 says when that is fine). What was read only finds the row: the values come back the same with or without a catalog. So, without one, a read sets no `RecordId` (ask for the id column, as scenario 2 does) and a lookup column comes back as its plain value, not an `ArxEntityReference`.

Usings:

```csharp
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var service = new ArxMsSqlDbService(connectionString);
var account = new ArxEntity("account");
account["name"] = "Wingtip Toys";
account["account_number"] = "ACC-N061";
account["employee_count"] = 75;
ArxRecordId id = await service.CreateAsync(account);
```

#### 1. By the id

The table and the id Create returned.

In scope: `service` (ArxMsSqlDbService), `id` (ArxRecordId).

```csharp
ArxEntity read = await service.RetrieveAsync("account", id, new ArxColumnSet("name", "employee_count")) ?? throw new InvalidOperationException("The account record isn't there.");

Console.WriteLine($"{read["name"]}, {read["employee_count"]} employees");
```

Check it in the database:

```sql
SELECT name, employee_count
FROM account
WHERE account_number = 'ACC-N061';
```

Expect: 1 row, Wingtip Toys, 75.

#### 2. By a key

By a key: the entity holds the value, the option names the column; ask for the id to keep it.

In scope: `service` (ArxMsSqlDbService).

```csharp
var match = new ArxEntity("account");
match["account_number"] = "ACC-N061";

ArxEntity read = await service.RetrieveAsync(match, new ArxRetrieveOptions
{
    Match = ArxMatch.Key("account_number"),
    Columns = new ArxColumnSet("id", "name")
}) ?? throw new InvalidOperationException("The record isn't there.");

Console.WriteLine($"{read["name"]}, id {read["id"]}");
```

Check it in the database:

```sql
SELECT id, name
FROM account
WHERE account_number = 'ACC-N061';
```

Expect: 1 row, Wingtip Toys.

### Every data type, written and read back

Real case 1 of Retrieve.

What each SQL Server 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.

Usings:

```csharp
using ARX.SDK.Models;
using ARX.SDK.Services;
```

#### 1. Written with Create, read with Retrieve

Write one value of each type, read the row, print each attribute's type and value.

In scope: `service` (ArxMsSqlDbService).

```csharp
var row = new ArxEntity("data_type_showcase");
row["text_value"] = "Read back R641";
row["char_value"] = "AB";
row["smallint_value"] = (short)7;
row["int_value"] = 42;
row["bigint_value"] = 9_000_000_000L;
row["decimal_value"] = 12345.678901m;
row["money_value"] = 19.99m;
row["real_value"] = 1.5f;
row["float_value"] = 2.25d;
row["bit_value"] = true;
row["uniqueidentifier_value"] = Guid.Parse("6c1b3a6e-7c2d-4c7e-9a51-2a9c6b0e1641");
row["datetimeoffset_value"] = new DateTimeOffset(2026, 4, 1, 11, 30, 0, TimeSpan.FromHours(2));
row["datetime2_value"] = new DateTime(2026, 4, 1, 9, 30, 0);
row["date_value"] = new DateOnly(2026, 4, 1);
row["time_value"] = new TimeOnly(9, 30);
row["varbinary_value"] = new byte[] { 1, 2, 3 };
row["json_value"] = new ArxJsonB { SerializedValue = "{\"tier\": \"gold\"}" };
row["mood"] = new ArxSelectionSetValue("Mood", "happy");
var id = await service.CreateAsync(row);

var read = await service.RetrieveAsync("data_type_showcase", id) ?? throw new InvalidOperationException("The data_type_showcase record isn't there.");

foreach (var column in new[] { "text_value", "char_value", "smallint_value", "int_value", "bigint_value", "decimal_value", "money_value", "real_value",
             "float_value", "bit_value", "uniqueidentifier_value", "datetimeoffset_value", "datetime2_value", "date_value", "time_value",
             "varbinary_value", "json_value", "mood" })
{
    var value = read[column];
    var text = value switch
    {
        ArxJsonB json => json.SerializedValue,
        DateTime dateTime => $"{dateTime:yyyy-MM-dd HH:mm} ({dateTime.Kind})",
        DateTimeOffset offset => $"{offset:yyyy-MM-dd HH:mm zzz}",
        Array array => "[" + string.Join(", ", array.Cast<object>()) + "]",
        _ => value?.ToString()
    };

    Console.WriteLine($"{column,-18} {value?.GetType().Name,-10} {text}");
}
```

Check it in the database:

```sql
SELECT char_value, date_value, time_value, datetimeoffset_value, json_value, mood
FROM data_type_showcase
WHERE text_value = 'Read back R641';
```

Expect: 1 row, "AB " (nchar(3) keeps its padding), 2026-04-01, 09:30:00.0000000, 2026-04-01 11:30:00.0000000 +02:00, {"tier": "gold"}, happy.

### Read your own writes in an ARX transaction

Advanced case 1 of Retrieve.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

#### 1. Inside the transaction, its writes are visible

Inside, the new account is there; outside, it isn't until the commit.

In scope: `service` (ArxMsSqlDbService), `connectionString` (string).

```csharp
await using var connection = new ArxMsSqlConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var account = new ArxEntity("account");
account["name"] = "Transacted Textiles";
account["account_number"] = "ACC-R711";
var id = await service.CreateAsync(account, transaction);

var inside = await service.RetrieveAsync("account", id, transaction, new ArxColumnSet("name"));
Console.WriteLine($"inside: {inside?["name"] ?? "not there"}");

// Outside, SQL Server's default READ COMMITTED waits for the uncommitted row instead of skipping it (unless the
// database turns READ_COMMITTED_SNAPSHOT on): here a 1-second timeout ends the wait.
var query = new ArxQueryExpression("account");
query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-R711");

try
{
    await service.RetrieveMultipleAsync(query, new ArxRetrieveOptions { CommandTimeout = 1 });
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.Timeout)
{
    Console.WriteLine("outside before commit: the read waits for the transaction (timed out after 1 s)");
}

await transaction.CommitAsync();
Console.WriteLine($"outside after commit: {(await service.RetrieveAsync("account", id))?["name"]}");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-R711';
```

Expect: 1 row, Transacted Textiles (committed).

#### 2. Read to check, then commit or roll back

An import of contacts, checked before commit: one too many for the account's limit, so it's rolled back.

In scope: `service` (ArxMsSqlDbService), `connectionString` (string).

```csharp
await using var connection = new ArxMsSqlConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var contacts = Enumerable.Range(1, 4).Select(i =>
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = $"Import {i}";
    contact["last_name"] = "Checked-R712";
    return contact;
}).ToList();

await service.CreateMultipleAsync(contacts, transaction);

var query = new ArxQueryExpression("contact") { AggregateInfo = new ArxAggregateInfo() };
query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Checked-R712");
query.AggregateInfo.AddAggregateColumn("imported", "", ArxAggregateOperator.Count);
var imported = (long)(await service.RetrieveMultipleAsync(query, transaction)).Entities[0]["imported"]!;

if (imported > 3)
{
    await transaction.RollbackAsync();
    Console.WriteLine($"{imported} contacts, more than the limit of 3: rolled back");
}
else
{
    await transaction.CommitAsync();
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE last_name = 'Checked-R712';
```

Expect: 0: the check saw 4 and rolled the import back.