# RetrieveMultiple with ARX.SDK on SQL Server

## What a RetrieveMultiple does

| | |
|---|---|
| **RetrieveMultiple** | A query (`ArxQueryExpression`): columns, criteria (nested AND/OR), link entities, order, paging with the total in the same round trip, distinct, aggregates and group by |
| **Paging** | `PageInfo` (page size and number), sent as `OFFSET … ROWS FETCH NEXT … ROWS ONLY`. SQL Server needs an order to page: ARX adds the declared id to break ties, and a source with neither an order nor a declared id is ARX8001. `HasNextPage` is always right (ARX reads one row more than the page). The total is **opt-in** (`ReturnTotalRecordCount = true`): a second statement in the same round trip that counts every matching row, so leave it off when the screen doesn't show it, or ask on the first page only |
| **Values** | Every value in a condition is a parameter (an In list of more than 1,000 values is one JSON parameter). Contains, BeginsWith and EndsWith use `LIKE … ESCAPE`, so `%`, `_` and `[` are found literally; whether they ignore case is the column's collation's choice (the default ignores it) |
| **Aggregates** | Count is a `long` on every provider. Sum and Avg read back in the database's own type (the Retrieve page's Aggregates table): code that runs on several providers reads them with `Convert.ToDecimal(row["total"])`. |
| **Distinct** | `query.Distinct = true` returns rows that differ in no selected column once. The id isn't added to a distinct (or aggregate) query, since it would make every row distinct, so its rows have no `RecordId`: select the id column if you need it |
| **Without a catalog** | Nothing is read first: a query costs the same with or without a catalog. Without one, rows have no `RecordId` (ask for the id column) and a lookup column is its plain value (Basic3) |

## The samples

Every RetrieveMultiple 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 many records with a query

Basics 1 of 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.

Usings:

```csharp
using ARX.SDK.Query;
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 accounts = new List<ArxEntity>();
foreach (var (number, name, employees) in new[] { ("ACC-R201", "Basic Bikes", 12), ("ACC-R202", "Basic Boats", 340), ("ACC-R203", "Basic Books", 45) })
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    account["employee_count"] = employees;
    accounts.Add(account);
}
await service.CreateMultipleAsync(accounts);
```

#### 1. A condition and an order

The accounts whose name begins with "Basic B", by name.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumns("name", "employee_count");
query.Criteria.AddCondition("name", ArxConditionOperator.BeginsWith, "Basic B");
query.AddOrder("name", ArxOrderType.Ascending);

ArxEntityCollection result = await service.RetrieveMultipleAsync(query);

Console.WriteLine($"{string.Join(", ", result.Entities.Select(x => x["name"]))}");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE name LIKE 'Basic B%'
ORDER BY name;
```

Expect: 3 rows, Basic Bikes, Basic Boats, Basic Books.

#### 2. Several conditions

Conditions in the criteria are joined with AND: "Basic" accounts with 40 employees or more.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumns("name", "employee_count");
query.Criteria.AddCondition("name", ArxConditionOperator.BeginsWith, "Basic B");
query.Criteria.AddCondition("employee_count", ArxConditionOperator.GreaterEqual, 40);
query.AddOrder("employee_count", ArxOrderType.Descending);

var result = await service.RetrieveMultipleAsync(query);

Console.WriteLine($"{string.Join(", ", result.Entities.Select(x => $"{x["name"]} ({x["employee_count"]})"))}");
```

Check it in the database:

```sql
SELECT name, employee_count
FROM account
WHERE name LIKE 'Basic B%' AND employee_count >= 40
ORDER BY employee_count DESC;
```

Expect: 2 rows, Basic Boats 340, Basic Books 45.

#### 3. How many came back

Without paging, TotalRecordCount is the number of rows that came back.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumn("name");
query.Criteria.AddCondition("account_number", ArxConditionOperator.In, "ACC-R201", "ACC-R203", "ACC-R999");

var result = await service.RetrieveMultipleAsync(query);

Console.WriteLine($"{result.TotalRecordCount} of the 3 numbers asked for");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number IN ('ACC-R201', 'ACC-R203', 'ACC-R999');
```

Expect: 2 (nobody has ACC-R999).

### Page through results

Basics 2 of RetrieveMultiple.

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.

Usings:

```csharp
using ARX.SDK.Query;
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("product", ArxIdColumn.Generated<long>("id"))
    .Build());
var products = Enumerable.Range(1, 7).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"PAGE-R50{i}";
    product["name"] = $"Paged product {i}";
    product["unit_price"] = 10m + i;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. One page

Page 1 of 3 rows, with the total: one round trip.

In scope: `service` (ArxMsSqlDbService).

```csharp
var page = await service.RetrieveMultipleAsync(Products(1, total: true));

Console.WriteLine($"{string.Join(", ", page.Entities.Select(x => x["sku"]))}; {page.TotalRecordCount} in all, {page.TotalPageCount} pages, next page: {page.HasNextPage}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Products(int pageNumber, bool total)
{
    var query = new ArxQueryExpression("product");
    query.ColumnSet.AddColumns("sku", "name");
    query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, "PAGE-R50");
    query.AddOrder("sku", ArxOrderType.Ascending);
    query.PageInfo = new ArxPagingInfo { Count = 3, PageNumber = pageNumber, ReturnTotalRecordCount = total };
    return query;
}
```

Check it in the database:

```sql
SELECT sku
FROM product
WHERE sku LIKE 'PAGE-R50%'
ORDER BY sku
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;
```

Expect: 3 rows, PAGE-R501, PAGE-R502, PAGE-R503 (and 7 rows without the FETCH).

#### 2. Every page

Every page: HasNextPage is right without the total (ARX reads one row more than the page).

In scope: `service` (ArxMsSqlDbService).

```csharp
var read = new List<string>();
var pageNumber = 1;
ArxEntityCollection page;

do
{
    page = await service.RetrieveMultipleAsync(Products(pageNumber++, total: false));
    read.AddRange(page.Entities.Select(x => (string)x["sku"]!));
}
while (page.HasNextPage);

Console.WriteLine($"{read.Count} products in {pageNumber - 1} pages");
```

Check it in the database:

```sql
SELECT count(*)
FROM product
WHERE sku LIKE 'PAGE-R50%';
```

Expect: 7, the products the loop read.

#### 3. Past the end

A page past the end: no rows, and the total is still there.

In scope: `service` (ArxMsSqlDbService).

```csharp
var page = await service.RetrieveMultipleAsync(Products(9, total: true));

Console.WriteLine($"{page.Entities.Count} rows, total {page.TotalRecordCount}");
```

Check it in the database:

```sql
SELECT count(*)
FROM product
WHERE sku LIKE 'PAGE-R50%';
```

Expect: 7, the total page 9 reported.

### Query without a catalog

Basics 3 of RetrieveMultiple.

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.

Good to know:

Without a catalog the values come back as the database returns them: no `RecordId` on the rows (ask for the id column to keep it), and a lookup column is its plain value, not an `ArxEntityReference`. Declare the table in a catalog for both.

Usings:

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

#### 1. A query

Columns, a condition and an order: nothing to resolve.

In scope: `connectionString` (string).

```csharp
var service = new ArxMsSqlDbService(connectionString);

var accounts = new List<ArxEntity>();
foreach (var (name, number, employees) in new[] { ("Coho Winery", "ACC-N071", 30), ("Fourth Coffee", "ACC-N072", 18) })
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    account["employee_count"] = employees;
    accounts.Add(account);
}

await service.CreateMultipleAsync(accounts);

var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumns("name", "employee_count");
query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-N07");
query.AddOrder("name", ArxOrderType.Ascending);

ArxEntityCollection rows = await service.RetrieveMultipleAsync(query);

foreach (var row in rows.Entities)
{
    Console.WriteLine($"{row["name"]}, {row["employee_count"]} employees");
}
```

Check it in the database:

```sql
SELECT name, employee_count
FROM account
WHERE account_number LIKE 'ACC-N07%'
ORDER BY name;
```

Expect: 2 rows, Coho Winery 30, Fourth Coffee 18.

### A contact search screen

Real case 1 of RetrieveMultiple.

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.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var contacts = new List<ArxEntity>();
foreach (var (first, last, title, channel) in new[]
{
    ("Maria", "Search-R601", "Sales Manager", "E"),
    ("Mario", "Search-R601", "Engineer", "P"),
    ("Anna", "Marino-R601", "100% remote engineer", "E"),
    ("Tom", "Search-R601", "Buyer", "S"),
})
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = last;
    contact["job_title"] = title;
    contact["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", channel[0]);
    contacts.Add(contact);
}
await service.CreateMultipleAsync(contacts);
```

#### 1. A free-text box: first or last name contains "mari"

The user types "mari": first name or last name contains it, in any case.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();
var text = query.Criteria.AddFilter(ArxLogicalOperator.Or);
text.AddCondition("first_name", ArxConditionOperator.Contains, "mari");
text.AddCondition("last_name", ArxConditionOperator.Contains, "mari");

var found = await service.RetrieveMultipleAsync(query);
Console.WriteLine(string.Join(", ", found.Entities.Select(x => $"{x["first_name"]} {x["last_name"]}")));
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Contacts()
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("first_name", "last_name", "job_title");
    var ours = query.Criteria.AddFilter(ArxLogicalOperator.Or);
    ours.AddCondition("last_name", ArxConditionOperator.EndsWith, "-R601");
    query.AddOrder("first_name", ArxOrderType.Ascending);
    return query;
}
```

Check it in the database:

```sql
SELECT first_name, last_name
FROM contact
WHERE last_name LIKE '%-R601' AND (first_name LIKE '%mari%' OR last_name LIKE '%mari%')
ORDER BY first_name;
```

Expect: 3 rows, Anna Marino-R601, Maria Search-R601, Mario Search-R601.

#### 2. Ticked filters: preferred channel e-mail or phone

The user ticked "e-mail" and "phone" as preferred channels: one In list, one parameter a value.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();
query.Criteria.AddCondition("preferred_channel", ArxConditionOperator.In, "E", "P");

var found = await service.RetrieveMultipleAsync(query);
Console.WriteLine(string.Join(", ", found.Entities.Select(x => x["first_name"])));
```

Check it in the database:

```sql
SELECT first_name
FROM contact
WHERE last_name LIKE '%-R601' AND preferred_channel IN ('E', 'P')
ORDER BY first_name;
```

Expect: 3 rows, Anna, Maria, Mario.

#### 3. "100%" is searched as text, not as a pattern

"100%" typed into the box: the % is a character to find, not a wildcard.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();
query.Criteria.AddCondition("job_title", ArxConditionOperator.Contains, "100%");

var found = await service.RetrieveMultipleAsync(query);
Console.WriteLine(string.Join(", ", found.Entities.Select(x => $"{x["first_name"]}: {x["job_title"]}")));
```

Check it in the database:

```sql
SELECT first_name, job_title
FROM contact
WHERE last_name LIKE '%-R601' AND CHARINDEX('100%', job_title) > 0;
```

Expect: 1 row, Anna, 100% remote engineer.

### Accounts and contacts in one query

Real case 2 of RetrieveMultiple.

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

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Linked Logistics";
account["account_number"] = "ACC-R602";
account["status"] = new ArxSelectionSetValue("AccountStatus", "ACTIVE");
var accountId = await service.CreateAsync(account);
var contacts = new List<ArxEntity>();
foreach (var (first, withAccount) in new[] { ("Lena", true), ("Lars", true), ("Lone", false) })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Link-R602";
    contact["account_id"] = withAccount ? new ArxEntityReference("account", accountId) : null;
    contacts.Add(contact);
}
await service.CreateMultipleAsync(contacts);
```

#### 1. A link entity: each contact with its account

Contacts with their account's name and number: an inner join; the columns come back as `acc.name`.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();
var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Inner);
account.EntityAlias = "acc";
account.ColumnSet.AddColumns("name", "account_number");
account.LinkCriteria.AddCondition("status", ArxConditionOperator.Equal, "ACTIVE");

var rows = await service.RetrieveMultipleAsync(query);

foreach (var row in rows.Entities)
{
    Console.WriteLine($"{row["first_name"]} works at {row["acc.name"]} ({row["acc.account_number"]})");
}
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Contacts()
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumn("first_name");
    query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Link-R602");
    query.AddOrder("first_name", ArxOrderType.Ascending);
    return query;
}
```

Check it in the database:

```sql
SELECT c.first_name, a.name, a.account_number
FROM contact c
JOIN account a ON a.id = c.account_id AND a.status = 'ACTIVE'
WHERE c.last_name = 'Link-R602'
ORDER BY c.first_name;
```

Expect: 2 rows, Lars and Lena at Linked Logistics (ACC-R602).

#### 2. A LEFT join keeps the contact without an account

A LEFT join: Lone has no account and is kept, with a null account name.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();
var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Left);
account.EntityAlias = "acc";
account.ColumnSet.AddColumn("name");

var rows = await service.RetrieveMultipleAsync(query);
Console.WriteLine(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]}: {x["acc.name"] ?? "(no account)"}")));
```

Check it in the database:

```sql
SELECT c.first_name, a.name
FROM contact c
LEFT JOIN account a ON a.id = c.account_id
WHERE c.last_name = 'Link-R602'
ORDER BY c.first_name;
```

Expect: 3 rows, Lars and Lena with Linked Logistics, Lone with NULL.

#### 3. A direct join written in the column set

The short form: `account_id._lk.account.name,account_number` in the column set joins the account on its declared id and reads its columns as `account_id.name` and `account_id.account_number`.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();
query.ColumnSet.AddColumn("account_id._lk.account.name,account_number");

var rows = await service.RetrieveMultipleAsync(query);
Console.WriteLine(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]}: {x["account_id.account_number"] ?? "-"}")));
```

Check it in the database:

```sql
SELECT c.first_name, a.account_number
FROM contact c
LEFT JOIN account a ON a.id = c.account_id
WHERE c.last_name = 'Link-R602'
ORDER BY c.first_name;
```

Expect: 3 rows, Lars ACC-R602, Lena ACC-R602, Lone NULL (a direct join is a LEFT join).

### A report: accounts by industry

Real case 3 of RetrieveMultiple.

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).

Usings:

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

Set up by the sample before its scenarios:

```csharp
var accounts = new List<ArxEntity>();
var rows = new (string Number, Industry Industry, decimal Revenue)[]
{
    ("ACC-R631", Industry.Retail, 1_000_000m),
    ("ACC-R632", Industry.Retail, 3_000_000m),
    ("ACC-R633", Industry.Finance, 9_000_000m),
    ("ACC-R634", Industry.Technology, 2_500_000m),
    ("ACC-R635", Industry.Technology, 1_500_000m),
    ("ACC-R636", Industry.Technology, 500_000m),
};
foreach (var (number, industry, revenue) in rows)
{
    var account = new ArxEntity("account");
    account["name"] = "Report " + number;
    account["account_number"] = number;
    account["industry"] = new ArxSelectionSetValue("Industry", industry);
    account["annual_revenue"] = revenue;
    accounts.Add(account);
}
await service.CreateMultipleAsync(accounts);
```

#### 1. Accounts, total and average revenue per industry

Count, sum and average per industry, the industries with most accounts first.

In scope: `service` (ArxMsSqlDbService), `catalog` (ArxSchemaCatalog).

```csharp
var query = new ArxQueryExpression("account") { AggregateInfo = new ArxAggregateInfo() };
query.ColumnSet.AddColumn("industry");
query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R63");
query.AggregateInfo.AddAggregateColumn("accounts", "", ArxAggregateOperator.Count);
query.AggregateInfo.AddAggregateColumn("total_revenue", "annual_revenue", ArxAggregateOperator.Sum);
query.AggregateInfo.AddAggregateColumn("average_revenue", "annual_revenue", ArxAggregateOperator.Avg);
query.AggregateInfo.AddGroupByColumn("industry");
query.AddOrder("accounts", ArxOrderType.Descending);

var report = await service.RetrieveMultipleAsync(query);

foreach (var row in report.Entities)
{
    // The raw code read back; its option from the catalog.
    var industry = row.GetSelectionSet("industry", "Industry", catalog)?.DisplayText;
    Console.WriteLine($"{industry,-12} {row["accounts"]} accounts, total {row["total_revenue"]:N0}, average {(decimal)row["average_revenue"]!:N0}");
}
```

Check it in the database:

```sql
SELECT industry, count(*), sum(annual_revenue), CAST(AVG(annual_revenue) AS decimal(15, 0))
FROM account
WHERE account_number LIKE 'ACC-R63%'
GROUP BY industry
ORDER BY count(*) DESC;
```

Expect: 3 rows, 5 (Technology) 3 4500000.00 1500000, 1 (Retail) 2 4000000.00 2000000, 4 (Finance) 1 9000000.00 9000000.

#### 2. One number for a dashboard tile

One number for a dashboard: how many of these accounts earn a million or more.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account") { AggregateInfo = new ArxAggregateInfo() };
query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R63");
query.Criteria.AddCondition("annual_revenue", ArxConditionOperator.GreaterEqual, 1_000_000m);
query.AggregateInfo.AddAggregateColumn("big_accounts", "", ArxAggregateOperator.Count);

var tile = (await service.RetrieveMultipleAsync(query)).Entities[0];
Console.WriteLine($"{tile["big_accounts"]} accounts with a revenue of a million or more");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number LIKE 'ACC-R63%' AND annual_revenue >= 1000000;
```

Expect: 5.

### Dates and times in conditions

Real case 4 of RetrieveMultiple.

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

Usings:

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

Set up by the sample before its scenarios:

```csharp
var accounts = new List<ArxEntity>();
foreach (var (number, founded) in new[] { ("ACC-R651", new DateOnly(1999, 12, 31)), ("ACC-R652", new DateOnly(2000, 1, 1)), ("ACC-R653", new DateOnly(2010, 6, 15)) })
{
    var account = new ArxEntity("account");
    account["name"] = "Dated " + number;
    account["account_number"] = number;
    account["founded_on"] = founded;
    account["modified_on"] = new DateTime(2026, 2, 1, 23, 30, 0, DateTimeKind.Utc).AddDays(int.Parse(number[^1..]) - 1);
    accounts.Add(account);
}
await service.CreateMultipleAsync(accounts);
```

#### 1. Founded on or after 1 January 2000

Founded in 2000 or later: a date column and a DateOnly value.

In scope: `service` (ArxMsSqlDbService).

```csharp
Console.WriteLine(await NumbersAsync(service, "founded_on", ArxConditionOperator.GreaterEqual, new DateOnly(2000, 1, 1)));
```

Defined in the sample, used above:

```csharp
private static async Task<string> NumbersAsync(ArxMsSqlDbService service, string attribute, ArxConditionOperator op, object value)
{
    var query = new ArxQueryExpression("account");
    query.ColumnSet.AddColumn("account_number");
    query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R65");
    query.Criteria.AddCondition(attribute, op, value);
    query.AddOrder("account_number", ArxOrderType.Ascending);
    var rows = await service.RetrieveMultipleAsync(query);
    return string.Join(", ", rows.Entities.Select(x => x["account_number"]));
}
```

Check it in the database:

```sql
SELECT account_number
FROM account
WHERE account_number LIKE 'ACC-R65%' AND founded_on >= '2000-01-01'
ORDER BY account_number;
```

Expect: 2 rows, ACC-R652, ACC-R653.

#### 2. Changed after 2 February 2026, 23:00 UTC

Changed after a UTC moment: a datetimeoffset column and a UTC DateTime, compared to the second.

In scope: `service` (ArxMsSqlDbService).

```csharp
Console.WriteLine(await NumbersAsync(service, "modified_on", ArxConditionOperator.GreaterThan, new DateTime(2026, 2, 2, 23, 0, 0, DateTimeKind.Utc)));
```

Check it in the database:

```sql
SELECT account_number
FROM account
WHERE account_number LIKE 'ACC-R65%' AND modified_on > CAST('2026-02-02 23:00:00 +00:00' AS datetimeoffset)
ORDER BY account_number;
```

Expect: 2 rows, ACC-R652 (23:30 on the 2nd), ACC-R653.

#### 3. Changed on 1 February 2026 (any time that day)

Changed on 1 February: only the date part of the moment is compared.

In scope: `service` (ArxMsSqlDbService).

```csharp
var onTheDay = new ArxDateTimeConditionOption { Value = new DateTime(2026, 2, 1), ComparingType = ArxDateTimeConditionOptionType.DateOnly };
Console.WriteLine(await NumbersAsync(service, "modified_on", ArxConditionOperator.Equal, onTheDay));
```

Check it in the database:

```sql
SELECT account_number
FROM account
WHERE account_number LIKE 'ACC-R65%' AND CAST(modified_on AS date) = '2026-02-01'
ORDER BY account_number;
```

Expect: 1 row, ACC-R651 (the date in the stored offset: ARX writes a UTC DateTime as +00:00).

### Read a view

Real case 5 of RetrieveMultiple.

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.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Viewed Ventures";
account["account_number"] = "ACC-R661";
var accountId = await service.CreateAsync(account);
foreach (var first in new[] { "Vera", "Vik" })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "View-R661";
    contact["account_id"] = new ArxEntityReference("account", accountId);
    await service.CreateAsync(contact);
}
```

#### 1. The view as it is

vw_account_overview (an account and its number of contacts), filtered like a table.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("vw_account_overview");
query.ColumnSet.AddColumns("name", "contacts");
query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-R661");

var row = (await service.RetrieveMultipleAsync(query)).Entities[0];
Console.WriteLine($"{row["name"]}: {row["contacts"]} contacts, RecordId {(row.RecordId == null ? "none (the view isn't declared)" : row.RecordId)}");
```

Check it in the database:

```sql
SELECT name, contacts
FROM vw_account_overview
WHERE account_number = 'ACC-R661';
```

Expect: 1 row, Viewed Ventures, 2.

#### 2. The view declared with its id

Declared with its id (a view has no primary key, so the id is supplied, not generated): RecordId and Retrieve by id.

In scope: `connectionString` (string), `accountId` (ArxRecordId).

```csharp
var service = new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("vw_account_overview", ArxIdColumn.Supplied<long>("id"))
    .Build());

var row = await service.RetrieveAsync("vw_account_overview", accountId.AsInt64(), new ArxColumnSet("name", "contacts")) ?? throw new InvalidOperationException("The vw_account_overview record isn't there.");
Console.WriteLine($"Retrieve by id: {row["name"]}, RecordId {row.RecordId}");
```

Check it in the database:

```sql
SELECT o.name, o.contacts
FROM vw_account_overview o
JOIN account a ON a.id = o.id
WHERE a.account_number = 'ACC-R661';
```

Expect: 1 row, Viewed Ventures, 2.

### Read a function that returns a table

Real case 6 of RetrieveMultiple.

A table-valued function, read like a table, in three scenarios: its argument by position, sent as a typed parameter; filtered, ordered and paged on top; and a named argument, which SQL Server's functions don't take (ARX8001, before anything is sent). SQL Server functions can't change data, so a function read is only a read.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Function Foods";
account["account_number"] = "ACC-R671";
var accountId = await service.CreateAsync(account);
foreach (var first in new[] { "Fay", "Finn", "Flo", "Fred" })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Func-R671";
    contact["email"] = $"{first.ToLowerInvariant()}.r671@example.com";
    contact["account_id"] = new ArxEntityReference("account", accountId);
    await service.CreateAsync(contact);
}
```

#### 1. An argument by position

fn_contacts_of_account(@p0): the argument, by position, is a typed parameter.

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

```csharp
var query = new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", accountId.AsInt64()));
query.AddOrder("first_name", ArxOrderType.Ascending);

var rows = await service.RetrieveMultipleAsync(query);
Console.WriteLine(string.Join(", ", rows.Entities.Select(x => x["first_name"])));
```

Check it in the database:

```sql
SELECT f.first_name
FROM account a CROSS APPLY dbo.fn_contacts_of_account(a.id) f
WHERE a.account_number = 'ACC-R671'
ORDER BY f.first_name;
```

Expect: 4 rows, Fay, Finn, Flo, Fred.

#### 2. Filtered, ordered and paged

Criteria, order and paging on top of the function, as on a table.

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

```csharp
var query = new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", accountId.AsInt64()));
query.Criteria.AddCondition("first_name", ArxConditionOperator.BeginsWith, "F");
query.Criteria.AddCondition("first_name", ArxConditionOperator.NotEqual, "Flo");
query.AddOrder("first_name", ArxOrderType.Descending);
query.PageInfo = new ArxPagingInfo { Count = 2, ReturnTotalRecordCount = true };

var page = await service.RetrieveMultipleAsync(query);
Console.WriteLine($"{string.Join(", ", page.Entities.Select(x => x["first_name"]))} (page 1 of {page.TotalPageCount}, {page.TotalRecordCount} in all)");
```

Check it in the database:

```sql
SELECT f.first_name
FROM account a CROSS APPLY dbo.fn_contacts_of_account(a.id) f
WHERE a.account_number = 'ACC-R671' AND f.first_name <> 'Flo'
ORDER BY f.first_name DESC
OFFSET 0 ROWS FETCH NEXT 2 ROWS ONLY;
```

Expect: 2 rows, Fred, Finn (3 in all).

#### 3. A named argument is refused

The same function with its argument by name: SQL Server's functions take their arguments by position only, so ARX refuses it before anything is sent instead of guessing the order. Pass the arguments in the function's order.

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

```csharp
try
{
    await service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("fn_contacts_of_account", ("p_account_id", (object?)accountId.AsInt64()))));
}
catch (ArxNotSupportedException error) when (error.Code == ArxErrorCode.NotSupported)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT name, type_desc
FROM sys.objects
WHERE name = 'fn_contacts_of_account';
```

Expect: 1 row, SQL_INLINE_TABLE_VALUED_FUNCTION: the function is there; only the named call was refused.

### Lookups and display names

Real case 7 of RetrieveMultiple.

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).

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Named Nurseries";
account["account_number"] = "ACC-R681";
var accountId = await service.CreateAsync(account);
var withAccount = new ArxEntity("contact");
withAccount["first_name"] = "Nora";
withAccount["last_name"] = "Lookup-R681";
withAccount["account_id"] = new ArxEntityReference("account", accountId);
var noraId = await service.CreateAsync(withAccount);
var withoutAccount = new ArxEntity("contact");
withoutAccount["first_name"] = "Ned";
withoutAccount["last_name"] = "Lookup-R681";
await service.CreateAsync(withoutAccount);
```

#### 1. A contact's account, with its name

account_id reads back as an ArxEntityReference: the table, the id, and the account's name.

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

```csharp
var contact = await service.RetrieveAsync("contact", contactId, new ArxColumnSet("first_name", "account_id")) ?? throw new InvalidOperationException("The contact record isn't there.");
var account = (ArxEntityReference)contact["account_id"]!;
Console.WriteLine($"{contact["first_name"]} → {account.EntityName} {account.RecordId}: \"{account.Name}\"");
```

Check it in the database:

```sql
SELECT c.first_name, a.name
FROM contact c
JOIN account a ON a.id = c.account_id
WHERE c.first_name = 'Nora' AND c.last_name = 'Lookup-R681';
```

Expect: 1 row, Nora, Named Nurseries.

#### 2. A contact without an account

A contact without an account: the attribute is there, and null.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.ColumnSet.AddColumns("first_name", "account_id");
query.Criteria.AddCondition("first_name", ArxConditionOperator.Equal, "Ned");
query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Lookup-R681");

var ned = (await service.RetrieveMultipleAsync(query)).Entities[0];
Console.WriteLine($"{ned["first_name"]} → {(ned["account_id"] == null ? "no account" : "an account")}");
```

Check it in the database:

```sql
SELECT CASE WHEN account_id IS NULL THEN 1 ELSE 0 END AS no_account
FROM contact
WHERE first_name = 'Ned' AND last_name = 'Lookup-R681';
```

Expect: 1 row, 1.

#### 3. Names off: the id only, one join fewer

LookupNames = false: the references keep their id, without the join that reads the names.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.ColumnSet.AddColumns("first_name", "account_id");
query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Lookup-R681");
query.Criteria.AddCondition("account_id", ArxConditionOperator.NotNull);

var nora = (await service.RetrieveMultipleAsync(query, new ArxRetrieveOptions { LookupNames = false })).Entities[0];
var account = (ArxEntityReference)nora["account_id"]!;
Console.WriteLine($"{nora["first_name"]} → {account.EntityName} {account.RecordId}, name {(account.Name == null ? "not read" : account.Name)}");
```

Check it in the database:

```sql
SELECT CASE WHEN account_id IS NOT NULL THEN 1 ELSE 0 END AS has_account
FROM contact
WHERE first_name = 'Nora' AND last_name = 'Lookup-R681';
```

Expect: 1 row, 1.

### Turn read errors into messages

Real case 8 of RetrieveMultiple.

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).

Usings:

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

#### 1. What the database refuses

The database refuses: each error has a code to choose the message by.

In scope: `service` (ArxMsSqlDbService).

```csharp
var noTable = new ArxQueryExpression("accounts_typo");

var noColumn = new ArxQueryExpression("account");
noColumn.ColumnSet.AddColumn("fax_number");

var textForNumber = new ArxQueryExpression("account");
textForNumber.Criteria.AddCondition("employee_count", ArxConditionOperator.Equal, "forty");

foreach (var (what, query) in new[] { ("a table that doesn't exist", noTable), ("a column that doesn't exist", noColumn), ("text for a number", textForNumber) })
{
    try
    {
        await service.RetrieveMultipleAsync(query);
    }
    catch (ArxDatabaseException error)
    {
        Console.WriteLine($"{what}: {error.Code}, the user sees \"{ForTheUser(error)}\"");
    }
}
```

Defined in the sample, used above:

```csharp
private static string ForTheUser(ArxException error) => error.Code switch
{
    ArxErrorCode.UndefinedEntity => "That list doesn't exist.",
    ArxErrorCode.UndefinedAttribute => "One of the fields doesn't exist.",
    ArxErrorCode.DataTypeMismatch => "One of the filter values has the wrong type.",
    _ => "The search couldn't be run."
};
```

Check it in the database:

```sql
SELECT CASE WHEN OBJECT_ID('dbo.accounts_typo') IS NULL THEN 1 ELSE 0 END AS no_such_table,
       CASE WHEN COL_LENGTH('dbo.account', 'fax_number') IS NULL THEN 1 ELSE 0 END AS no_such_column;
```

Expect: 1, 1: nothing was created or changed by the failed reads.

#### 2. What ARX refuses before the database

ARX refuses before the database: these never reach it.

In scope: `service` (ArxMsSqlDbService).

```csharp
var between = new ArxQueryExpression("account");
between.Criteria.AddCondition("employee_count", ArxConditionOperator.Between, 10);

var removed = new ArxQueryExpression("account");
removed.ColumnSet.AddColumn("status._opt");

var attempts = new (string What, Func<Task> Call)[]
{
    ("a text id for a number id", () => service.RetrieveAsync("account", "ACC-R691")),
    ("Between with one value", () => service.RetrieveMultipleAsync(between)),
    ("a removed column form", () => service.RetrieveMultipleAsync(removed)),
};

foreach (var (what, call) in attempts)
{
    try
    {
        await call();
    }
    catch (ArxException error) when (error is ArxValidationException or ArxNotSupportedException)
    {
        Console.WriteLine($"{what}: {error.Code}");
    }
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number = 'ACC-R691';
```

Expect: 0: the refused reads were about records that don't exist, and changed nothing.

### Catch an SQL injection attempt on a search

Real case 9 of RetrieveMultiple.

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.

Usings:

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

Set up by the sample before its scenarios:

```csharp
foreach (var number in new[] { "ACC-R6A1", "ACC-R6A2" })
{
    var account = new ArxEntity("account");
    account["name"] = "Injection search " + number;
    account["account_number"] = number;
    await service.CreateAsync(account);
}
```

#### 1. A sort field from the request carries SQL

`?sort=name; DROP TABLE account; --` used as the order column.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumn("name");
query.AddOrder("name; DROP TABLE account; --", ArxOrderType.Ascending);

try
{
    await service.RetrieveMultipleAsync(query);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    Report(error);
}
```

Defined in the sample, used above:

```csharp
private static void Report(ArxValidationException error)
    => Console.WriteLine(error.Code == ArxErrorCode.SuspectedInjection ? $"SECURITY: {error.Message}" : $"bug, not an attack: {error.Message}");
```

Check it in the database:

```sql
SELECT CASE WHEN OBJECT_ID('dbo.account') IS NOT NULL THEN 1 ELSE 0 END AS account_exists;
```

Expect: 1.

#### 2. A filter field from the request carries SQL

`?field=name) OR (1=1` used as the condition's column.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.Criteria.AddCondition("name) OR (1=1", ArxConditionOperator.Equal, "x");

try
{
    await service.RetrieveMultipleAsync(query);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    Report(error);
}

// The fix: the fields a search may filter or sort by, from a fixed list.
var searchable = new HashSet<string> { "name", "account_number", "status" };
Console.WriteLine(searchable.Contains("name) OR (1=1") ? "would search" : "the allow-list refused the field");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number LIKE 'ACC-R6A%';
```

Expect: 2: the accounts are there; the refused search read none of them.

#### 3. SQL in the search value matches nothing

The classic in a value: concatenated it would return every account; as a parameter it is a name nobody has.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumn("name");
query.Criteria.AddCondition("name", ArxConditionOperator.Equal, "x' OR '1'='1");

var rows = await service.RetrieveMultipleAsync(query);
Console.WriteLine($"{rows.Entities.Count} accounts found");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name = 'x'' OR ''1''=''1';
```

Expect: 0.

#### 4. A badly formed name is ARX1003, not ARX1017

A badly formed field name ("account number", with a space) is ARX1003: a bug to fix, not an attack. A well-formed misspelling reaches the database and is ARX5002.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.AddOrder("account number", ArxOrderType.Ascending);

try
{
    await service.RetrieveMultipleAsync(query);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidIdentifier)
{
    Report(error);
}
```

Check it in the database:

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

Expect: 1: the column is account_number, with an underscore.

### Read in a batch scope

Advanced case 1 of RetrieveMultiple.

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.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Scoped Supplies";
account["account_number"] = "ACC-R721";
var accountId = await service.CreateAsync(account);
var product = new ArxEntity("product");
product["sku"] = "SKU-R721";
product["name"] = "Scoped widget";
product["unit_price"] = 10.00m;
var productId = await service.CreateAsync(product);
```

#### 1. Create, read back, update: one transaction

Create an order and its lines, read the lines back to total them, write the total, commit.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId), `productId` (ArxRecordId).

```csharp
await service.RunBatchAsync(async scope =>
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = "SO-R721";
    order["account_id"] = new ArxEntityReference("account", accountId);
    order["ordered_on"] = new DateTime(2026, 11, 8, 9, 0, 0, DateTimeKind.Utc);
    var orderId = await scope.CreateAsync(order);

    var lines = new List<ArxEntity>();

    foreach (var (quantity, price) in new[] { (2, 10.00m), (1, 25.50m), (3, 4.25m) })
    {
        var line = new ArxEntity("order_line");
        line["order_id"] = new ArxEntityReference("sales_order", orderId);
        line["product_id"] = new ArxEntityReference("product", productId);
        line["quantity"] = quantity;
        line["unit_price"] = price;
        lines.Add(line);
    }

    await scope.CreateMultipleAsync(lines);

    // The lines aren't committed yet: only a read in this scope sees them.
    var query = new ArxQueryExpression("order_line");
    query.ColumnSet.AddColumns("quantity", "unit_price");
    query.Criteria.AddCondition("order_id", ArxConditionOperator.Equal, orderId.AsInt64());
    var read = await scope.RetrieveMultipleAsync(query);
    var total = read.Entities.Sum(x => (int)x["quantity"]! * (decimal)x["unit_price"]!);

    var header = new ArxEntity("sales_order") { RecordId = orderId };
    header["total"] = total;
    await scope.UpdateAsync(header);

    Console.WriteLine($"{read.Entities.Count} lines read in the scope, total {total:N2} written");
});
```

Check it in the database:

```sql
SELECT total
FROM sales_order
WHERE order_number = 'SO-R721';
```

Expect: 1 row, 58.25.

#### 2. A failed read in the scope is undone alone

A read of a table that doesn't exist fails; its savepoint undoes it alone, and the scope commits the rest.

In scope: `service` (ArxMsSqlDbService).

```csharp
await using var scope = await service.BeginBatchAsync(new ArxBatchOptions { Savepoints = true });

var before = new ArxEntity("contact");
before["first_name"] = "Before";
before["last_name"] = "Scope-R722";
await scope.CreateAsync(before);

try
{
    await scope.RetrieveMultipleAsync(new ArxQueryExpression("no_such_table"));
}
catch (ArxDatabaseException error)
{
    Console.WriteLine($"call {error.BatchNumber} failed: {error.Code}; the scope is still {scope.State}");
}

var after = new ArxEntity("contact");
after["first_name"] = "After";
after["last_name"] = "Scope-R722";
await scope.CreateAsync(after);

await scope.CommitAsync();
```

Check it in the database:

```sql
SELECT first_name
FROM contact
WHERE last_name = 'Scope-R722'
ORDER BY first_name;
```

Expect: 2 rows, After, Before.

### Export a large table in pages

Advanced case 2 of RetrieveMultiple.

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).

Usings:

```csharp
using ARX.SDK.Query;
using ARX.SDK.Services;
using System.Diagnostics;
```

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, PRODUCTS).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"EXP-R731-{i:D5}";
    product["name"] = $"Exported product {i}";
    product["unit_price"] = 1m + i % 100;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. Every row, 500 at a time

Read page after page until HasNextPage is false; write each page out before reading the next.

In scope: `service` (ArxMsSqlDbService).

```csharp
var watch = Stopwatch.StartNew();
var exported = 0;
var pages = 0;
var total = 0m;
ArxEntityCollection page;

do
{
    page = await service.RetrieveMultipleAsync(Page(++pages, ordered: true));
    exported += page.Entities.Count;
    total += page.Entities.Sum(x => (decimal)x["unit_price"]!);
}
while (page.HasNextPage);

Console.WriteLine($"{exported} products in {pages} pages, prices summing to {total:N0} ({watch.ElapsedMilliseconds} ms on this machine; not a benchmark)");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Page(int number, bool ordered)
{
    var query = new ArxQueryExpression("product");
    query.ColumnSet.AddColumns("sku", "unit_price");
    query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, "EXP-R731-");

    if (ordered)
    {
        query.AddOrder("sku", ArxOrderType.Ascending);
    }

    query.PageInfo = new ArxPagingInfo { Count = 500, PageNumber = number };
    return query;
}
```

Check it in the database:

```sql
SELECT count(*), sum(unit_price)
FROM product
WHERE sku LIKE 'EXP-R731-%';
```

Expect: 2000, 101000.00: what the export read.

#### 2. Pages without an order: still no row twice

No order given: ARX adds the declared id as the order, so no row is read twice or missed.

In scope: `service` (ArxMsSqlDbService).

```csharp
var seen = new HashSet<string>();
var read = 0;

for (var number = 1; number <= PRODUCTS / 500; number++)
{
    foreach (var row in (await service.RetrieveMultipleAsync(Page(number, ordered: false))).Entities)
    {
        read++;
        seen.Add((string)row["sku"]!);
    }
}

Console.WriteLine($"{read} rows read, {seen.Count} different SKUs");
```

Check it in the database:

```sql
SELECT count(DISTINCT sku)
FROM product
WHERE sku LIKE 'EXP-R731-%';
```

Expect: 2000.

### A security check around a search endpoint

Advanced case 3 of RetrieveMultiple.

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.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var guard = new SecurityGuard(blockAfter: 3);
foreach (var number in new[] { "ACC-R741", "ACC-R742" })
{
    var account = new ArxEntity("account");
    account["name"] = "Guarded search " + number;
    account["account_number"] = number;
    await service.CreateAsync(account);
}
```

#### 1. A normal search passes through

A normal search goes through the guard untouched.

In scope: `service` (ArxMsSqlDbService), `guard` (SecurityGuard).

```csharp
var result = await SearchAsync(service, guard, "ana", "account_number");
Console.WriteLine($"ana: {result.Outcome}, {string.Join(", ", result.Value!.Entities.Select(x => x["account_number"]))}");
```

Defined in the sample, used above:

```csharp
private static Task<GuardResult<ArxEntityCollection>> SearchAsync(ArxMsSqlDbService service, SecurityGuard guard, string user, string sort)
{
    var query = new ArxQueryExpression("account");
    query.ColumnSet.AddColumn("account_number");
    query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R74");
    query.AddOrder(sort, ArxOrderType.Ascending);

    return guard.RunAsync(user, "search accounts", NameSource.UserInput, () => service.RetrieveMultipleAsync(query));
}
```

Check it in the database:

```sql
SELECT account_number
FROM account
WHERE account_number LIKE 'ACC-R74%'
ORDER BY account_number;
```

Expect: 2 rows, ACC-R741, ACC-R742.

#### 2. An attacker is logged, then blocked

Three sort fields carrying SQL: each refused by ARX (ARX1017) and logged; then the user is blocked.

In scope: `service` (ArxMsSqlDbService), `guard` (SecurityGuard).

```csharp
foreach (var probe in new[] { "name; DROP TABLE account; --", "(SELECT password FROM users)", "name UNION SELECT 1" })
{
    await SearchAsync(service, guard, "mallory", probe);
}

var valid = await SearchAsync(service, guard, "mallory", "name");
Console.WriteLine($"mallory: {guard.Events.Count(x => x.User == "mallory")} events, blocked {guard.IsBlocked("mallory")}; a valid search: {valid.Outcome}, \"{valid.ForTheUser}\"");
```

Check it in the database:

```sql
SELECT CASE WHEN OBJECT_ID('dbo.account') IS NOT NULL THEN 1 ELSE 0 END AS account_exists;
```

Expect: 1.

#### 3. The field list comes first

The well-written endpoint maps the request's field through its list: an unknown field is a 400, not a security event.

In scope: `service` (ArxMsSqlDbService), `guard` (SecurityGuard).

```csharp
var requested = "password";

if (SortFields.TryGetValue(requested, out var column))
{
    await SearchAsync(service, guard, "ben", column);
}
else
{
    Console.WriteLine($"400 Bad Request: can't sort by '{requested}'; security log unchanged ({guard.Events.Count} events)");
}
```

Defined in the sample, used above:

```csharp
private static readonly Dictionary<string, string> SortFields = new(StringComparer.OrdinalIgnoreCase)
{
    ["name"] = "name",
    ["number"] = "account_number",
};

private static Task<GuardResult<ArxEntityCollection>> SearchAsync(ArxMsSqlDbService service, SecurityGuard guard, string user, string sort)
{
    var query = new ArxQueryExpression("account");
    query.ColumnSet.AddColumn("account_number");
    query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R74");
    query.AddOrder(sort, ArxOrderType.Ascending);

    return guard.RunAsync(user, "search accounts", NameSource.UserInput, () => service.RetrieveMultipleAsync(query));
}
```

Check it in the database:

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

Expect: 0: there is no such column, and nothing asked the database.

#### 4. A false positive: a column label with parentheses

A false positive: a reporting tool sends the sort field as its column label "Number (legal)". The parentheses make ARX flag it (ARX1017), one strike, nobody blocked; triage reads the log, and the label is added to the list.

In scope: `service` (ArxMsSqlDbService), `guard` (SecurityGuard).

```csharp
var refused = await SearchAsync(service, guard, "reporting-tool", "Number (legal)");
Console.WriteLine($"reporting-tool: {refused.Outcome}; blocked {guard.IsBlocked("reporting-tool")}; triage reads: {guard.Events[^1].Detail}");

SortFields["Number (legal)"] = "account_number";
var fixedSearch = await SearchAsync(service, guard, "reporting-tool", SortFields["Number (legal)"]);
Console.WriteLine($"after the mapping: {fixedSearch.Outcome}, {fixedSearch.Value!.Entities.Count} accounts");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number LIKE 'ACC-R74%';
```

Expect: 2, what the mapped search read.

### Filter expressions in depth: nesting, recursion, links, And and Or

Advanced case 4 of RetrieveMultiple.

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.

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

Usings:

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

Set up by the sample before its scenarios:

```csharp
var accountIds = new Dictionary<string, ArxRecordId>();
foreach (var (number, name, industry, revenue, status) in new[]
{
    ("ACC-R751", "Filter Retail", Industry.Retail, 500_000m, "ACTIVE"),
    ("ACC-R752", "Filter Bank", Industry.Finance, 5_000_000m, "ACTIVE"),
    ("ACC-R753", "Filter Closed", Industry.Technology, 2_000_000m, "CLOSED"),
})
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    account["industry"] = new ArxSelectionSetValue("Industry", industry);
    account["annual_revenue"] = revenue;
    account["status"] = new ArxSelectionSetValue("AccountStatus", status);
    accountIds[number] = await service.CreateAsync(account);
}
var contacts = new List<ArxEntity>();
foreach (var (first, title, channel, doNotEmail, account) in new (string, string, char, bool, string?)[]
{
    ("Ana", "Sales Manager", 'E', false, "ACC-R751"),
    ("Ben", "Engineer", 'P', false, "ACC-R752"),
    ("Cleo", "Account Manager", 'S', true, "ACC-R752"),
    ("Dan", "Buyer", 'E', true, "ACC-R753"),
    ("Zoe", "Intern", 'P', false, null),
    ("Zack", "Store Manager", 'E', false, "ACC-R753"),
})
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = LAST_NAME;
    contact["job_title"] = title;
    contact["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", channel);
    contact["do_not_email"] = doNotEmail;
    contact["account_id"] = account == null ? null : new ArxEntityReference("account", accountIds[account]);
    contacts.Add(contact);
}
await service.CreateMultipleAsync(contacts);
```

#### 1. A tree four levels deep

last_name AND ( (title contains "manager" AND (channel = E OR do_not_email = false)) OR (first name begins with "Z" AND (title = "Intern" OR title contains "store")) ).

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();

var either = query.Criteria.AddFilter(ArxLogicalOperator.Or);               // level 2

var managers = either.AddFilter(ArxLogicalOperator.And);                     // level 3
managers.AddCondition("job_title", ArxConditionOperator.Contains, "manager");
var reachable = managers.AddFilter(ArxLogicalOperator.Or);                   // level 4
reachable.AddCondition("preferred_channel", ArxConditionOperator.Equal, "E");
reachable.AddCondition("do_not_email", ArxConditionOperator.Equal, false);

var zNames = either.AddFilter(ArxLogicalOperator.And);                       // level 3
zNames.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
var juniorOrStore = zNames.AddFilter(ArxLogicalOperator.Or);                 // level 4
juniorOrStore.AddCondition("job_title", ArxConditionOperator.Equal, "Intern");
juniorOrStore.AddCondition("job_title", ArxConditionOperator.Contains, "store");

Print(query.Criteria);
Console.WriteLine($"found: {await FirstNamesAsync(service, query)}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Contacts()
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("first_name", "job_title");
    query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, LAST_NAME);
    query.AddOrder("first_name", ArxOrderType.Ascending);
    return query;
}

private static async Task<string> FirstNamesAsync(ArxMsSqlDbService service, ArxQueryExpression query)
    => string.Join(", ", (await service.RetrieveMultipleAsync(query)).Entities.Select(x => x["first_name"]));

private static void Print(ArxFilterExpression filter, int depth = 0)
{
    var indent = new string(' ', depth * 4);
    Console.WriteLine($"{indent}{filter.FilterOperator.ToString().ToUpperInvariant()} (level {depth + 1})");

    foreach (var condition in filter.Conditions)
    {
        Console.WriteLine($"{indent}    {condition.AttributeName} {condition.Operator} {string.Join(", ", condition.Values.Select(v => v is ArxSelectionSetValue choice ? $"{choice.SetName} {choice.Value}" : v))}");
    }

    foreach (var child in filter.Filters)
    {
        Print(child, depth + 1);
    }
}
```

Check it in the database:

```sql
SELECT first_name
FROM contact
WHERE last_name = 'Filter-R751'
  AND ( (job_title LIKE '%manager%' AND (preferred_channel = 'E' OR do_not_email = 0))
     OR (first_name LIKE 'Z%' AND (job_title = 'Intern' OR job_title LIKE '%store%')) )
ORDER BY first_name;
```

Expect: 3 rows, Ana, Zack, Zoe (Cleo is a manager, but neither by e-mail nor willing to get one).

#### 2. A saved search, built recursively

A saved search five levels deep: (manager AND (e-mail OR (do-not-email AND (first name B… OR C…)))) OR first name is Dan. The recursive ToFilter turns any depth into filters.

In scope: `service` (ArxMsSqlDbService).

```csharp
var none = Array.Empty<Rule>();
var saved = new Rule(ArxLogicalOperator.Or,
    new[] { ("first_name", ArxConditionOperator.Equal, (object)"Dan") },
    new[]
    {
        new Rule(ArxLogicalOperator.And,
            new[] { ("job_title", ArxConditionOperator.Contains, (object)"manager") },
            new[]
            {
                new Rule(ArxLogicalOperator.Or,
                    new[] { ("preferred_channel", ArxConditionOperator.Equal, (object)"E") },
                    new[]
                    {
                        new Rule(ArxLogicalOperator.And,
                            new[] { ("do_not_email", ArxConditionOperator.Equal, (object)true) },
                            new[]
                            {
                                new Rule(ArxLogicalOperator.Or,
                                    new[] { ("first_name", ArxConditionOperator.BeginsWith, (object)"B"), ("first_name", ArxConditionOperator.BeginsWith, (object)"C") },
                                    none),
                            }),
                    }),
            }),
    });

var query = Contacts();
query.Criteria.AddFilter(ToFilter(saved));

Print(query.Criteria);
Console.WriteLine($"found: {await FirstNamesAsync(service, query)}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Contacts()
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("first_name", "job_title");
    query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, LAST_NAME);
    query.AddOrder("first_name", ArxOrderType.Ascending);
    return query;
}

private static async Task<string> FirstNamesAsync(ArxMsSqlDbService service, ArxQueryExpression query)
    => string.Join(", ", (await service.RetrieveMultipleAsync(query)).Entities.Select(x => x["first_name"]));

private static void Print(ArxFilterExpression filter, int depth = 0)
{
    var indent = new string(' ', depth * 4);
    Console.WriteLine($"{indent}{filter.FilterOperator.ToString().ToUpperInvariant()} (level {depth + 1})");

    foreach (var condition in filter.Conditions)
    {
        Console.WriteLine($"{indent}    {condition.AttributeName} {condition.Operator} {string.Join(", ", condition.Values.Select(v => v is ArxSelectionSetValue choice ? $"{choice.SetName} {choice.Value}" : v))}");
    }

    foreach (var child in filter.Filters)
    {
        Print(child, depth + 1);
    }
}

private sealed record Rule(ArxLogicalOperator Operator, (string Attribute, ArxConditionOperator Operator, object Value)[] Conditions, Rule[] Groups);

private static ArxFilterExpression ToFilter(Rule rule)
{
    var filter = new ArxFilterExpression(rule.Operator);

    foreach (var (attribute, op, value) in rule.Conditions)
    {
        filter.AddCondition(attribute, op, value);
    }

    foreach (var group in rule.Groups)
    {
        filter.AddFilter(ToFilter(group));
    }

    return filter;
}
```

Check it in the database:

```sql
SELECT first_name
FROM contact
WHERE last_name = 'Filter-R751'
  AND ( first_name = 'Dan'
     OR (job_title LIKE '%manager%'
         AND (preferred_channel = 'E'
              OR (do_not_email = 1 AND (first_name LIKE 'B%' OR first_name LIKE 'C%')))) )
ORDER BY first_name;
```

Expect: 4 rows, Ana, Cleo, Dan, Zack (Cleo through the deepest group).

#### 3. A link entity with its own filter

A link entity with its own filter tree: contacts of accounts that are Retail, OR (a million or more AND active). The link's criteria go in the join (`ON … AND (…)`); on an INNER join they also drop the contacts whose account doesn't match.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = Contacts();
var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Inner);
account.EntityAlias = "acc";
account.ColumnSet.AddColumn("name");

// The link's filter is an ArxFilterExpression too: its conditions refer to the account's columns.
account.LinkCriteria = new ArxFilterExpression(ArxLogicalOperator.Or);
account.LinkCriteria.AddCondition("industry", ArxConditionOperator.Equal, new ArxSelectionSetValue("Industry", Industry.Retail));
var bigAndActive = account.LinkCriteria.AddFilter(ArxLogicalOperator.And);
bigAndActive.AddCondition("annual_revenue", ArxConditionOperator.GreaterEqual, 1_000_000m);
bigAndActive.AddCondition("status", ArxConditionOperator.Equal, "ACTIVE");

Print(account.LinkCriteria);
var rows = await service.RetrieveMultipleAsync(query);
Console.WriteLine($"found: {string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]} ({x["acc.name"]})"))}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Contacts()
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("first_name", "job_title");
    query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, LAST_NAME);
    query.AddOrder("first_name", ArxOrderType.Ascending);
    return query;
}

private static void Print(ArxFilterExpression filter, int depth = 0)
{
    var indent = new string(' ', depth * 4);
    Console.WriteLine($"{indent}{filter.FilterOperator.ToString().ToUpperInvariant()} (level {depth + 1})");

    foreach (var condition in filter.Conditions)
    {
        Console.WriteLine($"{indent}    {condition.AttributeName} {condition.Operator} {string.Join(", ", condition.Values.Select(v => v is ArxSelectionSetValue choice ? $"{choice.SetName} {choice.Value}" : v))}");
    }

    foreach (var child in filter.Filters)
    {
        Print(child, depth + 1);
    }
}
```

Check it in the database:

```sql
SELECT c.first_name, a.name
FROM contact c
JOIN account a ON a.id = c.account_id
 AND (a.industry = 1 OR (a.annual_revenue >= 1000000 AND a.status = 'ACTIVE'))
WHERE c.last_name = 'Filter-R751'
ORDER BY c.first_name;
```

Expect: 3 rows, Ana (Filter Retail), Ben (Filter Bank), Cleo (Filter Bank).

#### 4. Two filters joined: under And, then under Or

Two filters built apart and joined: the user's search (first name A… OR Z…) and a security rule (the account isn't closed, OR there is no account). The rule refers to the joined account as `acc.status`. Joined under the criteria (And): both must hold. Joined in an Or group: either is enough.

In scope: `service` (ArxMsSqlDbService).

```csharp
ArxFilterExpression UserSearch()
{
    var search = new ArxFilterExpression(ArxLogicalOperator.Or);
    search.AddCondition("first_name", ArxConditionOperator.BeginsWith, "A");
    search.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
    return search;
}

ArxFilterExpression SecurityRule()
{
    // NotEqual skips NULL (SQL's rule), so "no account" is asked for on its own.
    var rule = new ArxFilterExpression(ArxLogicalOperator.Or);
    rule.AddCondition("acc.status", ArxConditionOperator.NotEqual, "CLOSED");
    rule.AddCondition("acc.id", ArxConditionOperator.Null);
    return rule;
}

ArxQueryExpression WithAccount()
{
    var query = Contacts();
    query.AddLink("account", "account_id", "id", ArxJoinOperator.Left).EntityAlias = "acc";
    return query;
}

var both = WithAccount();
both.Criteria.AddFilter(UserSearch());
both.Criteria.AddFilter(SecurityRule());
Console.WriteLine($"search AND rule: {await FirstNamesAsync(service, both)}");

var either = WithAccount();
var orGroup = either.Criteria.AddFilter(ArxLogicalOperator.Or);
orGroup.AddFilter(UserSearch());
orGroup.AddFilter(SecurityRule());
Console.WriteLine($"search OR rule:  {await FirstNamesAsync(service, either)}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Contacts()
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("first_name", "job_title");
    query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, LAST_NAME);
    query.AddOrder("first_name", ArxOrderType.Ascending);
    return query;
}

private static async Task<string> FirstNamesAsync(ArxMsSqlDbService service, ArxQueryExpression query)
    => string.Join(", ", (await service.RetrieveMultipleAsync(query)).Entities.Select(x => x["first_name"]));
```

Check it in the database:

```sql
SELECT c.first_name,
       CASE WHEN c.first_name LIKE 'A%' OR c.first_name LIKE 'Z%' THEN 1 ELSE 0 END AS search,
       CASE WHEN a.status <> 'CLOSED' OR a.id IS NULL THEN 1 ELSE 0 END AS [rule]
FROM contact c
LEFT JOIN account a ON a.id = c.account_id
WHERE c.last_name = 'Filter-R751'
ORDER BY c.first_name;
```

Expect: 6 rows; search and rule both 1 for Ana, Zoe; either 1 for Ana, Ben, Cleo, Zack, Zoe (Dan: neither).

#### 5. And and Or: the grouping is the meaning

The same three conditions, two groupings: (e-mail AND willing) OR Z…, against e-mail AND (willing OR Z…).

In scope: `service` (ArxMsSqlDbService).

```csharp
var first = Contacts();
var any = first.Criteria.AddFilter(ArxLogicalOperator.Or);
var emailAndWilling = any.AddFilter(ArxLogicalOperator.And);
emailAndWilling.AddCondition("preferred_channel", ArxConditionOperator.Equal, "E");
emailAndWilling.AddCondition("do_not_email", ArxConditionOperator.Equal, false);
any.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
Console.WriteLine($"(e-mail AND willing) OR Z…: {await FirstNamesAsync(service, first)}");

var second = Contacts();
second.Criteria.AddCondition("preferred_channel", ArxConditionOperator.Equal, "E");
var willingOrZ = second.Criteria.AddFilter(ArxLogicalOperator.Or);
willingOrZ.AddCondition("do_not_email", ArxConditionOperator.Equal, false);
willingOrZ.AddCondition("first_name", ArxConditionOperator.BeginsWith, "Z");
Console.WriteLine($"e-mail AND (willing OR Z…): {await FirstNamesAsync(service, second)}");
```

Check it in the database:

```sql
SELECT first_name,
       CASE WHEN (preferred_channel = 'E' AND do_not_email = 0) OR first_name LIKE 'Z%' THEN 1 ELSE 0 END AS first_grouping,
       CASE WHEN preferred_channel = 'E' AND (do_not_email = 0 OR first_name LIKE 'Z%') THEN 1 ELSE 0 END AS second_grouping
FROM contact
WHERE last_name = 'Filter-R751'
ORDER BY first_name;
```

Expect: 6 rows; first_grouping 1 for Ana, Zack, Zoe; second_grouping 1 for Ana, Zack (Zoe prefers the phone).

### Link entities in depth: chains, branches, criteria per level, recursion

Advanced case 5 of RetrieveMultiple.

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.

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

Usings:

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

Set up by the sample before its scenarios:

```csharp
ArxRecordId? parent = null;
var accounts = new Dictionary<string, ArxRecordId>();
foreach (var (number, name) in new[] { ("ACC-R761", "Holding R76"), ("ACC-R762", "Group R76"), ("ACC-R763", "Shop R76") })
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    account["parent_account_id"] = parent == null ? null : new ArxEntityReference("account", parent);
    parent = await service.CreateAsync(account);
    accounts[name] = parent;
}
var solo = new ArxEntity("account");
solo["name"] = "Solo R76";
solo["account_number"] = "ACC-R764";
accounts["Solo R76"] = await service.CreateAsync(solo);
foreach (var (first, account) in new[] { ("Sam", "Shop R76"), ("Sue", "Shop R76"), ("Sol", "Solo R76") })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Links-R76";
    contact["account_id"] = new ArxEntityReference("account", accounts[account]);
    await service.CreateAsync(contact);
}
var categories = new Dictionary<string, ArxRecordId>();
foreach (var name in new[] { "Bikes R76", "Helmets R76" })
{
    var category = new ArxEntity("product_category");
    category["name"] = name;
    categories[name] = await service.CreateAsync(category);
}
var products = new Dictionary<string, ArxRecordId>();
foreach (var (sku, name, category, price) in new[] { ("SKU-R761", "Road bike", "Bikes R76", 900m), ("SKU-R762", "Kids helmet", "Helmets R76", 35m) })
{
    var product = new ArxEntity("product");
    product["sku"] = sku;
    product["name"] = name;
    product["category_id"] = new ArxEntityReference("product_category", categories[category]);
    product["unit_price"] = price;
    products[sku] = await service.CreateAsync(product);
}
foreach (var (orderNumber, account, lines) in new[]
{
    ("SO-R761", "Shop R76", new[] { ("SKU-R761", 2), ("SKU-R762", 1) }),
    ("SO-R762", "Solo R76", new[] { ("SKU-R762", 3) }),
})
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = orderNumber;
    order["account_id"] = new ArxEntityReference("account", accounts[account]);
    order["ordered_on"] = new DateTime(2026, 11, 8, 10, 0, 0, DateTimeKind.Utc);
    var orderId = await service.CreateAsync(order);

    foreach (var (sku, quantity) in lines)
    {
        var line = new ArxEntity("order_line");
        line["order_id"] = new ArxEntityReference("sales_order", orderId);
        line["product_id"] = new ArxEntityReference("product", products[sku]);
        line["quantity"] = quantity;
        line["unit_price"] = sku == "SKU-R761" ? 900m : 35m;
        await service.CreateAsync(line);
    }
}
```

#### 1. A chain four joins deep, and a second branch

Every line with its order, account, parent and grandparent, and its product and category.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = OrderLines(out _, out _, out _, out _);
Print(query.LinkEntities);

foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine(Line(row));
}
```

Defined in the sample, used above:

```csharp
private static ArxLinkEntity Link(string table, string from, string to, ArxJoinOperator join, string alias, params string[] columns)
{
    var link = new ArxLinkEntity(table, from, to, join) { EntityAlias = alias };
    link.ColumnSet.AddColumns(columns);
    return link;
}

private static ArxQueryExpression OrderLines(out ArxLinkEntity order, out ArxLinkEntity account, out ArxLinkEntity parent, out ArxLinkEntity category)
{
    var query = new ArxQueryExpression("order_line");
    query.ColumnSet.AddColumn("quantity");

    // Branch 1: order → account → parent → grandparent (each link nested in the one before).
    order = Link("sales_order", "order_id", "id", ArxJoinOperator.Inner, "ord", "order_number");
    order.LinkCriteria.AddCondition("order_number", ArxConditionOperator.BeginsWith, "SO-R76");
    account = Link("account", "account_id", "id", ArxJoinOperator.Inner, "acc", "name");
    parent = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, "parent", "name");
    var grandparent = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, "grand", "name");
    parent.LinkEntities.Add(grandparent);
    account.LinkEntities.Add(parent);
    order.LinkEntities.Add(account);
    query.LinkEntities.Add(order);

    // Branch 2: product → category.
    var product = Link("product", "product_id", "id", ArxJoinOperator.Inner, "prod", "sku", "name");
    category = Link("product_category", "category_id", "id", ArxJoinOperator.Inner, "cat", "name");
    product.LinkEntities.Add(category);
    query.LinkEntities.Add(product);

    query.AddOrder("ord", "order_number", ArxOrderType.Ascending);
    query.AddOrder("prod", "sku", ArxOrderType.Ascending);
    return query;
}

private static void Print(IEnumerable<ArxLinkEntity> links, string parentAlias = "main", int depth = 1)
{
    foreach (var link in links)
    {
        Console.WriteLine($"{new string(' ', depth * 4)}{link.JoinOperator} JOIN {link.LinkToEntityName} AS {link.EntityAlias} ON {link.EntityAlias}.{link.LinkToAttributeName} = {parentAlias}.{link.LinkFromAttributeName}"
            + (link.LinkCriteria.Conditions.Count + link.LinkCriteria.Filters.Count > 0 ? "  (+ its own criteria)" : string.Empty));
        Print(link.LinkEntities, link.EntityAlias, depth + 1);
    }
}

private static string Line(ArxEntity x)
    => $"{x["ord.order_number"]} {x["prod.sku"]} x{x["quantity"]} ({x["cat.name"]}) for {x["acc.name"]} < {x["parent.name"] ?? "-"} < {x["grand.name"] ?? "-"}";
```

Check it in the database:

```sql
SELECT o.order_number, p.sku, l.quantity, c.name, a.name, pa.name, ga.name
FROM order_line l
JOIN sales_order o ON o.id = l.order_id AND o.order_number LIKE 'SO-R76%'
JOIN account a ON a.id = o.account_id
LEFT JOIN account pa ON pa.id = a.parent_account_id
LEFT JOIN account ga ON ga.id = pa.parent_account_id
JOIN product p ON p.id = l.product_id
JOIN product_category c ON c.id = p.category_id
ORDER BY o.order_number, p.sku;
```

Expect: 3 rows: SO-R761 SKU-R761 2 Bikes R76 Shop R76 Group R76 Holding R76; SO-R761 SKU-R762 1 Helmets R76 Shop R76 Group R76 Holding R76; SO-R762 SKU-R762 3 Helmets R76 Solo R76 NULL NULL.

#### 2. Each level with its own criteria: INNER filters, LEFT keeps

Criteria at two depths: the category (an INNER join, so it filters the lines) must be helmets; the parent (a LEFT join, so it filters nothing) must begin with "Group", or it reads as empty.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = OrderLines(out _, out _, out var parent, out var category);
category.LinkCriteria.AddCondition("name", ArxConditionOperator.Equal, "Helmets R76");
parent.LinkCriteria.AddCondition("name", ArxConditionOperator.BeginsWith, "Group");

foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine(Line(row));
}
```

Defined in the sample, used above:

```csharp
private static ArxLinkEntity Link(string table, string from, string to, ArxJoinOperator join, string alias, params string[] columns)
{
    var link = new ArxLinkEntity(table, from, to, join) { EntityAlias = alias };
    link.ColumnSet.AddColumns(columns);
    return link;
}

private static ArxQueryExpression OrderLines(out ArxLinkEntity order, out ArxLinkEntity account, out ArxLinkEntity parent, out ArxLinkEntity category)
{
    var query = new ArxQueryExpression("order_line");
    query.ColumnSet.AddColumn("quantity");

    // Branch 1: order → account → parent → grandparent (each link nested in the one before).
    order = Link("sales_order", "order_id", "id", ArxJoinOperator.Inner, "ord", "order_number");
    order.LinkCriteria.AddCondition("order_number", ArxConditionOperator.BeginsWith, "SO-R76");
    account = Link("account", "account_id", "id", ArxJoinOperator.Inner, "acc", "name");
    parent = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, "parent", "name");
    var grandparent = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, "grand", "name");
    parent.LinkEntities.Add(grandparent);
    account.LinkEntities.Add(parent);
    order.LinkEntities.Add(account);
    query.LinkEntities.Add(order);

    // Branch 2: product → category.
    var product = Link("product", "product_id", "id", ArxJoinOperator.Inner, "prod", "sku", "name");
    category = Link("product_category", "category_id", "id", ArxJoinOperator.Inner, "cat", "name");
    product.LinkEntities.Add(category);
    query.LinkEntities.Add(product);

    query.AddOrder("ord", "order_number", ArxOrderType.Ascending);
    query.AddOrder("prod", "sku", ArxOrderType.Ascending);
    return query;
}

private static string Line(ArxEntity x)
    => $"{x["ord.order_number"]} {x["prod.sku"]} x{x["quantity"]} ({x["cat.name"]}) for {x["acc.name"]} < {x["parent.name"] ?? "-"} < {x["grand.name"] ?? "-"}";
```

Check it in the database:

```sql
SELECT o.order_number, p.sku, l.quantity, a.name, pa.name
FROM order_line l
JOIN sales_order o ON o.id = l.order_id AND o.order_number LIKE 'SO-R76%'
JOIN account a ON a.id = o.account_id
LEFT JOIN account pa ON pa.id = a.parent_account_id AND pa.name LIKE 'Group%'
JOIN product p ON p.id = l.product_id
JOIN product_category c ON c.id = p.category_id AND c.name = 'Helmets R76'
ORDER BY o.order_number;
```

Expect: 2 rows: SO-R761 SKU-R762 1 Shop R76 Group R76; SO-R762 SKU-R762 3 Solo R76 NULL.

#### 3. Conditions and order on aliases deep in the tree

The main criteria and order can name any alias in the tree: the lines of accounts whose grandparent is "Holding R76", the biggest quantity first. A condition on a LEFT-joined alias in the main criteria drops the rows where it is NULL (WHERE runs after the joins); in the link's own criteria it wouldn't.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = OrderLines(out _, out _, out _, out _);
query.Criteria.AddCondition("grand.name", ArxConditionOperator.Equal, "Holding R76");
query.Orders.Clear();
query.AddOrder("quantity", ArxOrderType.Descending);

foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine(Line(row));
}
```

Check it in the database:

```sql
SELECT p.sku, l.quantity
FROM order_line l
JOIN sales_order o ON o.id = l.order_id
JOIN account a ON a.id = o.account_id
LEFT JOIN account pa ON pa.id = a.parent_account_id
LEFT JOIN account ga ON ga.id = pa.parent_account_id
JOIN product p ON p.id = l.product_id
WHERE o.order_number LIKE 'SO-R76%' AND ga.name = 'Holding R76'
ORDER BY l.quantity DESC;
```

Expect: 2 rows: SKU-R761 2, SKU-R762 1 (Solo has no grandparent).

#### 4. Ancestors, built by a recursive function

A self-join chain built recursively: Shop's parent, grandparent and great-grandparent (there is none).

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumn("name");
query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-R763");
query.LinkEntities.Add(Ancestors(1, 3));
Print(query.LinkEntities);

var shop = (await service.RetrieveMultipleAsync(query)).Entities[0];
Console.WriteLine($"{shop["name"]} < {shop["up1.name"]} < {shop["up2.name"]} < {shop["up3.name"] ?? "(none)"}");
```

Defined in the sample, used above:

```csharp
private static ArxLinkEntity Link(string table, string from, string to, ArxJoinOperator join, string alias, params string[] columns)
{
    var link = new ArxLinkEntity(table, from, to, join) { EntityAlias = alias };
    link.ColumnSet.AddColumns(columns);
    return link;
}

private static void Print(IEnumerable<ArxLinkEntity> links, string parentAlias = "main", int depth = 1)
{
    foreach (var link in links)
    {
        Console.WriteLine($"{new string(' ', depth * 4)}{link.JoinOperator} JOIN {link.LinkToEntityName} AS {link.EntityAlias} ON {link.EntityAlias}.{link.LinkToAttributeName} = {parentAlias}.{link.LinkFromAttributeName}"
            + (link.LinkCriteria.Conditions.Count + link.LinkCriteria.Filters.Count > 0 ? "  (+ its own criteria)" : string.Empty));
        Print(link.LinkEntities, link.EntityAlias, depth + 1);
    }
}

private static ArxLinkEntity Ancestors(int level, int levels)
{
    var link = Link("account", "parent_account_id", "id", ArxJoinOperator.Left, $"up{level}", "name");

    if (level < levels)
    {
        link.LinkEntities.Add(Ancestors(level + 1, levels));
    }

    return link;
}
```

Check it in the database:

```sql
SELECT a.name, u1.name, u2.name, u3.name
FROM account a
LEFT JOIN account u1 ON u1.id = a.parent_account_id
LEFT JOIN account u2 ON u2.id = u1.parent_account_id
LEFT JOIN account u3 ON u3.id = u2.parent_account_id
WHERE a.account_number = 'ACC-R763';
```

Expect: 1 row: Shop R76, Group R76, Holding R76, NULL.

#### 5. A one-to-many link, aggregated per account

A link to the many side (account → its contacts) gives one row per contact. Aggregated: contacts per account, counting the linked ids (accounts without contacts count 0, kept by the LEFT join).

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("account") { AggregateInfo = new ArxAggregateInfo() };
query.ColumnSet.AddColumn("name");
query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R76");
query.LinkEntities.Add(Link("contact", "id", "account_id", ArxJoinOperator.Left, "c"));
query.AggregateInfo.AddAggregateColumn("contacts", "c.id", ArxAggregateOperator.Count);
query.AggregateInfo.AddGroupByColumn("name");
query.AddOrder("contacts", ArxOrderType.Descending);
query.AddOrder("name", ArxOrderType.Ascending);

foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine($"{row["name"],-12} {row["contacts"]} contacts");
}
```

Defined in the sample, used above:

```csharp
private static ArxLinkEntity Link(string table, string from, string to, ArxJoinOperator join, string alias, params string[] columns)
{
    var link = new ArxLinkEntity(table, from, to, join) { EntityAlias = alias };
    link.ColumnSet.AddColumns(columns);
    return link;
}
```

Check it in the database:

```sql
SELECT a.name, count(c.id) AS contacts
FROM account a
LEFT JOIN contact c ON c.account_id = a.id
WHERE a.account_number LIKE 'ACC-R76%'
GROUP BY a.name
ORDER BY contacts DESC, a.name;
```

Expect: 4 rows: Shop R76 2, Solo R76 1, Group R76 0, Holding R76 0.

### Paging in depth: the total is opt-in, ask once, load more, Top, seek paging

Advanced case 6 of RetrieveMultiple.

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).

- The same page with and without the total: what each gives back.
- What the total costs, timed on this machine (not a benchmark; `RetrievePagingBenchmarks` measures it).
- "Page 1 of 40": ask for the total on the first page only, then page without it.
- "Load more": never ask for the total; HasNextPage is enough.
- One page only: Top (no extra row, no offset); Top together with PageInfo is ARX1001.
- 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. SQL Server 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 `UPDATE STATISTICS product`.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
using System.Diagnostics;
```

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, PRODUCTS).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"{PREFIX}{i:D5}";
    product["name"] = $"Paged product {i}";
    product["unit_price"] = 1m + i % 50;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. The same page with and without the total

Page 2, twice. With the total: 20000 rows in 40 pages. Without it: TotalRecordCount and TotalPageCount stay 0 (not counted, not "no rows"), and HasNextPage is still right.

In scope: `service` (ArxMsSqlDbService).

```csharp
Console.WriteLine("with:    " + Describe(await service.RetrieveMultipleAsync(Page(2, total: true))));
Console.WriteLine("without: " + Describe(await service.RetrieveMultipleAsync(Page(2, total: false))));
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Page(int pageNumber, bool total, int pageSize = PAGE_SIZE)
{
    var query = new ArxQueryExpression("product");
    query.ColumnSet.AddColumns("sku", "unit_price");
    query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, PREFIX);
    query.AddOrder("sku", ArxOrderType.Ascending);
    query.PageInfo = new ArxPagingInfo { Count = pageSize, PageNumber = pageNumber, ReturnTotalRecordCount = total };
    return query;
}

private static string Describe(ArxEntityCollection page)
    => $"{page.Entities.Count} rows ({page.Entities[0]["sku"]} to {page.Entities[^1]["sku"]}), page {page.CurrentPage}, "
       + $"total {page.TotalRecordCount}, pages {page.TotalPageCount}, next page: {page.HasNextPage}";
```

Check it in the database:

```sql
SELECT min(sku), max(sku), count(*)
FROM (SELECT sku FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 500 ROWS FETCH NEXT 500 ROWS ONLY) AS page;
```

Expect: PGN-R77-00501, PGN-R77-01000, 500: page 2 both times. SELECT count(*) FROM product WHERE sku LIKE 'PGN-R77-%'; 20000, the total the first read reported (the statement ARX adds when asked).

#### 2. What the total costs (timed on this machine; not a benchmark)

The total's cost: the page reads 501 rows, the count reads all 20000 that match. Timed here over 20 reads of page 1 each way; the numbers are this machine's and only show the direction (the benchmark is `RetrievePagingBenchmarks`, categories Page and PageAndTotal).

In scope: `service` (ArxMsSqlDbService).

```csharp
const int READS = 20;

async Task<double> TimeAsync(bool total)
{
    await service.RetrieveMultipleAsync(Page(1, total)); // warm up
    var watch = Stopwatch.StartNew();

    for (var i = 0; i < READS; i++)
    {
        await service.RetrieveMultipleAsync(Page(1, total));
    }

    return watch.Elapsed.TotalMilliseconds / READS;
}

var without = await TimeAsync(total: false);
var with = await TimeAsync(total: true);
Console.WriteLine($"page 1 without the total: {without:N2} ms a read; with it: {with:N2} ms a read");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Page(int pageNumber, bool total, int pageSize = PAGE_SIZE)
{
    var query = new ArxQueryExpression("product");
    query.ColumnSet.AddColumns("sku", "unit_price");
    query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, PREFIX);
    query.AddOrder("sku", ArxOrderType.Ascending);
    query.PageInfo = new ArxPagingInfo { Count = pageSize, PageNumber = pageNumber, ReturnTotalRecordCount = total };
    return query;
}
```

Check it in the database:

```sql
UPDATE STATISTICS product;
SET STATISTICS IO ON;
SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 0 ROWS FETCH NEXT 501 ROWS ONLY;
SELECT count(*) FROM (SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%') AS arx_count;
-- Expect (Messages tab, with the actual execution plan on): the page is a seek on product_sku_key that stops
-- after 501 rows, a few logical reads; the count reads all 20000 rows.
```

#### 3. "Page 1 of 40": the total on the first page only

A grid that shows "page 1 of 40": the total is asked for on page 1 and kept; pages 2 to 40 don't ask again (the count would be the same 20000 rows read 39 more times). Rows added meanwhile show up on later pages, and the page count shown is the one from page 1: refresh it when the user goes back to page 1.

In scope: `service` (ArxMsSqlDbService).

```csharp
var first = await service.RetrieveMultipleAsync(Page(1, total: true));
var pageCount = first.TotalPageCount;
var read = first.Entities.Count;
var pageNumber = 1;
var page = first;

while (page.HasNextPage)
{
    page = await service.RetrieveMultipleAsync(Page(++pageNumber, total: false));
    read += page.Entities.Count;
}

Console.WriteLine($"page 1 of {pageCount} ({first.TotalRecordCount} products); read {read} rows in {pageNumber} pages, counted once");
```

Check it in the database:

```sql
SELECT count(*), CEILING(count(*) / 500.0)
FROM product
WHERE sku LIKE 'PGN-R77-%';
```

Expect: 20000, 40: the total and the page count page 1 reported, and what the loop read.

#### 4. "Load more": no total at all

A "load more" list or an infinite scroll shows no page count, so it never asks for one: each page is one statement, and HasNextPage decides whether to show the button. Here: the first three loads.

In scope: `service` (ArxMsSqlDbService).

```csharp
for (var load = 1; load <= 3; load++)
{
    var page = await service.RetrieveMultipleAsync(Page(load, total: false, pageSize: 50));
    Console.WriteLine($"load {load}: {page.Entities[0]["sku"]} to {page.Entities[^1]["sku"]}; show \"Load more\": {page.HasNextPage}");
}
```

Check it in the database:

```sql
SELECT min(sku), max(sku)
FROM (SELECT sku FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 0 ROWS FETCH NEXT 150 ROWS ONLY) AS loaded;
```

Expect: PGN-R77-00001, PGN-R77-00150: the three loads of 50.

#### 5. One page only: Top

"The five cheapest" is one page and never a next one: Top reads exactly five rows (PageInfo would read six, to know about a next page). Top and PageInfo together are ARX1001: one says how many rows, the other which page.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("product") { Top = 5 };
query.ColumnSet.AddColumns("sku", "unit_price");
query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, PREFIX);
query.AddOrder("unit_price", ArxOrderType.Ascending);
query.AddOrder("sku", ArxOrderType.Ascending);

var cheapest = await service.RetrieveMultipleAsync(query);
Console.WriteLine(string.Join(", ", cheapest.Entities.Select(x => $"{x["sku"]} {x["unit_price"]:N2}")));

try
{
    query.PageInfo = new ArxPagingInfo { Count = 5 };
    await service.RetrieveMultipleAsync(query);
}
catch (ArxException error) when (error.Code == ArxErrorCode.InvalidArgument)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT sku, unit_price
FROM product
WHERE sku LIKE 'PGN-R77-%'
ORDER BY unit_price, sku
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
```

Expect: PGN-R77-00050, PGN-R77-00100, PGN-R77-00150, PGN-R77-00200, PGN-R77-00250, all 1.00.

#### 6. Deep pages: seek past the last row instead of OFFSET

OFFSET makes the database read and throw away every row before the page: page 40 reads 20000 rows to return 500. Seek paging remembers the last SKU read and asks for the rows after it, always as page 1, so each page reads only its own rows through the index on the order column (here the unique index on sku). The order must be unique (add the id after a column with repeats, and seek on both), and the page can't be jumped to by number: it suits exports and "load more", not "go to page 37". Rows added meanwhile aren't read twice or skipped.

In scope: `service` (ArxMsSqlDbService).

```csharp
var byOffset = await service.RetrieveMultipleAsync(Page(40, total: false));

string? last = null;
ArxEntityCollection page;
var pages = 0;

do
{
    var query = Page(1, total: false);

    if (last != null)
    {
        query.Criteria.AddCondition("sku", ArxConditionOperator.GreaterThan, last);
    }

    page = await service.RetrieveMultipleAsync(query);
    last = (string)page.Entities[^1]["sku"]!;
    pages++;
}
while (page.HasNextPage);

Console.WriteLine($"page 40 by OFFSET: {byOffset.Entities[0]["sku"]} to {byOffset.Entities[^1]["sku"]}");
Console.WriteLine($"page {pages} by seek:   {page.Entities[0]["sku"]} to {page.Entities[^1]["sku"]}, the same rows");
```

Check it in the database:

```sql
UPDATE STATISTICS product;
SET STATISTICS IO ON;
SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%' ORDER BY sku OFFSET 19500 ROWS FETCH NEXT 501 ROWS ONLY;
SELECT sku, unit_price FROM product WHERE sku LIKE 'PGN-R77-%' AND sku > 'PGN-R77-19500' ORDER BY sku OFFSET 0 ROWS FETCH NEXT 501 ROWS ONLY;
```

Expect: both return PGN-R77-19501 to PGN-R77-20000 (500 rows); the OFFSET plan reads the index through all 20000 rows (more logical reads), the seek plan starts at sku > 'PGN-R77-19500' and reads 500.

### Read other row sources: a view in a schema, a summary view, a table with no id

Advanced case 7 of RetrieveMultiple.

RetrieveMultiple reads more than tables: whatever name the query is given, SQL Server 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`):

- A view in another schema (`reporting.vw_product_catalogue`), linked to a table and aggregated.
- A summary view: it runs its query at each read, so a new order shows at once.
- A table with no id (`web_visit`), read with a date range and a link.
SQL Server has no materialized views (an indexed view is the nearest, and reads the same way) and no inheritance; a partitioned table is read like any table: partitions are invisible to statements.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var accounts = new Dictionary<string, ArxRecordId>();
foreach (var (number, name) in new[] { ("ACC-R781", "Summit Supplies"), ("ACC-R782", "Harbor Hardware") })
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    accounts[number] = await service.CreateAsync(account);
}
var category = new ArxEntity("product_category");
category["name"] = "Tools R78";
var categoryId = await service.CreateAsync(category);
var products = new Dictionary<string, ArxRecordId>();
foreach (var (sku, name, price, active) in new[] { ("SKU-R781", "Hammer", 25m, true), ("SKU-R782", "Saw", 40m, true), ("SKU-R783", "Old drill", 90m, false) })
{
    var product = new ArxEntity("product");
    product["sku"] = sku;
    product["name"] = name;
    product["category_id"] = new ArxEntityReference("product_category", categoryId);
    product["unit_price"] = price;
    product["is_active"] = active;
    products[sku] = await service.CreateAsync(product);
}
foreach (var (orderNumber, account, total, lines) in new[]
{
    ("SO-R781", "ACC-R781", 180m, new[] { ("SKU-R781", 4, 25m), ("SKU-R782", 2, 40m) }),
    ("SO-R782", "ACC-R781", 200m, Array.Empty<(string, int, decimal)>()),
    ("SO-R783", "ACC-R782", 115m, new[] { ("SKU-R781", 1, 25m), ("SKU-R783", 1, 90m) }),
})
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = orderNumber;
    order["account_id"] = new ArxEntityReference("account", accounts[account]);
    order["ordered_on"] = new DateTime(2026, 11, 8, 9, 0, 0, DateTimeKind.Utc);
    order["total"] = total;
    var orderId = await service.CreateAsync(order);

    foreach (var (sku, quantity, price) in lines)
    {
        var line = new ArxEntity("order_line");
        line["order_id"] = new ArxEntityReference("sales_order", orderId);
        line["product_id"] = new ArxEntityReference("product", products[sku]);
        line["quantity"] = quantity;
        line["unit_price"] = price;
        await service.CreateAsync(line);
    }
}
```

#### 1. A view in another schema, linked to a table and aggregated

`reporting.vw_product_catalogue` (active products with their category's name), named with its schema, then used like any table: a criterion, a LEFT link to `order_line` (a table), units sold per product, the best seller first. The inactive Old drill isn't in the view, so it isn't in the result.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("reporting.vw_product_catalogue") { AggregateInfo = new ArxAggregateInfo() };
query.ColumnSet.AddColumns("sku", "name");
query.Criteria.AddCondition("category", ArxConditionOperator.Equal, "Tools R78");
query.LinkEntities.Add(new ArxLinkEntity("order_line", "id", "product_id", ArxJoinOperator.Left) { EntityAlias = "l" });
query.AggregateInfo.AddAggregateColumn("units", "l.quantity", ArxAggregateOperator.Sum);
query.AggregateInfo.AddGroupByColumns("sku", "name");
query.AddOrder("units", ArxOrderType.Descending);

var rows = await service.RetrieveMultipleAsync(query);

foreach (var row in rows.Entities)
{
    Console.WriteLine($"{row["sku"]} {row["name"],-6} {row["units"]} sold   (entity name: {row.EntityName})");
}
```

Check it in the database:

```sql
SELECT v.sku, v.name, sum(l.quantity) AS units
FROM reporting.vw_product_catalogue v
LEFT JOIN order_line l ON l.product_id = v.id
WHERE v.category = 'Tools R78'
GROUP BY v.sku, v.name
ORDER BY units DESC;
```

Expect: 2 rows: SKU-R781 Hammer 5, SKU-R782 Saw 2 (no Old drill: inactive).

#### 2. A summary view: always up to date

`vw_account_sales_summary` (orders and revenue per account) runs its query at each read: an order created afterwards shows in the next read at once.

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

```csharp
foreach (var row in (await service.RetrieveMultipleAsync(SalesSummary())).Entities)
{
    Console.WriteLine($"{row["name"],-15} orders {row["orders"]}, revenue {row["revenue"]:N2}");
}

var order = new ArxEntity("sales_order");
order["order_number"] = "SO-R784";
order["account_id"] = new ArxEntityReference("account", harbor);
order["ordered_on"] = new DateTime(2026, 11, 8, 15, 0, 0, DateTimeKind.Utc);
order["total"] = 500m;
await service.CreateAsync(order);

var snapshot = (await service.RetrieveMultipleAsync(SalesSummary())).Entities.Single(x => (string)x["name"]! == "Harbor Hardware");

var live = new ArxQueryExpression("sales_order") { AggregateInfo = new ArxAggregateInfo() };
live.Criteria.AddCondition("account_id", ArxConditionOperator.Equal, harbor.AsInt64());
live.AggregateInfo.AddAggregateColumn("orders", "id", ArxAggregateOperator.Count);
live.AggregateInfo.AddAggregateColumn("revenue", "total", ArxAggregateOperator.Sum);
var now = (await service.RetrieveMultipleAsync(live)).Entities[0];

Console.WriteLine($"a new order for Harbor: the view says {snapshot["orders"]} orders, {snapshot["revenue"]:N2}; the tables say {now["orders"]} orders, {now["revenue"]:N2}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression SalesSummary()
{
    var query = new ArxQueryExpression("vw_account_sales_summary");
    query.ColumnSet.AddColumns("name", "orders", "revenue");
    query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R78");
    query.AddOrder("revenue", ArxOrderType.Descending);
    return query;
}
```

Check it in the database:

```sql
SELECT name, orders, revenue
FROM vw_account_sales_summary
WHERE account_number LIKE 'ACC-R78%'
ORDER BY revenue DESC;
```

Expect: Harbor Hardware 2 615.00, Summit Supplies 2 380.00 (the view is always current). SELECT COUNT(*), SUM(total) FROM sales_order WHERE order_number IN ('SO-R783', 'SO-R784'); 2, 615.00: Harbor's orders now.

#### 3. A table with no id, read with a date range and a link

`web_visit` has no id (declared NoIdColumn): its rows are created together and read with a date range and a link to the account.

In scope: `service` (ArxMsSqlDbService), `accounts` (Dictionary<string, ArxRecordId>).

```csharp
var visits = new[]
{
    ("ACC-R781", "/pricing", new DateOnly(2026, 3, 10)),
    ("ACC-R781", "/pricing", new DateOnly(2026, 8, 2)),
    ("ACC-R781", "/contact", new DateOnly(2026, 9, 15)),
    ("ACC-R782", "/pricing", new DateOnly(2026, 10, 1)),
}.Select(x =>
{
    var visit = new ArxEntity("web_visit");
    visit["account_id"] = new ArxEntityReference("account", accounts[x.Item1]);
    visit["page"] = x.Item2;
    visit["visited_on"] = x.Item3;
    return visit;
}).ToList();

await service.CreateMultipleAsync(visits);

var query = new ArxQueryExpression("web_visit");
query.ColumnSet.AddColumns("page", "visited_on");
query.Criteria.AddCondition("visited_on", ArxConditionOperator.GreaterEqual, new DateOnly(2026, 7, 1));
query.Criteria.AddCondition("visited_on", ArxConditionOperator.LessThan, new DateOnly(2026, 10, 1));
var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Inner);
account.EntityAlias = "acc";
account.ColumnSet.AddColumn("name");
account.LinkCriteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R78");
query.AddOrder("visited_on", ArxOrderType.Ascending);

foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine($"July to September: {row["visited_on"]:yyyy-MM-dd} {row["page"],-9} {row["acc.name"]}");
}
```

Check it in the database:

```sql
SELECT v.visited_on, v.page, a.name
FROM web_visit v
JOIN account a ON a.id = v.account_id AND a.account_number LIKE 'ACC-R78%'
WHERE v.visited_on >= '2026-07-01' AND v.visited_on < '2026-10-01'
ORDER BY v.visited_on;
```

Expect: 2 rows: 2026-08-02 /pricing, 2026-09-15 /contact (both Summit Supplies).

### Read functions in depth: table rows, one row, scalar values, what functions don't take, procedures

Advanced case 8 of RetrieveMultiple.

Functions as row sources on SQL Server, in five scenarios. A table-valued function is named with `Function` (arguments by position), and the rest of the query works on its rows as on a table's. Every argument is a typed parameter. RealCase6 shows an inline table-valued function; this sample shows the other shapes and what SQL Server's functions don't do:

- An inline function returning a table's rows, with a criterion, a link entity and an order.
- A multi-statement function returning one row of totals.
- A scalar function: not a row source; its value comes from Execute.
- Named arguments and declared result columns: refused (ARX8001).
- A procedure: not a row source; procedures are Execute's.
SQL Server functions can't write, so there is no AllowWrites case here.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var active = new ArxEntity("account");
active["name"] = "Fn Active";
active["account_number"] = "ACC-R791";
var activeId = await service.CreateAsync(active);
var held = new ArxEntity("account");
held["name"] = "Fn Held";
held["account_number"] = "ACC-R792";
held["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");
held["parent_account_id"] = new ArxEntityReference("account", activeId);
await service.CreateAsync(held);
foreach (var number in new[] { "SO-R791", "SO-R792", "SO-R793" })
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = number;
    order["account_id"] = new ArxEntityReference("account", activeId);
    order["ordered_on"] = new DateTime(2026, 11, 8, 10, 0, 0, DateTimeKind.Utc);
    await service.CreateAsync(order);
}
```

#### 1. A function returning a table's rows, linked

`fn_accounts_with_status('ON_HOLD')` returns whole account rows. Its rows take a criterion and an order like a table's, and a link entity: here the parent account's name.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression(ArxRowSource.Function("fn_accounts_with_status", "ON_HOLD"));
query.ColumnSet.AddColumns("name", "account_number", "status");
query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R79");
var parent = query.AddLink("account", "parent_account_id", "id", ArxJoinOperator.Left);
parent.EntityAlias = "parent";
parent.ColumnSet.AddColumn("name");
query.AddOrder("name", ArxOrderType.Ascending);

foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine($"{row["name"]} ({row["account_number"]}, {row["status"]}), parent: {row["parent.name"] ?? "-"}");
}
```

Check it in the database:

```sql
SELECT f.name, f.account_number, f.status, p.name AS parent
FROM fn_accounts_with_status('ON_HOLD') AS f
LEFT JOIN account p ON p.id = f.parent_account_id
WHERE f.account_number LIKE 'ACC-R79%';
```

Expect: 1 row: Fn Held, ACC-R792, ON_HOLD, Fn Active.

#### 2. A multi-statement function: one row

`fn_account_totals()`, a multi-statement function: one row, an attribute per column.

In scope: `service` (ArxMsSqlDbService).

```csharp
var totals = (await service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("fn_account_totals")))).Entities.Single();
Console.WriteLine($"accounts: {totals["accounts"]}, contacts: {totals["contacts"]}");
```

Check it in the database:

```sql
SELECT * FROM fn_account_totals();
```

Expect: the two numbers the sample printed (they count every sample's rows since the last reset).

#### 3. A scalar function: not a row source; its value through Execute

`fn_order_count` is a scalar function: SQL Server can't select rows from it (UndefinedEntity). A value comes from Execute instead, `result.Value<T>()`.

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

```csharp
try
{
    await service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("fn_order_count", accountId.AsInt64())));
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UndefinedEntity)
{
    Console.WriteLine(error.Message);
}

var count = new ArxProcedureExpression("fn_order_count") { ProcedureType = ArxProcedureType.Function };
count.AddParameter(accountId.AsInt64());
Console.WriteLine($"through Execute: fn_order_count = {(await service.ExecuteAsync(count)).Value<long>()}");
```

Check it in the database:

```sql
SELECT dbo.fn_order_count(a.id)
FROM account a
WHERE a.account_number = 'ACC-R791';
```

Expect: 3.

#### 4. Named arguments and declared columns are refused

SQL Server functions take their arguments by position, and declare their own columns: named arguments and `Returns(...)` are refused before the database (ARX8001), never silently ignored.

In scope: `service` (ArxMsSqlDbService).

```csharp
try
{
    await service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("fn_accounts_with_status", ("p_status", "ON_HOLD"))));
}
catch (ArxNotSupportedException error)
{
    Console.WriteLine(error.Message);
}

try
{
    await service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("fn_account_totals").Returns(("accounts", typeof(long)))));
}
catch (ArxNotSupportedException error)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT COUNT(*) FROM account WHERE account_number LIKE 'ACC-R79%';
```

Expect: 2: nothing was read or written by the refused calls.

#### 5. A procedure is not a row source

A procedure returns no rows to select from: UndefinedEntity; procedures are Execute's.

In scope: `service` (ArxMsSqlDbService).

```csharp
try
{
    await service.RetrieveMultipleAsync(new ArxQueryExpression(ArxRowSource.Function("sp_recalculate_order_totals")));
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UndefinedEntity)
{
    Console.WriteLine(error.Message);
    Console.WriteLine("call it with ExecuteAsync(new ArxProcedureExpression(\"sp_recalculate_order_totals\"))");
}
```

Check it in the database:

```sql
SELECT type_desc FROM sys.objects WHERE name = 'sp_recalculate_order_totals';
```

Expect: SQL_STORED_PROCEDURE.

### Views in depth: computed criteria, links both ways, declared ids and lookups, column boundaries

Advanced case 9 of RetrieveMultiple.

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 SQL Server 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_`).

- 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.
- Links both ways: from a view to a table, and from a table to a view.
- 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.
- A view as a column boundary: a directory without the contacts' private columns; asking it for one is ARX5002.
- A view on a view: read like any other; SQL Server 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.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var accounts = new Dictionary<string, ArxRecordId>();
foreach (var (number, name, contacts) in new[]
{
    ("ACC-R801", "Alpha Views", new[] { "Ann", "Al", "Ash" }),
    ("ACC-R802", "Beta Views", new[] { "Bea", "Bo" }),
    ("ACC-R803", "Gamma Views", new[] { "Gus" }),
})
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    accounts[number] = await service.CreateAsync(account);

    foreach (var first in contacts)
    {
        var contact = new ArxEntity("contact");
        contact["first_name"] = first;
        contact["last_name"] = "Views-R80";
        contact["job_title"] = first == contacts[0] ? "Buyer" : "Engineer";
        contact["email"] = $"{first.ToLowerInvariant()}.r80@example.com";
        contact["account_id"] = new ArxEntityReference("account", accounts[number]);
        await service.CreateAsync(contact);
    }
}
foreach (var (orderNumber, account) in new[] { ("SO-R801", "ACC-R801"), ("SO-R802", "ACC-R803") })
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = orderNumber;
    order["account_id"] = new ArxEntityReference("account", accounts[account]);
    order["ordered_on"] = new DateTime(2026, 11, 8, 12, 0, 0, DateTimeKind.Utc);
    await service.CreateAsync(order);
}
```

#### 1. A computed column as a criterion, ordered and paged

`vw_account_overview.contacts` is a count the view computes. A table has no such column; on the view it's a criterion like any other (the HAVING of the view's GROUP BY, written as a plain condition), and an order. One account per page, with the total.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("vw_account_overview");
query.ColumnSet.AddColumns("name", "contacts");
query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R80");
query.Criteria.AddCondition("contacts", ArxConditionOperator.GreaterEqual, 2);
query.AddOrder("contacts", ArxOrderType.Descending);

for (var pageNumber = 1; ; pageNumber++)
{
    query.PageInfo = new ArxPagingInfo { Count = 1, PageNumber = pageNumber, ReturnTotalRecordCount = pageNumber == 1 };
    var page = await service.RetrieveMultipleAsync(query);
    var row = page.Entities.Single();
    Console.WriteLine($"page {pageNumber}: {row["name"]}, {row["contacts"]} contacts" + (pageNumber == 1 ? $" ({page.TotalRecordCount} accounts in {page.TotalPageCount} pages)" : string.Empty));

    if (!page.HasNextPage)
    {
        break;
    }
}
```

Check it in the database:

```sql
SELECT name, contacts
FROM vw_account_overview
WHERE account_number LIKE 'ACC-R80%' AND contacts >= 2
ORDER BY contacts DESC;
```

Expect: 2 rows: Alpha Views 3, Beta Views 2 (Gamma has 1).

#### 2. Links both ways: view to table, table to view

From the view to a table: each key account's buyers (a LEFT link to contact with its own criterion). From a table to the view: each order with its account's number of contacts, which the orders table doesn't have.

In scope: `service` (ArxMsSqlDbService).

```csharp
var fromView = new ArxQueryExpression("vw_account_overview");
fromView.ColumnSet.AddColumn("name");
fromView.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R80");
var buyers = fromView.AddLink("contact", "id", "account_id", ArxJoinOperator.Left);
buyers.EntityAlias = "buyer";
buyers.ColumnSet.AddColumn("first_name");
buyers.LinkCriteria.AddCondition("job_title", ArxConditionOperator.Equal, "Buyer");
fromView.AddOrder("name", ArxOrderType.Ascending);

foreach (var row in (await service.RetrieveMultipleAsync(fromView)).Entities)
{
    Console.WriteLine($"view → table: {row["name"],-12} buyer {row["buyer.first_name"]}");
}

var toView = new ArxQueryExpression("sales_order");
toView.ColumnSet.AddColumn("order_number");
toView.Criteria.AddCondition("order_number", ArxConditionOperator.BeginsWith, "SO-R80");
var overview = toView.AddLink("vw_account_overview", "account_id", "id", ArxJoinOperator.Inner);
overview.EntityAlias = "acc";
overview.ColumnSet.AddColumns("name", "contacts");
toView.AddOrder("order_number", ArxOrderType.Ascending);

foreach (var row in (await service.RetrieveMultipleAsync(toView)).Entities)
{
    Console.WriteLine($"table → view: {row["order_number"]} for {row["acc.name"]}, contacts: {row["acc.contacts"]}");
}
```

Check it in the database:

```sql
SELECT v.name, c.first_name
FROM vw_account_overview v
LEFT JOIN contact c ON c.account_id = v.id AND c.job_title = 'Buyer'
WHERE v.account_number LIKE 'ACC-R80%'
ORDER BY v.name;
```

Expect: 3 rows: Alpha Views Ann, Beta Views Bea, Gamma Views Gus. SELECT o.order_number, v.name, v.contacts FROM sales_order o JOIN vw_account_overview v ON v.id = o.account_id WHERE o.order_number LIKE 'SO-R80%' ORDER BY o.order_number; 2 rows: SO-R801 Alpha Views 3, SO-R802 Gamma Views 1.

#### 3. A view declared with its id and a lookup

A view has no primary key SQL Server could report, so ARX knows its id only when the developer declares it (as for any table). Declared here with its id and its account_id lookup: Retrieve by id works, the row has a RecordId, and account_id reads back as a reference with the account's display name. The `_lk` direct join works from a view's column too.

In scope: `connectionString` (string).

```csharp
var service = new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id")).DisplayName("account", "name")
    .Table("vw_contact_directory", ArxIdColumn.Supplied<long>("id")).Lookup("vw_contact_directory", "account_id", "account")
    .Build());

var query = new ArxQueryExpression("vw_contact_directory");
query.ColumnSet.AddColumns("first_name", "account_id", "account_id._lk.account.account_number");
query.Criteria.AddCondition("first_name", ArxConditionOperator.Equal, "Bea");
var bea = (await service.RetrieveMultipleAsync(query)).Entities.Single();
var account = (ArxEntityReference)bea["account_id"]!;
Console.WriteLine($"{bea["first_name"]}: RecordId on column {bea.RecordId!.Column}, account_id → {account.EntityName} '{account.Name}', number {bea["account_id.account_number"]}");

var again = await service.RetrieveAsync("vw_contact_directory", bea.RecordId, new ArxColumnSet("first_name", "job_title"));
Console.WriteLine($"Retrieve by that id: {again!["first_name"]}, {again["job_title"]}");
```

Check it in the database:

```sql
SELECT d.first_name, d.job_title, a.name, a.account_number
FROM vw_contact_directory d
JOIN account a ON a.id = d.account_id
WHERE d.first_name = 'Bea' AND d.last_name = 'Views-R80';
```

Expect: 1 row: Bea, Buyer, Beta Views, ACC-R802.

#### 4. A view as a column boundary

`vw_contact_directory` leaves out email, phone, birthday and notes. Asking it for email is ARX5002 (no such column), from the database itself: code that reads the directory can't get them, whatever its query says. Give a role SELECT on the view and not on the table, and the boundary holds outside ARX too.

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("vw_contact_directory");
query.ColumnSet.AddColumns("first_name", "email");
query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Views-R80");

try
{
    await service.RetrieveMultipleAsync(query);
}
catch (ArxException error) when (error.Code == ArxErrorCode.UndefinedAttribute)
{
    Console.WriteLine(error.Message);
}

query.ColumnSet = new ArxColumnSet("first_name", "job_title");
Console.WriteLine($"the directory's own columns: {(await service.RetrieveMultipleAsync(query)).Entities.Count} contacts, first name and job title");
```

Check it in the database:

```sql
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'vw_contact_directory'
ORDER BY ordinal_position;
```

Expect: id, first_name, last_name, job_title, account_id (no email).

#### 5. A view on a view

`vw_key_accounts` is a view on `vw_account_overview` (accounts with two or more contacts). ARX reads it like the others; SQL Server expands both views into one query over the tables (the grouped one as an aggregate step over contact).

In scope: `service` (ArxMsSqlDbService).

```csharp
var query = new ArxQueryExpression("vw_key_accounts");
query.ColumnSet.AddColumns("name", "contacts");
query.Criteria.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-R80");
query.AddOrder("name", ArxOrderType.Ascending);

Console.WriteLine("key accounts: " + string.Join(", ", (await service.RetrieveMultipleAsync(query)).Entities.Select(x => $"{x["name"]} ({x["contacts"]})")));
```

Check it in the database:

```sql
SELECT name, contacts
FROM vw_key_accounts
WHERE account_number LIKE 'ACC-R80%'
ORDER BY name;
```

Expect: 2 rows: Alpha Views 3, Beta Views 2. GO SET SHOWPLAN_TEXT ON; GO SELECT name FROM vw_key_accounts WHERE account_number LIKE 'ACC-R80%'; GO SET SHOWPLAN_TEXT OFF; one plan over account and contact, with no view in it: SQL Server expands both views into the query (the grouped one as an aggregate step over contact).

### Retrieve on a preloaded schema: lookups found, column types, Strict, saved JSON

Advanced case 10 of RetrieveMultiple.

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: SQL Server has no enum types) 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.

Good to know:

Display names and selection sets are the developer's: a preload doesn't guess them. Add them in the preload's `configure` callback, as below.

Usings:

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

#### 1. A lookup the preload found, read back with its display name

Preload account (contact comes with it), name contacts, and read the primary contact back as a reference.

In scope: `connectionString` (string).

```csharp
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("account");

var schema = await new ArxPreLoadTable(tables).LoadAsync(
    connectionString,
    configure => configure.DisplayName("contact", ["first_name", "last_name"], " "));
var service = new ArxMsSqlDbService(connectionString, schema);

var contact = new ArxEntity("contact");
contact["first_name"] = "Rita";
contact["last_name"] = "Retrieve";
contact["email"] = "rita.retrieve@example.com";
var contactId = await service.CreateAsync(contact);

var account = new ArxEntity("account");
account["name"] = "Lookup Lighting";
account["account_number"] = "ACC-PRE111";
account["primary_contact_id"] = new ArxEntityReference("contact", contactId);
await service.CreateAsync(account);

var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumns("name", "primary_contact_id");
query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE111");
var read = (await service.RetrieveMultipleAsync(query)).Entities.Single();
var primary = (ArxEntityReference)read["primary_contact_id"]!;
Console.WriteLine($"{read["name"]}: primary contact {primary.EntityName} {primary.RecordId}, \"{primary.Name}\"");

return schema;
```

Check it in the database:

```sql
SELECT a.name, c.first_name + ' ' + c.last_name AS primary_contact
FROM account a JOIN contact c ON c.id = a.primary_contact_id
WHERE a.account_number = 'ACC-PRE111';
```

Expect: Lookup Lighting, Rita Retrieve.

#### 2. A column of fixed values: its type, a string in and out

The showcase table's mood: fixed text values a CHECK guards; its preloaded type, a string in and out.

In scope: `connectionString` (string).

```csharp
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("data_type_showcase", ["text_value", "mood"]);

var schema = await new ArxPreLoadTable(tables).LoadAsync(connectionString);
var service = new ArxMsSqlDbService(connectionString, schema);

schema.TryGetTable("data_type_showcase", out var table);
Console.WriteLine($"mood: {table!.Columns["mood"]}, {table.Columns["mood"].Traits}");

var row = new ArxEntity("data_type_showcase");
row["text_value"] = "preloaded mood";
row["mood"] = "happy";
await service.CreateAsync(row);

var query = new ArxQueryExpression("data_type_showcase");
query.ColumnSet.AddColumns("text_value", "mood");
query.Criteria.AddCondition("mood", ArxConditionOperator.Equal, "happy");
query.Criteria.AddCondition("text_value", ArxConditionOperator.Equal, "preloaded mood");
var read = (await service.RetrieveMultipleAsync(query)).Entities.Single();
Console.WriteLine($"read back: mood \"{read["mood"]}\" ({read["mood"]!.GetType().Name})");
```

Check it in the database:

```sql
SELECT text_value, mood, SQL_VARIANT_PROPERTY(mood, 'BaseType') AS stored_as
FROM data_type_showcase
WHERE text_value = 'preloaded mood';
```

Expect: 1 row, happy, nvarchar: text the column's CHECK allows (SQL Server has no enum types).

#### 3. Strict: reading all columns reads the preloaded ones

Strict: all columns means the three preloaded; any other column is refused before the database.

In scope: `connectionString` (string).

```csharp
// A public directory endpoint: name, number and website, never the credit limit.
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("account", ["name", "account_number", "website"]);

var schema = await new ArxPreLoadTable(tables).LoadAsync(connectionString);
var directory = new ArxMsSqlDbService(connectionString, schema) { Strict = true };

var all = new ArxQueryExpression("account") { ColumnSet = ArxColumnSet.All };
all.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE111");
var read = (await directory.RetrieveMultipleAsync(all)).Entities.Single();
Console.WriteLine($"all columns: {string.Join(", ", read.Attributes.Keys)}");

var probe = new ArxQueryExpression("account");
probe.ColumnSet.AddColumns("name", "credit_limit");

try
{
    await directory.RetrieveMultipleAsync(probe);
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.NotPreloaded)
{
    Console.WriteLine(exception.Message);
}
```

Check it in the database:

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

Expect: 1 row: the four columns the Strict read returned (the id is always loaded).

#### 4. Save the preload as JSON and start from it

Save the preload as JSON; the next startup builds the catalog from it, without a catalog query.

In scope: `connectionString` (string), `preloaded` (ArxSchemaCatalog).

```csharp
// ARX doesn't write files: the application keeps the JSON where it likes (a file, a settings store).
var json = preloaded.ToJson();
var schema = ArxSchemaCatalog.Create().AddJson(json).Build();
var service = new ArxMsSqlDbService(connectionString, schema);

// The JSON doesn't drift by itself: check it against the database at startup, as with any declared catalog.
await service.ValidateSchemaAsync();

var query = new ArxQueryExpression("account");
query.ColumnSet.AddColumns("name", "primary_contact_id");
query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE111");
var primary = (ArxEntityReference)(await service.RetrieveMultipleAsync(query)).Entities.Single()["primary_contact_id"]!;
Console.WriteLine($"from the JSON ({json.Length:N0} characters): primary contact \"{primary.Name}\"");
```

Note: Nothing new is stored: the catalog was rebuilt in memory from the JSON.