# Retrieve with ARX.SDK on MySQL

## 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, or a view (in another database too: `arx_samples_reporting.vw_product_catalogue`). MySQL functions return one value, never rows, so a function as a row source (`ArxRowSource.Function`) is ARX8001 before anything is sent: read a table or view, or call a procedure through Execute. 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` (a `TimeSpan` past 24 hours), `DATETIME` → `DateTime` (Kind `Unspecified`), `TIMESTAMP` → `DateTime` (Kind `Utc` on ARX's connections, which run in UTC); 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 run under the server's default isolation (`REPEATABLE READ` unless it says otherwise). InnoDB's consistent reads never wait for another transaction: a row another transaction changed but hasn't committed reads as it was last committed. MySqlConnector resets a pooled connection's session when it is returned, so an isolation level another user left set never reaches an ARX read |
| **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

| MySQL column | Reads back as | Notes |
|---|---|---|
| `VARCHAR(n)`, `CHAR(n)`, `TEXT` | `string` | `CHAR(n)` drops its padding spaces when read; `""` stays `""` |
| `TINYINT` / `SMALLINT` / `INT` / `BIGINT` | `sbyte` / `short` / `int` / `long` | `TINYINT UNSIGNED` is a `byte` |
| `INT UNSIGNED`, `BIGINT UNSIGNED` | `uint`, `ulong` | |
| `DECIMAL(p, s)` | `decimal` | More than 28 significant digits: `ARX4001 ValueOutOfRange` |
| `FLOAT` / `DOUBLE` | `float` / `double` | |
| `BOOLEAN` (`TINYINT(1)`) | `bool` | |
| `CHAR(36)` | `Guid` | Text that isn't a Guid: `ARX5003 SchemaMismatch` |
| `BINARY(16)` | `byte[]`; a `Guid` for a declared Guid id | |
| `TIMESTAMP` | `DateTime`, Kind `Utc` | The instant, read in ARX's UTC session (on your own connection, in its session's zone) |
| `DATETIME` | `DateTime`, Kind `Unspecified` | The clock time as stored: MySQL keeps no zone |
| `DATE` / `TIME` | `DateOnly` / `TimeOnly` | A `TIME` past 24 hours or below zero is a `TimeSpan` |
| `BLOB`, `LONGBLOB`, `BINARY(n)` | `byte[]` | |
| `JSON` | `string` | MySQL's own form of the document |
| `ENUM`, `SET` | `string` | The label; a `SET`'s labels comma-separated |
| 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 MySQL | Reads back as |
|---|---|---|
| Count, CountBig, CountDistinct, CountBigDistinct | `COUNT(*)`, or of the attribute when one is named | `long` |
| Avg | `AVG`: of integers, a `DECIMAL` (2 and 3 give 2.5000) | `decimal` |
| Sum | of integers, a `DECIMAL`, so it never overflows | `decimal` |
| Min, Max | as they are | the column's type |
| StDev, StDevP, Var, VarP | `STDDEV_SAMP`, `STDDEV_POP`, `VAR_SAMP`, `VAR_POP` | `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 |
| 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 |
| ARX4004 DataTypeMismatch | A value of a type the column can't be compared with; text of different collations compared |
| ARX5001, ARX5002 | A table or view that doesn't exist; a column that doesn't exist, or a selected column that isn't grouped (`ONLY_FULL_GROUP_BY`) |
| ARX5003 SchemaMismatch | A value that isn't of the declared type (a `CHAR(36)` id that isn't a Guid) |
| ARX6001 PermissionDenied | No SELECT permission |
| ARX7003 Timeout | `CommandTimeout` exceeded |
| ARX8001 NotSupported | A function as a row source (MySQL's return one value); a removed column form (`._opt`, `ignoredefault.`); full-text search on a column without a `FULLTEXT` index |

## The samples

Every Retrieve sample, in reading order. Each scenario shows the code (verified against MySQL) and the SQL that checks what it did. `service` is the application's `ArxMySqlDbService`; 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-mysql-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.MySQL.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 ArxMySqlDbService Service(string connectionString) => new ArxMySqlDbService(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.MySQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var service = new ArxMySqlDbService(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` (ArxMySqlDbService), `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` (ArxMySqlDbService), `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_schema = DATABASE() AND 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` (ArxMySqlDbService), `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 website = '' AS empty_website, annual_revenue IS NULL 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.MySQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var service = new ArxMySqlDbService(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` (ArxMySqlDbService), `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` (ArxMySqlDbService).

```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` (ArxMySqlDbService).

```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` (ArxMySqlDbService).

```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.MySQL.Services;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var service = new ArxMySqlDbService(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` (ArxMySqlDbService), `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` (ArxMySqlDbService).

```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 MySQL 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` (ArxMySqlDbService).

```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["unsigned_value"] = 4_000_000_000u;
row["decimal_value"] = 12345.678901m;
row["float_value"] = 1.5f;
row["double_value"] = 2.25d;
row["bool_value"] = true;
row["uuid_value"] = Guid.Parse("6c1b3a6e-7c2d-4c7e-9a51-2a9c6b0e1641");
row["timestamp_value"] = new DateTimeOffset(2026, 4, 1, 11, 30, 0, TimeSpan.FromHours(2));
row["datetime_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["blob_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", "unsigned_value", "decimal_value", "float_value",
             "double_value", "bool_value", "uuid_value", "timestamp_value", "datetime_value", "date_value", "time_value",
             "blob_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
SET time_zone = '+00:00';
SELECT char_value, date_value, time_value, timestamp_value, json_value, mood
FROM data_type_showcase
WHERE text_value = 'Read back R641';
```

Expect: 1 row, AB (CHAR(3) drops its padding when read), 2026-04-01, 09:30:00.000000, 2026-04-01 09:30:00.000000 (the TIMESTAMP's instant in UTC), {"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.MySQL.Services;
using ARX.SDK.Query;
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` (ArxMySqlDbService), `connectionString` (string).

```csharp
await using var connection = new ArxMySqlConnection(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, InnoDB's consistent read shows the last committed data without waiting: the uncommitted row isn't there.
var query = new ArxQueryExpression("account");
query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-R711");

var outside = (await service.RetrieveMultipleAsync(query)).Entities.Count;
Console.WriteLine($"outside before commit: {outside} rows, without waiting for the transaction");

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` (ArxMySqlDbService), `connectionString` (string).

```csharp
await using var connection = new ArxMySqlConnection(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.