# RetrieveMultiple with ARX.SDK on MongoDB

## What a RetrieveMultiple does

| Rule | On MongoDB |
|---|---|
| A query | One aggregation pipeline: `$match`, `$lookup` for links, `$group` for aggregates, `$sort`, the page |
| **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 |
| Conditions | Equality, comparisons, In, Between, patterns (escaped regular expressions; Contains, BeginsWith and EndsWith ignore case) |
| Field paths | A dotted name in a condition, an order or a column set is a link's field when its first part is the link's alias (`acc.name`), and otherwise a path into an embedded document (`address.city`), each part checked by the name rules. The SQL providers refuse a dotted name that names no link (ARX1001) |
| Null | `Null` matches a stored null and a missing field; NotEqual and the other negations never match either |
| Joins | Inner and left (`$lookup`); a right join is ARX8001; link fields come back as `alias.field` |
| Paging | By page number (with the total in the same round trip), or by the paging cookie each page returns |
| Paging cookies | Sealed by default (encrypted and authenticated: a client can neither read nor change one), with a random key of the process unless `PagingCookieKey` is set. Several instances, or cookies across a restart: the same `PagingCookieKey` everywhere, or a cookie is ARX1001 on the other. `PagingCookieProtection` can be `Signed` or `Plain` (Advanced 4) |

## The samples

Every RetrieveMultiple sample, in reading order. Each scenario shows the code (verified against MongoDB) and the mongosh query that checks what it did. `service` is the application's `ArxMongoDbService`; 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-mongodb-sample-application.md.

### Read many records

Basics 1 of RetrieveMultiple.

Read many documents with conditions and an order, in three scenarios: comparisons and an order, text patterns, and what null means on MongoDB.

Good to know:

Contains, BeginsWith and EndsWith ignore case, and their text is never read as a regular expression. `Null` matches a stored null and a missing field; NotEqual and the other negations never match either, as in SQL.

Usings:

```csharp
using ARX.SDK.MongoDb.Services;
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, score) in new[] { ("Ana", "Silva", (int?)80), ("Bruno", "Costa", 55), ("Carla", "Sá", null), ("Diogo", "Silva (Jr.)", 91) })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = last;
    contact["segment"] = "M96";

    if (score != null)
    {
        contact["score"] = score;
    }

    contacts.Add(contact);
}
await service.CreateMultipleAsync(contacts);
```

#### 1. Comparisons and an order

A score of 60 or more, best first.

In scope: `service` (ArxMongoDbService).

```csharp
var query = Segment();
query.Criteria.AddCondition("score", ArxConditionOperator.GreaterEqual, 60);
query.AddOrder("score", ArxOrderType.Descending);

var page = await service.RetrieveMultipleAsync(query);

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

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Segment()
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("first_name", "last_name", "score");
    query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M96");
    return query;
}
```

Check it in the database:

```sql
db.contact.find({ segment: "M96", score: { $gte: 60 } }, { first_name: 1, score: 1 }).sort({ score: -1 })
```

Expect: 2 documents: Diogo 91, Ana 80.

#### 2. Text patterns

Last names containing "silva", any case; "(Jr.)" is plain text, not a pattern.

In scope: `service` (ArxMongoDbService).

```csharp
var silva = Segment();
silva.Criteria.AddCondition("last_name", ArxConditionOperator.Contains, "silva");

var junior = Segment();
junior.Criteria.AddCondition("last_name", ArxConditionOperator.EndsWith, "(Jr.)");

Console.WriteLine($"contains silva: {(await service.RetrieveMultipleAsync(silva)).Entities.Count}; ends with (Jr.): {(await service.RetrieveMultipleAsync(junior)).Entities.Count}");
```

Check it in the database:

```sql
db.contact.countDocuments({ segment: "M96", last_name: /silva/i })
```

Expect: 2.

#### 3. What null means

Carla has no score: Null finds her; NotEqual 80 doesn't.

In scope: `service` (ArxMongoDbService).

```csharp
var noScore = Segment();
noScore.Criteria.AddCondition("score", ArxConditionOperator.Null);

var notEighty = Segment();
notEighty.Criteria.AddCondition("score", ArxConditionOperator.NotEqual, 80);

Console.WriteLine($"no score: {string.Join(", ", (await service.RetrieveMultipleAsync(noScore)).Entities.Select(x => x["first_name"]))}; "
    + $"score not 80: {string.Join(", ", (await service.RetrieveMultipleAsync(notEighty)).Entities.Select(x => x["first_name"]))}");
```

Check it in the database:

```sql
db.contact.find({ segment: "M96", score: null }, { first_name: 1 })
```

Expect: 1 document, Carla (her document has no score field).

### Page through results

Basics 2 of RetrieveMultiple.

Page through a result, in two scenarios: by page number with the total, and by the paging cookie each page returns.

Good to know:

A page number skips the earlier documents on the server; the total comes in the same round trip. A cookie says where the next page starts (the last document's sort values and id), so no document is skipped or read twice even when values repeat; pass it back unchanged, with the same query.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, 7).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"SKU-M97{i}";
    product["name"] = $"Product {i}";
    product["category"] = "M97";
    product["price"] = i % 3 * 5m;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. By page number

Page 2 of 3, with the total.

In scope: `service` (ArxMongoDbService).

```csharp
var query = Category();
query.PageInfo = new ArxPagingInfo { Count = 3, PageNumber = 2, ReturnTotalRecordCount = true };

var page = await service.RetrieveMultipleAsync(query);

Console.WriteLine($"page {page.CurrentPage} of {page.TotalPageCount} ({page.TotalRecordCount} products): {string.Join(", ", page.Entities.Select(x => x["name"]))}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Category()
{
    var query = new ArxQueryExpression("product");
    query.ColumnSet.AddColumns("name", "price");
    query.Criteria.AddCondition("category", ArxConditionOperator.Equal, "M97");
    query.AddOrder("price", ArxOrderType.Ascending);
    return query;
}
```

Check it in the database:

```sql
db.product.countDocuments({ category: "M97" })
```

Expect: 7.

#### 2. By cookie

Every page by cookie: pass back the one the previous page gave, until there is none.

In scope: `service` (ArxMongoDbService).

```csharp
string? cookie = null;
var seen = new List<string>();

do
{
    var query = Category();
    query.PageInfo = new ArxPagingInfo { Count = 3, PagingCookie = cookie };
    var page = await service.RetrieveMultipleAsync(query);
    seen.AddRange(page.Entities.Select(x => (string)x["sku"]!));
    cookie = page.PagingCookie;
}
while (cookie != null);

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

Check it in the database:

```sql
db.product.find({ category: "M97" }, { name: 1, price: 1 }).sort({ price: 1, _id: 1 })
```

Expect: 7 documents, in the order the pages read them.

### Read an account with its contacts

Real case 1 of RetrieveMultiple.

An account and its contacts, three ways: lookups read back as references with the account's name, a join (`$lookup`) from contacts to their account, and a left join that keeps contacts without one.

Good to know:

A declared lookup (`.Lookup("contact", "account_id", "account")`) reads back as an `ArxEntityReference` whose `Name` is the account's display name. A link entity is a `$lookup`: inner or left; its fields come back as `alias.field`.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "City Power & Light";
account["account_number"] = "ACC-M981";
var accountId = await service.CreateAsync(account);
foreach (var (first, last, hasAccount) in new[] { ("Ivo", "Matos", true), ("Joana", "Reis", true), ("Kai", "Lopes", false) })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = last;
    contact["segment"] = "M98";

    if (hasAccount)
    {
        contact["account_id"] = new ArxEntityReference("account", accountId);
    }

    await service.CreateAsync(contact);
}
```

#### 1. Lookups with the account's name

Read the contacts: account_id comes back as a reference named after the account.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.ColumnSet.AddColumns("first_name", "account_id");
query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M98");
query.Criteria.AddCondition("account_id", ArxConditionOperator.NotNull);
query.AddOrder("first_name", ArxOrderType.Ascending);

foreach (var contact in (await service.RetrieveMultipleAsync(query)).Entities)
{
    var reference = (ArxEntityReference)contact["account_id"]!;
    Console.WriteLine($"{contact["first_name"]} works at {reference.Name}");
}
```

Check it in the database:

```sql
db.contact.find({ segment: "M98", account_id: { $ne: null } }, { first_name: 1, account_id: 1 })
```

Expect: 2 documents, Ivo and Joana, account_id the account's ObjectId.

#### 2. A join to the account

An inner join: only contacts with an account, with its name and number.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.ColumnSet.AddColumn("first_name");
query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M98");
var link = query.AddLink("account", "account_id", "account_id", ArxJoinOperator.Inner);
link.EntityAlias = "acc";
link.ColumnSet.AddColumns("name", "account_number");
query.AddOrder("first_name", ArxOrderType.Ascending);

foreach (var contact in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine($"{contact["first_name"]}: {contact["acc.name"]} ({contact["acc.account_number"]})");
}
```

Check it in the database:

```sql
db.contact.aggregate([{ $match: { segment: "M98" } }, { $lookup: { from: "account", localField: "account_id", foreignField: "_id", as: "acc" } }, { $unwind: "$acc" }])
```

Expect: 2 documents, Ivo and Joana, each with the account City Power & Light.

#### 3. A left join

A left join keeps Kai, who has no account: his account fields read null.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.ColumnSet.AddColumn("first_name");
query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M98");
var link = query.AddLink("account", "account_id", "account_id", ArxJoinOperator.Left);
link.EntityAlias = "acc";
link.ColumnSet.AddColumn("name");
query.AddOrder("first_name", ArxOrderType.Ascending);

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

Check it in the database:

```sql
db.contact.countDocuments({ segment: "M98" })
```

Expect: 3.

### A report by industry

Real case 2 of RetrieveMultiple.

A report from aggregates (`$group`), in two scenarios: counts and sums by industry, and the distinct regions.

Good to know:

Group-by columns and aggregate aliases are the result's attributes; order by either. A count of a field counts the documents that have a value for it, as SQL's COUNT(column).

Usings:

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

Set up by the sample before its scenarios:

```csharp
var accounts = new[] { (Industry.Retail, 120, "North"), (Industry.Retail, 80, "South"), (Industry.Finance, 300, "North"), (Industry.Technology, 45, "North") }
    .Select((x, i) =>
    {
        var account = new ArxEntity("account");
        account["name"] = $"Report account {i}";
        account["account_number"] = $"ACC-M99{i}";
        account["industry"] = new ArxSelectionSetValue("Industry", (int)x.Item1);
        account["employee_count"] = x.Item2;
        account["region"] = x.Item3;
        account["report"] = "M99";
        return account;
    })
    .ToList();
await service.CreateMultipleAsync(accounts);
```

#### 1. Counts and sums by industry

Accounts and employees by industry, the largest first.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("account");
query.Criteria.AddCondition("report", ArxConditionOperator.Equal, "M99");
query.AggregateInfo = new ArxAggregateInfo();
query.AggregateInfo.AddGroupByColumn("industry");
query.AggregateInfo.AddAggregateColumn("accounts", "", ArxAggregateOperator.Count);
query.AggregateInfo.AddAggregateColumn("employees", "employee_count", ArxAggregateOperator.Sum);
query.AddOrder("employees", ArxOrderType.Descending);

foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
{
    Console.WriteLine($"{(Industry)Convert.ToInt32(row["industry"])}: {row["accounts"]} accounts, {row["employees"]} employees");
}
```

Check it in the database:

```sql
db.account.aggregate([{ $match: { report: "M99" } }, { $group: { _id: "$industry", accounts: { $sum: 1 }, employees: { $sum: "$employee_count" } } }, { $sort: { employees: -1 } }])
```

Expect: 3 groups: Finance 1 / 300, Retail 2 / 200, Technology 1 / 45.

#### 2. Distinct regions

The regions the report's accounts are in, once each.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("account") { Distinct = true };
query.ColumnSet.AddColumn("region");
query.Criteria.AddCondition("report", ArxConditionOperator.Equal, "M99");
query.AddOrder("region", ArxOrderType.Ascending);

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

Check it in the database:

```sql
db.account.distinct("region", { report: "M99" })
```

Expect: ["North", "South"].

### Catch an injection attempt

Real case 3 of RetrieveMultiple.

A search endpoint attacked three ways: an operator as the field to search, a regular expression as the text to find, and a sort field built from user input.

Good to know:

Names with `$` or SQL syntax are ARX1017 before anything is sent. Search text is escaped, so `.*` finds the two characters ".*", not everything.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var contact = new ArxEntity("contact");
contact["first_name"] = "Private";
contact["segment"] = "M90";
await service.CreateAsync(contact);
```

#### 1. An operator as the field

Search field "$where": ARX1017.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.Criteria.AddCondition("$where", ArxConditionOperator.Equal, "this.first_name != ''");

try
{
    await service.RetrieveMultipleAsync(query);
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.SuspectedInjection)
{
    Console.WriteLine(exception.Message);
}
```

Check it in the database:

```sql
db.contact.countDocuments({ segment: "M90" })
```

Expect: 1: still there, and nothing was read.

#### 2. A regular expression as the text

Search text ".*": escaped, it matches no name.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M90");
query.Criteria.AddCondition("first_name", ArxConditionOperator.Contains, ".*");

Console.WriteLine($"{(await service.RetrieveMultipleAsync(query)).Entities.Count} contacts found");
```

Check it in the database:

```sql
db.contact.countDocuments({ segment: "M90", first_name: { $regex: "\\.\\*", $options: "i" } })
```

Expect: 0.

#### 3. A sort field from the user

A sort field from the query string, with SQL in it: ARX1017.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("contact");
query.AddOrder("first_name; DROP TABLE contact", ArxOrderType.Ascending);

try
{
    await service.RetrieveMultipleAsync(query);
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.SuspectedInjection)
{
    Console.WriteLine(exception.Message);
}
```

Check it in the database:

```sql
db.getCollectionNames().includes("contact")
```

Expect: true.

### Read your writes in a transaction

Advanced case 1 of RetrieveMultiple.

Reads inside an ARX transaction see its own writes; reads outside don't until the commit. Two scenarios: before and after the commit.

Good to know:

A transaction reads one snapshot of the database, plus its own writes.

Usings:

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

Set up by the sample before its scenarios:

```csharp
await using var connection = new ArxMongoConnection(service);
await using var transaction = await connection.BeginTransactionAsync();
```

#### 1. Before the commit

Two contacts written in the transaction: it reads both, a read outside reads none.

In scope: `service` (ArxMongoDbService), `transaction` (ArxTransaction).

```csharp
var contacts = new[] { "Lia", "Rui" }.Select(name =>
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = name;
    contact["segment"] = "M94";
    return contact;
}).ToList();

await service.CreateMultipleAsync(contacts, transaction);

var inside = (await service.RetrieveMultipleAsync(Imported(), transaction)).Entities.Count;
var outside = (await service.RetrieveMultipleAsync(Imported())).Entities.Count;

Console.WriteLine($"inside the transaction: {inside}; outside: {outside}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Imported()
{
    var query = new ArxQueryExpression("contact");
    query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M94");
    return query;
}
```

Check it in the database:

```sql
db.contact.countDocuments({ segment: "M94" })
```

Expect: 0 while the transaction is open.

#### 2. After the commit

After the commit everyone reads them.

In scope: `service` (ArxMongoDbService), `transaction` (ArxTransaction).

```csharp
await transaction.CommitAsync();

Console.WriteLine($"outside, after the commit: {(await service.RetrieveMultipleAsync(Imported())).Entities.Count}");
```

Check it in the database:

```sql
db.contact.countDocuments({ segment: "M94" })
```

Expect: 2.

### A security check around a search endpoint

Advanced case 2 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` (ArxMongoDbService), `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(ArxMongoDbService 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
db.account.find({ account_number: /^ACC\-R74/ }, { account_number: 1 }).sort({ account_number: 1 })
```

Expect: 2 documents, 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` (ArxMongoDbService), `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
db.getCollectionNames().includes("account")
```

Expect: true.

#### 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` (ArxMongoDbService), `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(ArxMongoDbService 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
db.account.countDocuments({ password: { $exists: true } })
```

Expect: 0: no document has such a field, 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` (ArxMongoDbService), `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
db.account.countDocuments({ account_number: /^ACC\-R74/ })
```

Expect: 2, what the mapped search read.

### Set up the service from the database's schema

Advanced case 3 of RetrieveMultiple.

The service set up from the database instead of by hand, then reading through it, in two scenarios: preload the lead collection at startup, and reading with Strict on, where only the preloaded fields may be named.

Good to know:

`ArxPreLoadTable` reads, once, each collection's id, its unique indexes (as keys) and the field types of its `$jsonSchema` validator. With `Strict`, a field the preload didn't load is ARX1018 before anything is sent.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var lead = new ArxEntity("lead");
lead["email"] = "lead-m861@example.com";
lead["full_name"] = "Nuno Teixeira";
lead["score"] = 40;
await service.CreateAsync(lead);
```

#### 1. Preload at startup

Preload the lead collection once, at startup, and make the service Strict.

In scope: `connectionString` (string).

```csharp
var catalog = await new ArxPreLoadTable(new ArxPreLoadTableRegister().PreLoadTable("lead")).LoadAsync(connectionString);
var service = new ArxMongoDbService(connectionString, catalog) { Strict = true };

catalog.TryGetTable("lead", out var table);
Console.WriteLine($"lead: id {table.IdColumn}, key {string.Join(", ", table.Keys)}, fields {string.Join(", ", table.Columns.Keys)}");

return service;
```

Check it in the database:

```sql
db.lead.getIndexes()
```

Expect: _id_ and the unique email_1 index.

#### 2. Read with the preloaded types

Read with the preloaded types: score comes back as an int; a field the preload doesn't know is refused.

In scope: `service` (ArxMongoDbService).

```csharp
var query = new ArxQueryExpression("lead");
query.ColumnSet.AddColumns("email", "score");
query.Criteria.AddCondition("email", ArxConditionOperator.Equal, "lead-m861@example.com");
var lead = (await service.RetrieveMultipleAsync(query)).Entities.Single();
Console.WriteLine($"score {lead["score"]} ({lead["score"]?.GetType().Name})");

var unknown = new ArxQueryExpression("lead");
unknown.ColumnSet.AddColumn("password");

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

Check it in the database:

```sql
db.lead.find({ email: "lead-m861@example.com" }, { score: 1 })
```

Expect: 1 document, score 40.

### Protect paging cookies

Advanced case 4 of RetrieveMultiple.

Protect the paging cookies your API hands to clients, in three scenarios: sealed by default, one key shared by every instance of the application, and plain cookies when you choose them.

Good to know:

A paging cookie carries the values the page is ordered by, of its last document: here a contact's email. A client could decode it, and change it to page from somewhere else. So ARX seals cookies by default (encrypted and authenticated, AES-256-GCM): a client can neither read nor change one. Without a key from you, ARX seals with a random key of the process, which every service in the process shares: right for one process. When several instances serve the same clients, or cookies must survive a restart, give every instance the same `PagingCookieKey` (32 bytes or more, from your configuration or secret store); otherwise a cookie that reaches another instance is ARX1001. `PagingCookieProtection` can also be `Signed` (readable, not changeable) or `Plain`.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.MongoDb.Services;
using ARX.SDK.Query;
using ARX.SDK.Services;
using System.Text;
```

Set up by the sample before its scenarios:

```csharp
var contacts = new[] { "ana", "ben", "cy", "dee" }.Select(name =>
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = name;
    contact["last_name"] = "M991";
    contact["email"] = $"{name}.m991@example.com";
    return contact;
}).ToList();
await service.CreateMultipleAsync(contacts);
```

#### 1. Sealed by default

The default: the cookie hides the email it pages by, and the next page reads it.

In scope: `service` (ArxMongoDbService).

```csharp
var first = await service.RetrieveMultipleAsync(Query());
Console.WriteLine($"protection {service.PagingCookieProtection}; the cookie shows the email: {Readable(first.PagingCookie).Contains("ben.m991@example.com")}");

var second = await service.RetrieveMultipleAsync(Query(first.PagingCookie));
Console.WriteLine($"next page: {string.Join(", ", second.Entities.Select(x => x["email"]))}");
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Query(string? cookie = null)
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("email");
    query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "M991");
    query.AddOrder("email", ArxOrderType.Ascending);
    query.PageInfo = new ArxPagingInfo { Count = 2, PagingCookie = cookie };
    return query;
}

// What a client could read from a cookie: its bytes as text.
private static string Readable(string cookie)
{
    var base64 = cookie.Replace('-', '+').Replace('_', '/');
    return Encoding.UTF8.GetString(Convert.FromBase64String(base64.PadRight(base64.Length + (4 - base64.Length % 4) % 4, '=')));
}
```

Check it in the database:

```sql
db.contact.find({ last_name: "M991" }, { email: 1 }).sort({ email: 1 })
```

Expect: 4 documents; the second page holds cy.m991@example.com and dee.m991@example.com.

#### 2. One key for every instance

Two instances of the application: with one shared key, either reads the other's cookies; with their own, it is ARX1001.

In scope: `connectionString` (string).

```csharp
// In production the key comes from configuration or a secret store, the same on every instance.
var sharedKey = ArxPagingCookie.NewKey();
var instanceA = new ArxMongoDbService(connectionString, SampleCatalog.Build()) { PagingCookieKey = sharedKey };
var instanceB = new ArxMongoDbService(connectionString, SampleCatalog.Build()) { PagingCookieKey = sharedKey };

var first = await instanceA.RetrieveMultipleAsync(Query());
var second = await instanceB.RetrieveMultipleAsync(Query(first.PagingCookie));
Console.WriteLine($"instance B continued: {string.Join(", ", second.Entities.Select(x => x["email"]))}");

var withItsOwnKey = new ArxMongoDbService(connectionString, SampleCatalog.Build()) { PagingCookieKey = ArxPagingCookie.NewKey() };

try
{
    await withItsOwnKey.RetrieveMultipleAsync(Query(first.PagingCookie));
}
catch (ArxValidationException exception)
{
    Console.WriteLine(exception.Message);
}
```

Defined in the sample, used above:

```csharp
private static ArxQueryExpression Query(string? cookie = null)
{
    var query = new ArxQueryExpression("contact");
    query.ColumnSet.AddColumns("email");
    query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "M991");
    query.AddOrder("email", ArxOrderType.Ascending);
    query.PageInfo = new ArxPagingInfo { Count = 2, PagingCookie = cookie };
    return query;
}
```

Check it in the database:

```sql
db.contact.countDocuments({ last_name: "M991" })
```

Expect: 4 (nothing is written: the check is in the cookie).

#### 3. Plain, when you choose it

Plain cookies: readable and changeable by clients, so only where their values aren't sensitive.

In scope: `connectionString` (string).

```csharp
var service = new ArxMongoDbService(connectionString, SampleCatalog.Build()) { PagingCookieProtection = ArxPagingCookieProtection.Plain };
var first = await service.RetrieveMultipleAsync(Query());
Console.WriteLine($"the cookie shows the email: {Readable(first.PagingCookie).Contains("ben.m991@example.com")}");
```

Check it in the database:

```sql
db.contact.countDocuments({ last_name: "M991" })
```

Expect: 4.