# Create with ARX.SDK on PostgreSQL

## What a create does

| | |
|---|---|
| **Names** | The entity name is the table; each attribute name is a column. Only ASCII letters, digits and underscores (at most 63 characters): `ARX1017 SuspectedInjection` when a name carries SQL, `ARX1003 InvalidIdentifier` for a plain bad name, both before anything is sent |
| **Values** | Always parameters: `Robert'); DROP TABLE contact;--`, `O'Brien`, emoji and backslashes are stored exactly as typed |
| **The id column** | Every table declares its one id column, or `ArxIdColumn.NoIdColumn`: in the schema catalog given to the service (the usual way), or on the entity (`entity.IdColumn`) when the service has no schema; never both. Undeclared: `ARX1016 IdNotDeclared`. `Generated` means the database *can* fill the id in (an id you set is written as given); `Supplied` means you always give it (missing or null: `ARX1001`) |
| **What comes back** | `CreateAsync` returns the new id as an `ArxRecordId` (`AsInt64()`, `AsInt32()`, `AsString()`, `AsGuid()`, or `Value`); the same id is set on `entity.RecordId`. A table with no id column gives `ArxRecordId.None` (`HasValue` is false) |
| **Keys** | Optional (`.Key(table, columns…)`). Create doesn't use them: the database's unique constraints enforce them. Update and Delete find rows by them |
| **Defaults** | Columns you don't set get their database defaults. `new ArxIgnore()` leaves an attribute out |
| **Errors** | The class says where the error was found, the code what happened: `ArxValidationException` for a request ARX refuses before the database (1xxx, and the 4xxx value checks ARX makes itself); `ArxDatabaseException` for an error the database reports (3xxx, 7xxx, and the 4xxx and 5xxx it reports); `ArxNotSupportedException` for ARX8001. The outcome codes (2xxx: nothing matched, several matched, a changed record) and a value read back as a type the schema doesn't expect (ARX5003) can come as the base `ArxException`. All of them are `ArxException`, with `Code`, `Category`, `EntityName` and `AttributeName`: catch `ArxException` and branch on the code or category, never on the class or the message. Messages name tables, columns and constraints, never values, so they are safe to log |
| **Without a catalog** | A service created with only the connection string reads each table's primary key and column types from the database before the call: the id is generated when the key has a default or identity, supplied otherwise; one more round trip per call, so much slower than with a catalog: meant for simple CRUD and small applications. `RememberResolvedTables = true` reads each table once per service. No primary key, a compound one or one of an unusable type is `ARX1019 TableNotResolvable` (declare it in a catalog or with `entity.IdColumn`); a service with a catalog never resolves (Basic6, Advanced10) |

## Reference

### Where a table is declared

| The service has | The call passes a schema | The entity declares | Result |
|---|---|---|---|
| A schema | No | Nothing | The service's schema |
| A schema | Yes | Nothing | The call's schema for the tables it declares; the service's for the others (selection sets the same way, by name) |
| No schema | Yes | Nothing | The call's schema |
| No schema | No | `IdColumn` (and `Keys`) | The entity's declaration |
| A schema, or the call passes one | | `IdColumn` or `Keys` | Refused: `ARX1001`, before anything is sent |
| No schema | No | Nothing | Read from the database before the call (a service without a catalog): the table's primary key is its id; `ARX1019 TableNotResolvable` when the call needs an id the table doesn't give (no primary key, a compound one, a type ARX can't use) |
| A schema that doesn't declare the table | No | Nothing | Refused: `ARX1016 IdNotDeclared`, naming the line to add |

Entities with the same declaration share one statement, even when each gets its own `ArxIdColumn` object.

### Ids by kind of table

| The table has | Declared as | What you do |
|---|---|---|
| A number the database generates | `Generated<long>("id")` | Leave it out; read the returned id (or `entity.RecordId`) after Create |
| A uuid the database generates | `Generated<Guid>("id")` | Leave it out, or set it to write your own (`device["id"] = Guid.NewGuid()`) |
| A natural id (code, SKU) | `Supplied<string>("code")` | Always set it; missing or null is `ARX1001` |
| No id column (a log, or rows identified by several columns) | `NoIdColumn`, plus `.Key(table, columns…)` if columns identify a row | Set the columns; Create returns `ArxRecordId.None`, and the database enforces the key |

Id types: `short`, `int`, `long`, `decimal`, `string`, `Guid`, `DateOnly`, `DateTime`.

### Lookups

`new ArxEntityReference(table, id, name?)`: the referenced **table first, then the id** (of any id type, or the
`ArxRecordId` Create returned), then an optional display name.

| Id type of the referenced table | Constructor |
|---|---|
| `short`, `int`, `long` | `new ArxEntityReference("account", 42L)` |
| `string` | `new ArxEntityReference("country", "PT")` |
| `Guid` | `new ArxEntityReference("device", deviceId)` |
| `decimal`, `DateOnly`, `DateTime` | `new ArxEntityReference("t", value)` |
| Any: the id Create returned | `new ArxEntityReference("account", accountId)` |

- **Checked before anything is sent** (`ARX1005 InvalidLookup`, naming the attribute): the reference has an id (not
  missing, zero, negative, an empty Guid or empty text); with a schema, the table is declared, has an id column, and
  the id has its type (integer widths are interchangeable).
- **Checked by the database:** the referenced record exists, through the foreign key (`ARX3002 ForeignKeyViolation`).
  Without a foreign key, an id that matches nothing is stored as given.

### Value types

| .NET value | Column | Stored as |
|---|---|---|
| `string` | `text`, `varchar(n)`, `char(n)` | as written (`char(n)` pads with spaces) |
| `short` / `int` / `long` | `smallint` / `int` / `bigint` | `32000` / `2000000000` / `9000000000000` |
| `decimal` | `numeric(p, s)` | `12345.678901` |
| `float` / `double` | `real` / `double precision` | `3.25` / `2.718281828459045` |
| `bool` | `boolean` | `true` |
| `Guid` | `uuid` | `6f9619ff-8b86-d011-b42d-00c04fc964ff` |
| `DateTime` (Kind `Utc`), `DateTimeOffset` | `timestamptz` | the UTC instant: `2026-11-07 14:30:00+00` |
| `DateTime` (Kind `Unspecified`) | `timestamp` | as written: `2026-11-07 09:00:00` |
| `DateOnly` / `TimeOnly` / `TimeSpan` | `date` / `time` / `interval` | `2026-11-07` / `17:45:30` / `1 day 12:30:00` |
| `byte[]`, `ArxBytea` | `bytea` | `\xcafe` |
| `ArxJsonB` | `jsonb` | the JSON |
| `string[]`, `int[]`, `decimal[]`, `Guid[]`, `DateOnly[]`, `DateTime[]`, … | `text[]`, `int[]`, … | `{red,green}`, `{1,2,3}`, … |
| `ArxEntityReference` | the lookup column | the referenced id |
| `ArxSelectionSetValue` | the set's column | the option's code |
| `null` | any | `NULL` |
| `byte`, `sbyte` / `ushort` / `uint` / `ulong` | converted to `smallint` / `int` / `bigint` / `numeric` | `200` / `65000` / `4000000000` / `18446744073709551615` |
| `char` | text | `X` |
| a plain `enum` | refused (ARX1007) | write `(int)value` or `value.ToString()`, or use a selection set, which stores the code its set declares |
| `DateTime` with Kind `Local` | `timestamptz` | converted to UTC |

Any other type is refused before anything is sent (`ARX1007 UnsupportedValueType`): write `uri.ToString()` rather than
a `Uri`. `RealCase2_EveryDataType` writes every row of this table and prints what PostgreSQL stored.

With the column types preloaded (`ArxPreLoadMode.DataTypes`), a number with a fraction written to a whole-number column is refused before PostgreSQL (`ARX4001 ValueOutOfRange`) on a create, an update and a write by a filter, where PostgreSQL would round it (7.9 stored as 8); a condition in a read (`qty < 7.5`) isn't refused. A `NaN` or an infinity written to a declared `decimal` column is refused the same way. Without declared types, PostgreSQL stores a `double` `NaN` in a `numeric` column, and it reads back as a `double`.

### Dates and times

| You have | Use | Column |
|---|---|---|
| A moment (an order was placed) | `DateTimeOffset`, or `DateTime` with Kind `Utc` | `timestamptz` |
| A wall-clock time with no zone | `DateTime` with Kind `Unspecified` | `timestamp` |
| A calendar date | `DateOnly` | `date` |
| A time of day | `TimeOnly` | `time` |
| A duration | `TimeSpan` | `interval` |

The same moment written as a `DateTimeOffset` (+08:00), a UTC `DateTime` or a local `DateTime` is stored as the same
instant. A `DateTime` with Kind `Unspecified` written to a `timestamptz` column is read in the session's time zone:
ARX's own connections are UTC (`Timezone=UTC`), so `09:00` is stored as `09:00+00`; a connection string that names
another zone (`Timezone=Asia/Manila`) makes it `01:00+00`. Give a moment Kind `Utc`, or a `DateTimeOffset`, so the
session can't change it. Refused: `default(DateTime)` (`ARX1013`: it means "not set", and PostgreSQL would store `-infinity`; use
`null`), and a date given as a string (`ARX4004`: ARX never parses strings as dates). Full rules:
[Data types](/csharp/postgresql/data-types).

### Selection sets

A selection set (picklist, option set) is a column that accepts only a fixed list of codes, each with a display text.
The **catalog** lists them once, in code (or loaded from JSON, a table or configuration at startup), and the service
checks every `ArxSelectionSetValue` against it in memory, before any SQL is sent: no lookup table, no extra query.

| Part | What it says | Example |
|---|---|---|
| Name | What an `ArxSelectionSetValue` refers to | `"AccountStatus"` |
| Kind | What the codes are, matching the column | `String`, `Integer`, `Character`, `Boolean`, `Decimal`, `Guid`, `DatabaseEnum` (a PostgreSQL enum type) |
| Multiplicity | One code, or several | `Single`, or several stored as `Array`, `Flags`, `Delimited` or `Json` |
| Options | Each code with its display text and order | `("ON_HOLD", "On hold")` |
| Active | A retired option stays readable in old rows, but can't be written | `IsActive: false` |

Refused before the database: a code that isn't in the set, a retired option, several values for a single-choice set
(`ARX1011`), a set the catalog doesn't have (`ARX1012`). A plain value (not an `ArxSelectionSetValue`) isn't checked.
`catalog.TryGetSet(name, out var set)` gives a set's options, for example for a drop-down list.

### Database errors as user messages

| What the user did | Code | Details on the exception |
|---|---|---|
| Reused an account number | `ARX3001 UniqueViolation` | `ConstraintName` |
| Left the name empty | `ARX3003 NotNullViolation` | `AttributeName` |
| Entered a negative employee count | `ARX3004 CheckViolation` | `ConstraintName` |
| Pasted a 220-character website into `varchar(200)` | `ARX4002 ValueTooLong` | – (PostgreSQL doesn't say which column) |
| Entered 3,000,000,000 employees into an `int` | `ARX4001 ValueOutOfRange` | – |
| Typed "forty" into a number | `ARX4004 DataTypeMismatch` | the column, in the message |
| A field the table doesn't have | `ARX5002 UndefinedAttribute` | the column, in the message |
| A table name with SQL in it | `ARX1017 SuspectedInjection`, before the database | the name, in the message |

`ArxDatabaseException` keeps `SqlState` (`23505`) and the `PostgresException` as `InnerException`; `IsTransient` is true
for errors worth retrying (deadlocks, timeouts, dropped connections). All codes:
[the error codes](/csharp/postgresql/error-codes).

### Transactions, batch scopes and services

- **One service per database, a singleton.** It holds no connection; every call takes one from the Npgsql pool. Inject
  `IArxBatchDataService` where code only needs batch scopes.
- **An ARX transaction** (`new ArxPgConnection(cs)`, then `BeginTransactionAsync()`) is for code that decides, step
  by step, whether the work is kept. A failed database call aborts it:

  | After | `IsAborted` | Next call | `CommitAsync()` |
  |---|---|---|---|
  | A database error (constraint, type, connection, cancellation) | `true` | `ARX1015 TransactionAborted` | rolls back, throws `ARX1015` |
  | A validation error (before the database) | `false` | works | commits |

- **A batch scope** gives each call its own savepoint, so a failed batch is undone alone (`BeginBatchAsync` to decide
  yourself, `RunBatchAsync` to commit when the body returns). With `Savepoints = false`, any failure rolls back
  everything. Nothing a scope writes is visible to other connections until it commits.
- **Two databases can't share a transaction.** Write the source of truth first and commit; then write the copy, made
  safe to repeat (a unique key there).
- *Interop only:* `CreateAsync(entity, IDbConnection, IDbTransaction)` accepts an Npgsql connection and transaction, to
  share one with another library; ARX never commits, rolls back or closes them.

### Checking declarations at startup

`ValidateSchemaAsync` compares each declaration with the database and reports every difference at once
(`ARX5003 SchemaMismatch`): the table exists; the id column exists with a compatible type, is generated (a default or
an identity) when declared so, and identifies one row on its own; every key's columns have a primary key or unique
index on exactly those columns. A drifted declaration that reaches a write is `ARX5003` too, never a raw .NET
exception: without a transaction the row is already committed, in an ARX transaction nothing is kept, in a batch scope
the whole scope is rolled back.

### Preloading the schema at service setup

| Choose | Mode | What you get | Trade-off |
|---|---|---|---|
| **Convenience** | `ArxPreLoadMode.KeysOnly` | The table keys, as you would declare them: the id, business keys (unique constraints), lookups (foreign keys) | Values go to the database as they are: a string for an enum or jsonb column fails there |
| **Typed** (the default) | `ArxPreLoadMode.DataTypes` | The keys plus every loaded column's type: a wrong type refused before the database (ARX4004), a string cast for an enum or jsonb column, enum columns read as text from the first read | A little more to load at startup |
| **An allow list** | `DataTypes` + `Strict = true` | Only the preloaded tables and columns can be used; anything else is `ARX1018 NotPreloaded`, before the database | Every column a call uses must be listed; functions and Execute aren't covered |

A table a loaded column references is added with its reason (`"added: referenced by account.primary_contact_id"`).
Display names and selection sets aren't discovered: add them in `LoadAsync(cs, configure => …)`.

## The samples

Every Create sample, in reading order. Each scenario shows the code (verified against PostgreSQL) and the SQL that checks what it did. `service` is the application's `ArxPgDbService`; 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-postgresql-sample-application.md.

### Create one record and get its id

Basics 1 of Create.

The smallest create: one record, in four scenarios. Read them in order: the usual way first (the id column declared in the schema), then the other way (declared on the entity), then two tables with no id column: one identified by a compound key, one with no key at all.

Usings:

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

#### 1. Id column in the schema

The usual way: the id column declared in the schema, the id read from what CreateAsync returns.

In scope: `connectionString` (string).

```csharp
// Declare each table's id column once, at startup. ARX never guesses it.
var schema = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var entity = new ArxEntity("account");
entity["name"] = "Northwind Traders";
entity["account_number"] = "ACC-0001";
entity["employee_count"] = 240;

// CreateAsync returns the id of the new record.
ArxRecordId id = await service.CreateAsync(entity);

// The account's id is a bigint column, so read it as a number.
long accountId = id.AsInt64();

Console.WriteLine($"created account {accountId}");
```

Check it in the database:

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

Expect: 1 row, Northwind Traders, employee_count 240.

#### 2. Id column on the entity

The other way: the id column declared on the entity, the id read from the entity.

In scope: `connectionString` (string).

```csharp
// No schema catalog: the entity says what its id column is, for this record only.
var service = new ArxPgDbService(connectionString);

var entity = new ArxEntity("account");
entity.IdColumn = ArxIdColumn.Generated<long>("id");
entity["name"] = "Contoso Pharmaceuticals";
entity["account_number"] = "ACC-0006";
entity["employee_count"] = 85;

await service.CreateAsync(entity);

// The id is also set on the entity, as entity.RecordId.
Console.WriteLine($"created account {entity.RecordId} (read from entity.RecordId)");
```

Check it in the database:

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

Expect: 1 row, Contoso Pharmaceuticals, employee_count 85.

#### 3. Compound key

A table with no id column, where a compound key (region + sku) identifies each record.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
    .Build();

var service = new ArxPgDbService(connectionString, schema);

// A price's region must be a country that exists, so add the country first.
var italy = new ArxEntity("country");
italy["code"] = "IT";
italy["name"] = "Italy";
await service.CreateAsync(italy);

var price = new ArxEntity("price_list");
price["region"] = "IT";
price["sku"] = "SKU-000001";
price["unit_price"] = 9.50m;
price["valid_from"] = new DateOnly(2026, 1, 1);

// There is no id column, so there is no id to return: ArxRecordId.None. The region and SKU identify the record.
ArxRecordId id = await service.CreateAsync(price);

Console.WriteLine($"created price region {price["region"]}, sku {price["sku"]} (id {id})");
```

Check it in the database:

```sql
SELECT p.region, c.name AS country, p.sku, p.unit_price, p.valid_from
FROM price_list p
JOIN country c ON c.code = p.region
WHERE p.region = 'IT' AND p.sku = 'SKU-000001';
```

Expect: 1 row, Italy, unit_price 9.50.

#### 4. No key

A table with no id column and no key: rows may repeat, like a log.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("activity_log", ArxIdColumn.NoIdColumn)
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var entry = new ArxEntity("activity_log");
entry["action"] = "signed in";

ArxRecordId id = await service.CreateAsync(entry);

Console.WriteLine($"logged \"{entry["action"]}\" (id {id})");
```

Check it in the database:

```sql
SELECT action, logged_at
FROM activity_log
WHERE action = 'signed in';
```

Expect: 1 row (the table has no id column).

### Create a record that points at another one

Basics 2 of Create.

A record that points at another one (a lookup), in four scenarios: by the referenced entity's RecordId, by the id CreateAsync returned, to a record whose id is text, and what happens when the lookup is wrong.

Usings:

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

#### 1. By the entity's RecordId

The usual way: point at the account by the RecordId set on it when it was created.

In scope: `connectionString` (string).

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

var account = new ArxEntity("account");
account["name"] = "Adventure Works";
await service.CreateAsync(account);

var contact = new ArxEntity("contact");
contact["first_name"] = "Ana";
contact["last_name"] = "Silva";
contact["email"] = "ana.silva@adventure-works.example";
contact["account_id"] = new ArxEntityReference("account", account.RecordId);   // the table, then the id

await service.CreateAsync(contact);

Console.WriteLine($"created contact {contact.RecordId} for account {account.RecordId}");
```

Defined in the sample, used above:

```csharp
private static ArxPgDbService Service(string connectionString) => new ArxPgDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Table("contact", ArxIdColumn.Generated<long>("id"))
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
    .Build());
```

Check it in the database:

```sql
SELECT c.id, c.first_name, c.last_name, c.account_id, a.name AS account
FROM contact c
JOIN account a ON a.id = c.account_id
WHERE c.email = 'ana.silva@adventure-works.example';
```

Expect: 1 row, Ana Silva, account Adventure Works.

#### 2. By the id Create returned

The other way: keep the id CreateAsync returned, and point at the account with it.

In scope: `connectionString` (string).

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

var account = new ArxEntity("account");
account["name"] = "Proseware Labs";

ArxRecordId accountId = await service.CreateAsync(account);

var contact = new ArxEntity("contact");
contact["first_name"] = "Ben";
contact["last_name"] = "Okafor";
contact["email"] = "ben.okafor@proseware-labs.example";
contact["account_id"] = new ArxEntityReference("account", accountId);    // an ArxRecordId works for any id type

await service.CreateAsync(contact);

Console.WriteLine($"created contact {contact.RecordId} for account {accountId}");
```

Check it in the database:

```sql
SELECT c.id, c.first_name, c.last_name, c.account_id, a.name AS account
FROM contact c
JOIN account a ON a.id = c.account_id
WHERE c.email = 'ben.okafor@proseware-labs.example';
```

Expect: 1 row, Ben Okafor, account Proseware Labs.

#### 3. To a text id

A record whose id is text: a price points at its country by the country code.

In scope: `connectionString` (string).

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

var belgium = new ArxEntity("country");
belgium["code"] = "BE";
belgium["name"] = "Belgium";
await service.CreateAsync(belgium);

var price = new ArxEntity("price_list");
price["region"] = new ArxEntityReference("country", "BE");               // a text id
price["sku"] = "SKU-000001";
price["unit_price"] = 2.40m;
price["valid_from"] = new DateOnly(2026, 1, 1);

await service.CreateAsync(price);

Console.WriteLine("created a price for country BE");
```

Check it in the database:

```sql
SELECT p.region, c.name AS country, p.sku, p.unit_price
FROM price_list p
JOIN country c ON c.code = p.region
WHERE p.region = 'BE';
```

Expect: 1 row, Belgium, SKU-000001, unit_price 2.40.

#### 4. A record that isn't there

When the lookup is wrong: a record that doesn't exist is refused by the database's foreign key (ARX3002); a table the schema doesn't declare is refused by ARX before anything is sent (ARX1005).

In scope: `connectionString` (string).

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

var noSuchAccount = new ArxEntity("contact");
noSuchAccount["first_name"] = "Nobody";
noSuchAccount["last_name"] = "Here";
noSuchAccount["account_id"] = new ArxEntityReference("account", 999_999L);

try
{
    await service.CreateAsync(noSuchAccount);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.ForeignKeyViolation)
{
    Console.WriteLine($"{error.Code} (the database's foreign key)");
}

var typo = new ArxEntity("contact");
typo["first_name"] = "Typo";
typo["last_name"] = "Table";
typo["account_id"] = new ArxEntityReference("acount", 1L);              // "acount": not a declared table

try
{
    await service.CreateAsync(typo);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidLookup)
{
    Console.WriteLine($"A table the schema lacks: {error.Code}, before anything was sent: {error.Message}");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE first_name IN ('Nobody', 'Typo');
```

Expect: 0: both contacts were refused.

### Catch an error by its code

Basics 3 of Create.

Catching errors, in three scenarios: a database error by its code, several database errors by their category, and a mistake ARX refuses before anything is sent to the database.

Usings:

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

#### 1. By its code

The usual way: the database refuses a record, and the ARX code says why. Here, a duplicate account number.

In scope: `connectionString` (string).

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

var original = new ArxEntity("account");
original["name"] = "Tailspin Toys";
original["account_number"] = "ACC-0005";
await service.CreateAsync(original);

// Account numbers are unique, so the database refuses this one.
var duplicate = new ArxEntity("account");
duplicate["name"] = "Tailspin Toys (copy)";
duplicate["account_number"] = "ACC-0005";

try
{
    await service.CreateAsync(duplicate);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
{
    Console.WriteLine($"not created: {error.Message}");
}
```

Defined in the sample, used above:

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

Check it in the database:

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

Expect: 1 row, Tailspin Toys: the copy was refused.

#### 2. By its category

The other way: catch by category when several codes need the same handling. Every constraint the database enforces (unique, not null, check, foreign key) is the Constraint category.

In scope: `connectionString` (string).

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

var numberTaken = new ArxEntity("account");
numberTaken["name"] = "Tailspin Toys (another copy)";
numberTaken["account_number"] = "ACC-0005";

var noName = new ArxEntity("account");
noName["account_number"] = "ACC-0013";   // name is required (NOT NULL)

foreach (var account in new[] { numberTaken, noName })
{
    try
    {
        await service.CreateAsync(account);
    }
    catch (ArxDatabaseException error) when (error.Category == ArxErrorCategory.Constraint)
    {
        Console.WriteLine($"{error.Category}: {error.Code}");
    }
}
```

Check it in the database:

```sql
SELECT account_number, count(*)
FROM account
WHERE account_number IN ('ACC-0005', 'ACC-0013')
GROUP BY account_number;
```

Expect: only ACC-0005, count 1: neither refused account was stored.

#### 3. Before the database

A mistake in the request itself is refused by ARX before anything is sent: an ArxValidationException, not an ArxDatabaseException. Here, a country without its code, which the schema declares as supplied.

In scope: `connectionString` (string).

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

var noCode = new ArxEntity("country");
noCode["name"] = "Narnia";

try
{
    await service.CreateAsync(noCode);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidArgument)
{
    Console.WriteLine($"{error.Code}: {error.Message}");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM country
WHERE name = 'Narnia';
```

Expect: 0: refused before anything was sent.

### Ids that aren't numbers: text and uuid

Basics 4 of Create.

Ids that aren't numbers, in three scenarios: a text id you supply (a country code), a uuid the database generates, and a uuid your code generates.

Usings:

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

#### 1. Text id you supply

A text id you supply: the country code. Supplied means you always set it.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var country = new ArxEntity("country");
country["code"] = "PT";
country["name"] = "Portugal";

ArxRecordId id = await service.CreateAsync(country);

// A text id is read with AsString().
Console.WriteLine($"created country {id.AsString()}");
```

Check it in the database:

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

Expect: 1 row, Portugal.

#### 2. Uuid the database makes

A uuid the database generates (DEFAULT gen_random_uuid()): leave the id out.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("device", ArxIdColumn.Generated<Guid>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var device = new ArxEntity("device");
device["serial_number"] = "DEV-0001";

ArxRecordId id = await service.CreateAsync(device);

// A uuid is read with AsGuid().
Console.WriteLine($"created device {id.AsGuid()}");
```

Check it in the database:

```sql
SELECT id, serial_number
FROM device
WHERE serial_number = 'DEV-0001';
```

Expect: 1 row, with the uuid the database generated.

#### 3. Uuid your code makes

A uuid your code generates: set the id, and it's written as given.

In scope: `connectionString` (string).

```csharp
// The same declaration as scenario 2: Generated means the database CAN fill it in, not that it must.
var schema = ArxSchemaCatalog.Create()
    .Table("device", ArxIdColumn.Generated<Guid>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var myId = Guid.NewGuid();

var device = new ArxEntity("device");
device["id"] = myId;
device["serial_number"] = "DEV-0002";

ArxRecordId id = await service.CreateAsync(device);

Console.WriteLine($"created device {id.AsGuid()} (the same as myId: {id.AsGuid() == myId})");
```

Check it in the database:

```sql
SELECT id, serial_number
FROM device
WHERE serial_number = 'DEV-0002';
```

Expect: 1 row, with the uuid the sample printed (myId).

### Where to declare: on the entity, or a schema for one call, never both

Basics 5 of Create.

Where a table's id column and keys are declared, in three scenarios: on the entity (for a service with no schema), in a schema passed to one call (which overrides the service's schema for that call), and the rule that a table is declared in one place only.

Usings:

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

#### 1. On the entity

A service with no schema: each entity says its id column (none for the price) and its key.

In scope: `connectionString` (string).

```csharp
// No schema catalog: the country and the price below declare themselves.
var service = new ArxPgDbService(connectionString);

// A price's region must be a country that exists, so add the country first.
var austria = new ArxEntity("country");
austria.IdColumn = ArxIdColumn.Supplied<string>("code");
austria["code"] = "AT";
austria["name"] = "Austria";
await service.CreateAsync(austria);

var price = new ArxEntity("price_list");
price.IdColumn = ArxIdColumn.NoIdColumn;
price.Keys = new[] { new ArxKey("region", "sku") };
price["region"] = new ArxEntityReference("country", "AT");   // a lookup to the country, by its code
price["sku"] = "SKU-000042";
price["unit_price"] = 4.90m;
price["valid_from"] = new DateOnly(2026, 1, 1);

await service.CreateAsync(price);

Console.WriteLine($"created price region {austria["code"]}, sku {price["sku"]}");
```

Check it in the database:

```sql
SELECT p.region, c.name AS country, p.sku, p.unit_price
FROM price_list p
JOIN country c ON c.code = p.region
WHERE p.region = 'AT';
```

Expect: 1 row, Austria, SKU-000042, unit_price 4.90.

#### 2. A schema for one call

The service's schema says the database can generate a device's uuid. This import wants every device to bring its own, so it passes a schema for this one call that declares the id Supplied: it overrides the service's declaration of that table, and ARX then refuses a device without an id.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("device", ArxIdColumn.Generated<Guid>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

// Only for this import: the same table, declared stricter.
var importSchema = ArxSchemaCatalog.Create()
    .Table("device", ArxIdColumn.Supplied<Guid>("id"))
    .Build();

var noId = new ArxEntity("device");
noId["serial_number"] = "IMPORT-0001";

try
{
    await service.CreateMultipleAsync(new List<ArxEntity> { noId }, importSchema);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidArgument)
{
    Console.WriteLine($"refused before anything was sent ({error.AttributeName} is required)");
}

var withId = new ArxEntity("device");
withId["id"] = Guid.NewGuid();
withId["serial_number"] = "IMPORT-0002";

var ids = await service.CreateMultipleAsync(new List<ArxEntity> { withId }, importSchema);

Console.WriteLine($"created device {ids[0]}");
```

Check it in the database:

```sql
SELECT id, serial_number
FROM device
WHERE serial_number IN ('IMPORT-0001', 'IMPORT-0002');
```

Expect: 1 row, IMPORT-0002: IMPORT-0001 had no id and was refused.

#### 3. One place only

One place only: when there is a schema (the service's, or one passed to the call), an entity that also declares its own id column or keys is refused, so a table is never declared twice.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("device", ArxIdColumn.Generated<Guid>("id"))
    .Build();

var service = new ArxPgDbService(connectionString, schema);

var device = new ArxEntity("device");
device.IdColumn = ArxIdColumn.Generated<Guid>("id");   // declared again on the entity: not allowed
device["serial_number"] = "TWICE-0001";

try
{
    await service.CreateAsync(device);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidArgument)
{
    Console.WriteLine($"refused ({error.Code}): the service has a schema, so the entity can't declare its own");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM device
WHERE serial_number = 'TWICE-0001';
```

Expect: 0: refused before anything was sent.

### Create without a catalog

Basics 6 of Create.

A service created with only a connection string, in four scenarios: ARX reads the table's id column and column types from the database before the create, for a generated id and for a supplied text id; then the two tables it can't resolve an id for, one with no primary key and one with a compound primary key, refused with ARX1019, and the one line that fixes each.

Good to know:

Without a catalog, every call first reads the tables it writes from the database: one more round trip per call, so it is much slower than a service with a catalog. It is meant for simple CRUD in small applications and scripts; for anything more, declare the catalog or preload it (Create Advanced6), or remember what was read (Create Advanced10).

ARX still never guesses: it uses the primary key the table declares. A table with no primary key, a compound one, or one of a type an id can't be is ARX1019 TableNotResolvable when the call needs the id, with the reason and the fix in the message.

A service created with a catalog never resolves: a table missing from it is still ARX1016 IdNotDeclared.

Usings:

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

Set up by the sample before its scenarios:

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

#### 1. A generated id, read from the database

The usual case: account's primary key is a bigserial, so ARX knows the id is generated and returns it.

In scope: `service` (ArxPgDbService).

```csharp
var entity = new ArxEntity("account");
entity["name"] = "Adatum Corporation";
entity["account_number"] = "ACC-N001";
entity["employee_count"] = 120;

ArxRecordId id = await service.CreateAsync(entity);

Console.WriteLine($"created account {id.AsInt64()} (id column \"{id.Column}\")");
```

Check it in the database:

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

Expect: 1 row, Adatum Corporation, employee_count 120.

#### 2. A supplied text id

country's primary key is a char(2) with no default, so the id is supplied: set it like any attribute.

In scope: `service` (ArxPgDbService).

```csharp
var norway = new ArxEntity("country");
norway["code"] = "NO";
norway["name"] = "Norway";

ArxRecordId id = await service.CreateAsync(norway);

Console.WriteLine($"created country {id.AsString()}");
```

Check it in the database:

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

Expect: 1 row, Norway.

#### 3. A table with no primary key

activity_log has no primary key: ARX1019, and the fix, declaring that on the entity.

In scope: `service` (ArxPgDbService).

```csharp
var entry = new ArxEntity("activity_log");
entry["action"] = "exported the price list";

try
{
    await service.CreateAsync(entry);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.TableNotResolvable)
{
    Console.WriteLine($"refused: {error.Message}");
}

// Say so on the entity (or in a catalog): the row is written and there is no id to return.
entry.IdColumn = ArxIdColumn.NoIdColumn;
ArxRecordId none = await service.CreateAsync(entry);

Console.WriteLine($"logged \"{entry["action"]}\" (id {none})");
```

Check it in the database:

```sql
SELECT action, logged_at
FROM activity_log
WHERE action = 'exported the price list';
```

Expect: 1 row (the refused call wrote nothing).

#### 4. A compound primary key

price_list's primary key is two columns (region, sku): no single id, so ARX1019 too, with that reason.

In scope: `service` (ArxPgDbService).

```csharp
var price = new ArxEntity("price_list");
price["region"] = "NO";
price["sku"] = "SKU-N00001";
price["unit_price"] = 12.00m;
price["valid_from"] = new DateOnly(2026, 1, 1);

try
{
    await service.CreateAsync(price);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.TableNotResolvable)
{
    Console.WriteLine($"refused: {error.Message}");
}

// The region and SKU identify the row: declare no id column, and the two as its key.
price.IdColumn = ArxIdColumn.NoIdColumn;
price.Keys = new[] { new ArxKey("region", "sku") };
await service.CreateAsync(price);

Console.WriteLine($"created price region {price["region"]}, sku {price["sku"]}");
```

Check it in the database:

```sql
SELECT region, sku, unit_price
FROM price_list
WHERE sku = 'SKU-N00001';
```

Expect: 1 row, NO, unit_price 12.00.

### A sign-up form creates an account

Real case 1 of Create.

A sign-up form creates a customer account, in three scenarios: the account from the form (columns left out get their database defaults), a subsidiary that points at its parent, and form fields the table doesn't have (ArxIgnore).

Usings:

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

#### 1. Create the account from the form

The account from the sign-up form. guid, status and created_on aren't set: the database fills them in.

In scope: `service` (ArxPgDbService).

```csharp
var account = new ArxEntity("account");
account["name"] = "Northwind Traders";
account["account_number"] = "ACC-10001";
account["industry"] = new ArxSelectionSetValue("Industry", Industry.Retail);
account["website"] = "https://northwind.example";
account["annual_revenue"] = 12_500_000.00m;
account["employee_count"] = 240;
account["is_key_account"] = true;
account["founded_on"] = new DateOnly(1998, 4, 1);
account["modified_on"] = DateTime.UtcNow;

var id = await service.CreateAsync(account);
Console.WriteLine($"account {id} (also on account.RecordId)");

return account;
```

Check it in the database:

```sql
SELECT id, name, account_number, industry, website, annual_revenue, guid, status, created_on
FROM account
WHERE account_number = 'ACC-10001';
```

Expect: 1 row; guid, status and created_on are the database defaults.

#### 2. Add a subsidiary that points at its parent

A subsidiary account points at its parent with a lookup.

In scope: `service` (ArxPgDbService), `parent` (ArxEntity).

```csharp
var subsidiary = new ArxEntity("account");
subsidiary["name"] = "Northwind Traders (Canada)";
subsidiary["account_number"] = "ACC-10002";
subsidiary["parent_account_id"] = new ArxEntityReference("account", parent.RecordId);

var id = await service.CreateAsync(subsidiary);
```

Check it in the database:

```sql
SELECT s.name, s.account_number, s.parent_account_id, p.name AS parent
FROM account s
JOIN account p ON p.id = s.parent_account_id
WHERE s.account_number = 'ACC-10002';
```

Expect: 1 row, Northwind Traders (Canada), parent Northwind Traders.

#### 3. Form fields the table doesn't have are marked ArxIgnore

A form field the table doesn't have stays on the entity, marked ArxIgnore, and isn't written.

In scope: `service` (ArxPgDbService).

```csharp
var fromForm = new ArxEntity("account");
fromForm["name"] = "Contoso Pharmaceuticals";
fromForm["industry"] = new ArxSelectionSetValue("Industry", Industry.Healthcare);
fromForm["display_label"] = new ArxIgnore();

// The id column "id" is declared Generated, and the form doesn't set it, so the database fills it in. An id you do
// set is written as given, so leave the id attribute out unless you mean it.
var id = await service.CreateAsync(fromForm);
Console.WriteLine($"account {id}: display_label wasn't sent, and the database chose the id");
```

Check it in the database:

```sql
SELECT id, name, industry
FROM account
WHERE name = 'Contoso Pharmaceuticals' AND account_number IS NULL;
```

Expect: 1 row, industry 3 (Healthcare); there is no display_label column.

### Every supported .NET type, and what PostgreSQL stores

Real case 2 of Create.

Every kind of value the PostgreSQL provider writes, into data_type_showcase, and what PostgreSQL stored, in four scenarios: every supported type, types converted to one that holds every value, null, and a type that's refused. The comment on each line is the stored value as PostgreSQL prints it.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var owner = new ArxEntity("account");
owner["name"] = "Showcase owner";
var ownerId = await service.CreateAsync(owner);
```

#### 1. One row with a value of every supported kind

One row with a value of every supported kind.

In scope: `service` (ArxPgDbService), `ownerId` (ArxRecordId).

```csharp
var row = new ArxEntity("data_type_showcase");
row["text_value"] = "Hello, ARX";                                                        // Hello, ARX
row["varchar_value"] = "Short text";                                                     // Short text
row["char_value"] = "AB";                                                                // AB (char(3) pads to "AB ")
row["smallint_value"] = (short)32_000;                                                   // 32000
row["int_value"] = 2_000_000_000;                                                        // 2000000000
row["bigint_value"] = 9_000_000_000_000L;                                                // 9000000000000
row["numeric_value"] = 12_345.678901m;                                                   // 12345.678901
row["real_value"] = 3.25f;                                                               // 3.25
row["double_value"] = 2.718281828459045;                                                 // 2.718281828459045
row["bool_value"] = true;                                                                // true
row["uuid_value"] = Guid.Parse("6f9619ff-8b86-d011-b42d-00c04fc964ff");                  // 6f9619ff-8b86-d011-b42d-00c04fc964ff
row["timestamptz_value"] = new DateTime(2026, 11, 7, 14, 30, 0, DateTimeKind.Utc);       // 2026-11-07 14:30:00+00
row["timestamp_value"] = new DateTime(2026, 11, 7, 9, 0, 0, DateTimeKind.Unspecified);   // 2026-11-07 09:00:00
row["date_value"] = new DateOnly(2026, 11, 7);                                           // 2026-11-07
row["time_value"] = new TimeOnly(17, 45, 30);                                            // 17:45:30
row["interval_value"] = TimeSpan.FromHours(36.5);                                        // 1 day 12:30:00
row["bytea_value"] = new byte[] { 0xCA, 0xFE };                                          // \xcafe
row["jsonb_value"] = new ArxJsonB { SerializedValue = "{\"plan\": \"gold\", \"seats\": 25}" };  // {"plan": "gold", "seats": 25}
row["text_array"] = new[] { "red", "green" };                                            // {red,green}
row["int_array"] = new[] { 1, 2, 3 };                                                    // {1,2,3}
row["numeric_array"] = new[] { 1.5m, 2.25m };                                            // {1.5,2.25}
row["uuid_array"] = new[] { Guid.Parse("00000000-0000-0000-0000-000000000001") };        // {00000000-0000-0000-0000-000000000001}
row["date_array"] = new[] { new DateOnly(2026, 1, 1), new DateOnly(2026, 12, 31) };      // {2026-01-01,2026-12-31}
row["timestamptz_array"] = new[] { new DateTime(2026, 1, 1, 0, 0, 0, DateTimeKind.Utc) }; // {"2026-01-01 00:00:00+00"}
row["mood"] = new ArxSelectionSetValue("Mood", "happy");                                 // happy (a PostgreSQL enum)
row["account_id"] = new ArxEntityReference("account", ownerId);                          // the account's id
row["industry"] = new ArxSelectionSetValue("Industry", Industry.Technology);             // 5

await service.CreateAsync(row);
```

Check it in the database:

```sql
SELECT text_value, char_value, smallint_value, int_value, bigint_value, numeric_value, real_value, double_value,
       bool_value, uuid_value, timestamptz_value, timestamp_value, date_value, time_value, interval_value,
       bytea_value, jsonb_value, text_array, int_array, numeric_array, uuid_array, date_array, mood, industry
FROM data_type_showcase
WHERE text_value = 'Hello, ARX';
```

Expect: 1 row with the values in the comments next to each attribute.

#### 2. Types without a PostgreSQL type of their own are converted to one that holds every value

Types without a PostgreSQL type of their own are converted to one that holds every value.

In scope: `service` (ArxPgDbService).

```csharp
var converted = new ArxEntity("data_type_showcase");
converted["smallint_value"] = (byte)200;                                                 // 200 (byte, sbyte → smallint)
converted["int_value"] = (ushort)65_000;                                                 // 65000 (ushort → int)
converted["bigint_value"] = 4_000_000_000u;                                              // 4000000000 (uint → bigint)
converted["numeric_value"] = ulong.MaxValue;                                             // 18446744073709551615.000000 (ulong → numeric)
converted["char_value"] = 'X';                                                           // X (char → text)
converted["text_value"] = Industry.Finance.ToString();                                   // Finance (an enum: its name, or (int) for its number)
converted["timestamptz_value"] = new DateTimeOffset(2026, 11, 7, 22, 30, 0, TimeSpan.FromHours(8)); // 2026-11-07 14:30:00+00
converted["bytea_value"] = new ArxBytea { Data = new byte[] { 1, 2 } };                  // \x0102

await service.CreateAsync(converted);
```

Check it in the database:

```sql
SELECT smallint_value, int_value, bigint_value, numeric_value, char_value, text_value, timestamptz_value, bytea_value
FROM data_type_showcase
WHERE text_value = 'Finance';
```

Expect: 1 row: 200, 65000, 4000000000, 18446744073709551615.000000, X, Finance, 2026-11-07 14:30:00+00, \x0102.

#### 3. null is stored as NULL

null is stored as NULL, whatever the column's type.

In scope: `service` (ArxPgDbService).

```csharp
var empty = new ArxEntity("data_type_showcase");
empty["text_value"] = null;
empty["int_value"] = null;
empty["jsonb_value"] = null;

await service.CreateAsync(empty);
```

Check it in the database:

```sql
SELECT id, text_value, int_value, jsonb_value
FROM data_type_showcase
WHERE text_value IS NULL AND int_value IS NULL AND jsonb_value IS NULL
  AND time_value IS NULL AND mood IS NULL AND uuid_value IS NULL;
```

Expect: 1 row, all NULL.

#### 4. A type the provider can't write is refused before anything is sent

A type the provider can't write is refused before anything is sent.

In scope: `service` (ArxPgDbService).

```csharp
var unsupported = new ArxEntity("data_type_showcase");
unsupported["text_value"] = new Uri("https://northwind.example");

try
{
    await service.CreateAsync(unsupported);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.UnsupportedValueType)
{
    Console.WriteLine(error.Message);
    Console.WriteLine("write uri.ToString() instead");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM data_type_showcase
WHERE text_value LIKE 'https://northwind%';
```

Expect: 0: the Uri was refused before anything was sent.

### Instants, local times, dates and times of day

Real case 3 of Create.

Dates and times, in four scenarios: an order placed in Manila written three ways (instants are stored as UTC), calendar values (a birthday, an opening time, a delivery window, a local event time), and what ARX refuses: default(DateTime) and a date given as a string.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Tailspin Toys";
var accountId = await service.CreateAsync(account);
```

#### 1. The same instant written three ways into sales_order.ordered_on (timestamptz)

An instant goes into timestamptz. These three orders were placed at the same moment, written three ways.

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

```csharp
var fromOffset = new ArxEntity("sales_order");
fromOffset["order_number"] = "SO-1001";
fromOffset["account_id"] = new ArxEntityReference("account", accountId);
fromOffset["ordered_on"] = new DateTimeOffset(2026, 11, 7, 18, 0, 0, TimeSpan.FromHours(8));            // Manila time

var fromUtc = new ArxEntity("sales_order");
fromUtc["order_number"] = "SO-1002";
fromUtc["account_id"] = new ArxEntityReference("account", accountId);
fromUtc["ordered_on"] = new DateTime(2026, 11, 7, 10, 0, 0, DateTimeKind.Utc);

var fromLocal = new ArxEntity("sales_order");
fromLocal["order_number"] = "SO-1003";
fromLocal["account_id"] = new ArxEntityReference("account", accountId);
fromLocal["ordered_on"] = new DateTime(2026, 11, 7, 10, 0, 0, DateTimeKind.Utc).ToLocalTime();          // ARX converts Local to UTC

var orders = new List<ArxEntity> { fromOffset, fromUtc, fromLocal };
var ids = await service.CreateMultipleAsync(orders);
```

Check it in the database:

```sql
SET TIME ZONE 'UTC';
SELECT order_number, ordered_on
FROM sales_order
WHERE order_number IN ('SO-1001', 'SO-1002', 'SO-1003')
ORDER BY order_number;
```

Expect: 3 rows, all 2026-11-07 10:00:00+00.

#### 2. A birthday, an opening time, a delivery window and a local event time

Calendar values have their own types. A wall-clock time with no zone (an Unspecified DateTime) goes into timestamp.

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

```csharp
var contact = new ArxEntity("contact");
contact["account_id"] = new ArxEntityReference("account", accountId);
contact["first_name"] = "Maria";
contact["last_name"] = "Santos";
contact["birthday"] = new DateOnly(1990, 2, 28);
var contactId = await service.CreateAsync(contact);

var schedule = new ArxEntity("data_type_showcase");
schedule["time_value"] = new TimeOnly(9, 0);
schedule["interval_value"] = TimeSpan.FromMinutes(90);
schedule["timestamp_value"] = new DateTime(2026, 12, 24, 18, 0, 0, DateTimeKind.Unspecified);
var scheduleId = await service.CreateAsync(schedule);
```

Check it in the database:

```sql
SELECT first_name, last_name, birthday
FROM contact
WHERE first_name = 'Maria' AND last_name = 'Santos';
SELECT time_value, interval_value, timestamp_value
FROM data_type_showcase
WHERE timestamp_value = '2026-12-24 18:00:00';
```

Expect: birthday 1990-02-28; 09:00:00, 01:30:00, 2026-12-24 18:00:00.

#### 3. default(DateTime) is refused: use null for "no value"

default(DateTime) means "not set", and PostgreSQL would store -infinity: refused before anything is sent.

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

```csharp
var unset = new ArxEntity("sales_order");
unset["order_number"] = "SO-1004";
unset["account_id"] = new ArxEntityReference("account", accountId);
unset["ordered_on"] = default(DateTime);

try
{
    await service.CreateAsync(unset);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidDateTimeValue)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM sales_order
WHERE order_number = 'SO-1004';
```

Expect: 0: default(DateTime) was refused before anything was sent.

#### 4. A date given as a string is refused; DateOnly.ParseExact works

Strings are never parsed as dates. Parse them yourself, with the format you mean.

In scope: `service` (ArxPgDbService).

```csharp
var fromCsv = new ArxEntity("contact");
fromCsv["first_name"] = "Leo";
fromCsv["last_name"] = "Park";
fromCsv["birthday"] = "1992-07-01";

try
{
    await service.CreateAsync(fromCsv);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.DataTypeMismatch)
{
    Console.WriteLine(error.Message);
}

fromCsv["birthday"] = DateOnly.ParseExact("1992-07-01", "yyyy-MM-dd");
var id = await service.CreateAsync(fromCsv);
Console.WriteLine($"stored contact {id}");
```

Check it in the database:

```sql
SELECT first_name, last_name, birthday
FROM contact
WHERE first_name = 'Leo' AND last_name = 'Park';
```

Expect: 1 row, birthday 1992-07-01 (the second attempt, with DateOnly.ParseExact).

### Picklist values checked against the application's catalog

Real case 4 of Create.

Selection sets (picklists, option sets), in seven scenarios: the catalog, a catalog without the sets, an account's industry, status and segments, a contact's preferred channel and a survey mood, sets loaded from JSON, values the catalog refuses, and a plain string that skips the catalog.

Good to know:

**The catalog** is the list of every selection set your application has: for each set, its name, what kind of code it uses (a number, a string, a character, …), whether one or several codes can be chosen, and its options (code + display text, and whether the option is still active). You build it once at startup, in code, and give it to the service: `new ArxPgDbService(connectionString, catalog)`. This sample's catalog is in SampleCatalog.cs.

When you write an `ArxSelectionSetValue`, ARX looks up its set in the catalog, checks the code, and writes the code itself into the column. The check happens in memory, before any SQL is sent: no lookup table and no extra query. The database never sees a code your catalog doesn't allow.

Usings:

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

#### 1. The application's catalog: one entry per selection set

The catalog, built once at startup (SampleCatalog.Build()) and passed to the service: what it holds.

In scope: `catalog` (ArxSchemaCatalog).

```csharp
foreach (var set in catalog.SelectionSets.Sets)
{
    var options = string.Join(", ", set.Options.Select(x => $"{x.Value} = {x.DisplayText}{(x.IsActive ? "" : " (retired)")}"));
    Console.WriteLine($"{set.Name,-16} {set.Kind,-12} {set.Multiplicity,-7} {options}");
}

// A set can be looked up by name, for example to fill a drop-down list in your UI.
if (catalog.TryGetSet("AccountStatus", out var statusSet))
{
    Console.WriteLine($"drop-down for AccountStatus: {string.Join(" | ", statusSet.Options.Where(x => x.IsActive).Select(x => x.DisplayText))}");
}
```

Check it in the database:

```sql
-- Nothing is stored: the catalog lives in memory (SampleCatalog.cs). The columns its sets write to:
SELECT table_name, column_name, data_type, udt_name
FROM information_schema.columns
WHERE (table_name, column_name) IN (('account', 'industry'), ('account', 'status'), ('account', 'segments'),
      ('contact', 'preferred_channel'), ('contact', 'lead_source'), ('contact', 'rating'), ('data_type_showcase', 'mood'))
ORDER BY table_name, column_name;
```

Expect: 7 rows.

#### 2. Without the selection sets in the catalog, a selection set value is refused

A service whose catalog declares the tables but no selection sets can't write selection set values: there is nothing to check them against.

In scope: `connectionString` (string).

```csharp
var tablesOnly = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();
var noCatalog = new ArxPgDbService(connectionString, tablesOnly);

var notChecked = new ArxEntity("account");
notChecked["name"] = "No catalog Ltd.";
notChecked["industry"] = new ArxSelectionSetValue("Industry", Industry.Retail);

try
{
    await noCatalog.CreateAsync(notChecked);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.UnknownSelectionSet)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name = 'No catalog Ltd.';
```

Expect: 0: refused, the service had no selection sets to check against.

#### 3. Industry (an enum), status (a string) and segments (several strings)

An account's industry (an enum), status (a string) and segments (several strings).

In scope: `service` (ArxPgDbService).

```csharp
var account = new ArxEntity("account");
account["name"] = "Adventure Works";
account["industry"] = new ArxSelectionSetValue("Industry", Industry.Manufacturing);
account["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");
account["segments"] = ArxSelectionSetValue.Multiple("CustomerSegment", "ENTERPRISE", "PUBLIC");
var id = await service.CreateAsync(account);

return id;
```

Check it in the database:

```sql
SELECT name, industry, status, segments
FROM account
WHERE name = 'Adventure Works' AND status = 'ON_HOLD';
```

Expect: 1 row: industry 2, status ON_HOLD, segments {ENTERPRISE,PUBLIC}.

#### 4. A preferred channel (a character) and a mood (a PostgreSQL enum)

A contact's preferred channel (a character) and a survey's mood (a PostgreSQL enum).

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

```csharp
var contact = new ArxEntity("contact");
contact["account_id"] = new ArxEntityReference("account", accountId);
contact["first_name"] = "Kenji";
contact["last_name"] = "Mori";
contact["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", 'S');
var contactId = await service.CreateAsync(contact);

var survey = new ArxEntity("data_type_showcase");
survey["mood"] = new ArxSelectionSetValue("Mood", "neutral");
var surveyId = await service.CreateAsync(survey);
```

Check it in the database:

```sql
SELECT first_name, last_name, preferred_channel
FROM contact
WHERE first_name = 'Kenji' AND last_name = 'Mori';
SELECT mood
FROM data_type_showcase
WHERE mood = 'neutral';
```

Expect: preferred_channel S; mood neutral.

#### 5. A lead source and a rating, from the sets the catalog loaded from JSON

Sets loaded from JSON (SampleCatalog.SetsJson) are used exactly like the ones written in code.

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

```csharp
var lead = new ArxEntity("contact");
lead["account_id"] = new ArxEntityReference("account", accountId);
lead["first_name"] = "Sofia";
lead["last_name"] = "Rossi";
lead["lead_source"] = new ArxSelectionSetValue("LeadSource", "EVENT");
lead["rating"] = new ArxSelectionSetValue("ContactRating", 4);
var leadId = await service.CreateAsync(lead);

var coldCall = new ArxEntity("contact");
coldCall["first_name"] = "Max";
coldCall["last_name"] = "Weber";
coldCall["lead_source"] = new ArxSelectionSetValue("LeadSource", "COLD_CALL");   // "active": false in the JSON

try
{
    await service.CreateAsync(coldCall);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SelectionSetValueNotAllowed)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT first_name, last_name, lead_source, rating
FROM contact
WHERE (first_name, last_name) IN (('Sofia', 'Rossi'), ('Max', 'Weber'));
```

Expect: 1 row, Sofia Rossi, EVENT, 4: Max Weber's retired lead source was refused.

#### 6. Values the catalog refuses

Values the catalog refuses: not an option, a retired option, two codes for a single choice, an unknown set.

In scope: `service` (ArxPgDbService).

```csharp
var notAnOption = new ArxEntity("account");
notAnOption["name"] = "Bad status";
notAnOption["status"] = new ArxSelectionSetValue("AccountStatus", "DELETED");

var retiredOption = new ArxEntity("account");
retiredOption["name"] = "Retired segment";
retiredOption["segments"] = ArxSelectionSetValue.Multiple("CustomerSegment", "SMB", "RESELLER");

var twoForOne = new ArxEntity("account");
twoForOne["name"] = "Two industries";
twoForOne["industry"] = ArxSelectionSetValue.Multiple("Industry", Industry.Retail, Industry.Finance);

var unknownSet = new ArxEntity("account");
unknownSet["name"] = "Typo";
unknownSet["industry"] = new ArxSelectionSetValue("Industries", Industry.Retail);

foreach (var refused in new[] { notAnOption, retiredOption, twoForOne, unknownSet })
{
    try
    {
        await service.CreateAsync(refused);
    }
    catch (ArxValidationException error)
    {
        Console.WriteLine(error.Message);
    }
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name IN ('Bad status', 'Retired segment', 'Two industries', 'Typo');
```

Expect: 0: all four were refused before anything was sent.

#### 7. A plain string skips the catalog

A plain value (not an ArxSelectionSetValue) isn't checked against the catalog; only the table's own constraints apply. Here the CHECK on status refuses it.

In scope: `service` (ArxPgDbService).

```csharp
var plain = new ArxEntity("account");
plain["name"] = "Plain value";
plain["status"] = "DELETED";

try
{
    await service.CreateAsync(plain);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.CheckViolation)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name = 'Plain value';
```

Expect: 0: the table's CHECK on status refused it.

### Turn ARX error codes into messages for the user

Real case 5 of Create.

An "add account" screen that turns ARX errors into messages for the user, in four scenarios: constraints the database enforces, values the column can't hold, requests that don't fit the table, and the native details kept on a database error. Every error has a stable code (the same on every database), a category, and the entity, attribute or constraint involved. Messages name things, never values, so they are safe to log.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var existing = new ArxEntity("account");
existing["name"] = "Litware Inc.";
existing["account_number"] = "ACC-20001";
await service.CreateAsync(existing);
```

#### 1. Constraints the database enforces

Constraints the database enforces: a unique number, a required name, a CHECK on the employee count.

In scope: `service` (ArxPgDbService).

```csharp
var numberTaken = new ArxEntity("account");
numberTaken["name"] = "Litware Europe";
numberTaken["account_number"] = "ACC-20001";

var noName = new ArxEntity("account");
noName["website"] = "https://no-name.example";

var negativeCount = new ArxEntity("account");
negativeCount["name"] = "Shrinking Co.";
negativeCount["employee_count"] = -5;

foreach (var attempt in new[] { numberTaken, noName, negativeCount })
{
    await SaveAndExplainAsync(service, attempt);
}
```

Defined in the sample, used above:

```csharp
private static async Task SaveAndExplainAsync(ArxPgDbService service, ArxEntity attempt)
{
    Console.WriteLine($"save {attempt["name"] ?? "(no name)"}");

    try
    {
        await service.CreateAsync(attempt);
    }
    catch (ArxException error)
    {
        // Handle by Code or Category, never by parsing the message.
        var forTheUser = error.Code switch
        {
            ArxErrorCode.UniqueViolation => "That account number is already in use.",
            ArxErrorCode.NotNullViolation => $"Please fill in {error.AttributeName}.",
            ArxErrorCode.CheckViolation => "One of the values isn't allowed.",
            ArxErrorCode.ValueTooLong => "One of the values is too long.",
            ArxErrorCode.ValueOutOfRange => "One of the numbers is too large.",
            ArxErrorCode.DataTypeMismatch or ArxErrorCode.InvalidValueFormat => "One of the values has the wrong format.",
            _ when error.IsTransient => "The database is busy. Please try again.",
            _ => "Something went wrong. The error was logged."
        };

        Console.WriteLine($"  {error.Code} ({error.Category}), constraint {error.ConstraintName ?? "-"}, attribute {error.AttributeName ?? "-"}");
        Console.WriteLine($"  logged:    {error.Message}");
        Console.WriteLine($"  user sees: {forTheUser}");
    }
}
```

Check it in the database:

```sql
SELECT name, account_number, employee_count
FROM account
WHERE account_number = 'ACC-20001' OR name IN ('Litware Europe', 'Shrinking Co.') OR website = 'https://no-name.example';
```

Expect: 1 row, Litware Inc.: the three attempts were refused.

#### 2. Values the column can't hold

Values the column can't hold: too long, out of range, the wrong type.

In scope: `service` (ArxPgDbService).

```csharp
var longWebsite = new ArxEntity("account");
longWebsite["name"] = "Long URL Ltd.";
longWebsite["website"] = "https://example.com/" + new string('a', 200);

var hugeCount = new ArxEntity("account");
hugeCount["name"] = "Huge Corp.";
hugeCount["employee_count"] = 3_000_000_000L;

var textForNumber = new ArxEntity("account");
textForNumber["name"] = "Typo Corp.";
textForNumber["employee_count"] = "forty";

foreach (var attempt in new[] { longWebsite, hugeCount, textForNumber })
{
    await SaveAndExplainAsync(service, attempt);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name IN ('Long URL Ltd.', 'Huge Corp.', 'Typo Corp.');
```

Expect: 0.

#### 3. Requests that don't fit the table

Requests that don't fit the table: a column it doesn't have, a table name that isn't a plain identifier.

In scope: `service` (ArxPgDbService).

```csharp
var unknownColumn = new ArxEntity("account");
unknownColumn["name"] = "Extra Field Corp.";
unknownColumn["fax_number"] = "555-0100";

var badTableName = new ArxEntity("account; DROP TABLE account");
badTableName["name"] = "Bad Table Name Corp.";

foreach (var attempt in new[] { unknownColumn, badTableName })
{
    await SaveAndExplainAsync(service, attempt);
}
```

Check it in the database:

```sql
SELECT count(*) AS stored, to_regclass('account') IS NOT NULL AS account_table_still_there
FROM account
WHERE name IN ('Extra Field Corp.', 'Bad Table Name Corp.');
```

Expect: 0, true: nothing stored, and the hostile table name changed nothing.

#### 4. Native details on a database error

ArxValidationException is found before the database is called; ArxDatabaseException is reported by PostgreSQL, with the native details kept.

In scope: `service` (ArxPgDbService).

```csharp
var numberTaken = new ArxEntity("account");
numberTaken["name"] = "Litware Europe";
numberTaken["account_number"] = "ACC-20001";

try
{
    await service.CreateAsync(numberTaken);
}
catch (ArxDatabaseException error)
{
    Console.WriteLine($"SqlState {error.SqlState}, InnerException {error.InnerException?.GetType().Name}, IsTransient {error.IsTransient}");
}
```

Check it in the database:

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

Expect: 1: still only Litware Inc.

### Values and names that come from users or files

Real case 6 of Create.

Saving what users typed and what files contain, in three scenarios: a public "contact us" form full of SQL-looking text, a CSV header used directly as attribute names, and the better way, mapping the file's columns. Values are always sent as parameters, so any text is stored exactly as typed. Names are validated and quoted, so a name can't change the SQL.

Usings:

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

#### 1. A form submission full of SQL-looking text is stored exactly as typed

A form submission full of SQL-looking text is stored exactly as typed.

In scope: `service` (ArxPgDbService).

```csharp
var form = new ContactForm("Robert'); DROP TABLE contact;--", "O'Brien", "Ünïcödé, emoji 🚀, quotes \" ' and a backslash \\");

var contact = new ArxEntity("contact");
contact["first_name"] = form.FirstName;
contact["last_name"] = form.LastName;
contact["notes"] = form.Message;
var id = await service.CreateAsync(contact);
```

Defined in the sample, used above:

```csharp
private sealed record ContactForm(string FirstName, string LastName, string Message);
```

Check it in the database:

```sql
SELECT first_name, last_name, notes
FROM contact
WHERE last_name = 'O''Brien';
```

Expect: 1 row, stored exactly as typed: Robert'); DROP TABLE contact;-- and the Unicode note.

#### 2. A CSV header used directly as attribute names

Column names from a file header: only ASCII letters, digits and underscores (at most 63 characters) are accepted, and the check happens before any SQL is built. A header with quotes in it is SQL, not a typo, so it is refused as ARX1017 SuspectedInjection (RealCase10_CatchAnInjectionAttempt shows how to handle it).

In scope: `service` (ArxPgDbService).

```csharp
var header = new[] { "first_name", "last_name", "email\" text, \"x" };
var values = new[] { "Ivy", "Chen", "ivy.chen@example.com" };

var imported = new ArxEntity("contact");

for (var i = 0; i < header.Length; i++)
{
    imported[header[i]] = values[i];
}

try
{
    await service.CreateAsync(imported);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'contact' AND column_name LIKE 'email%';
```

Expect: 1 row, email: the header with quotes was refused before any SQL was built.

#### 3. Better: map the file's columns to the attributes you expect

The better way: map the file's columns to the attributes you expect.

In scope: `service` (ArxPgDbService).

```csharp
var csvRow = new Dictionary<string, string> { ["First Name"] = "Ivy", ["Last Name"] = "Chen", ["E-mail"] = "ivy.chen@example.com", ["Fax"] = "ignored" };

var mapped = new ArxEntity("contact");
mapped["first_name"] = csvRow["First Name"];
mapped["last_name"] = csvRow["Last Name"];
mapped["email"] = csvRow["E-mail"];
var id = await service.CreateAsync(mapped);

Console.WriteLine($"stored contact {id}");
```

Check it in the database:

```sql
SELECT first_name, last_name, email
FROM contact
WHERE email = 'ivy.chen@example.com';
```

Expect: 1 row, Ivy Chen.

### Natural ids, uuid ids and a table with a compound key

Real case 7 of Create.

Ids that aren't a generated number, and a table with no id column, in five scenarios: countries by their ISO code, devices with uuid ids, a regional price list identified by region + SKU, and the two mistakes PostgreSQL catches: the same region and SKU again, and one part of the key missing. All declared in SampleCatalog.cs.

Good to know:

A supplied id is an attribute like any other: `country["code"] = "ES"`. Create returns the id as an `ArxRecordId` (also on `entity.RecordId`). A table declared NoIdColumn returns `None`; its declared key (region, sku) is what Update and Delete will find a price by.

Usings:

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

#### 1. Countries by their ISO code (a supplied text id)

Countries by their ISO code: a text id the caller supplies.

In scope: `service` (ArxPgDbService).

```csharp
var spain = new ArxEntity("country");
spain["code"] = "ES";
spain["name"] = "Spain";

var brazil = new ArxEntity("country");
brazil["code"] = "BR";
brazil["name"] = "Brazil";

var philippines = new ArxEntity("country");
philippines["code"] = "PH";
philippines["name"] = "Philippines";

var countryIds = await service.CreateMultipleAsync(new List<ArxEntity> { spain, brazil, philippines });
Console.WriteLine($"ids: {string.Join(", ", countryIds.Select(x => x.AsString()))}");
```

Check it in the database:

```sql
SELECT code, name
FROM country
WHERE code IN ('ES', 'BR', 'PH')
ORDER BY code;
```

Expect: 3 rows, Brazil, Spain, Philippines.

#### 2. Devices with uuid ids: generated by the database, or generated by your code

Devices with uuid ids: generated by the database, or generated by your code.

In scope: `service` (ArxPgDbService).

```csharp
var tablet = new ArxEntity("device");
tablet["serial_number"] = "TAB-0001";

var phone = new ArxEntity("device");
phone["id"] = Guid.NewGuid();   // a client-generated id is written as given
phone["serial_number"] = "PHN-0001";

var deviceIds = await service.CreateMultipleAsync(new List<ArxEntity> { tablet, phone });
Console.WriteLine($"tablet {deviceIds[0].AsGuid()} (from the database), phone {deviceIds[1].AsGuid()} (from the code)");
```

Check it in the database:

```sql
SELECT id, serial_number
FROM device
WHERE serial_number IN ('TAB-0001', 'PHN-0001');
```

Expect: 2 rows, with the uuids the sample printed.

#### 3. A regional price list: 3 regions x 1,000 SKUs, no id column, identified by (region, sku)

A regional price list with no id column: each price is identified by its region and SKU together.

In scope: `service` (ArxPgDbService).

```csharp
var prices = new List<ArxEntity>();

foreach (var region in new[] { "ES", "BR", "PH" })
{
    for (var i = 1; i <= 1_000; i++)
    {
        var price = new ArxEntity("price_list");
        price["region"] = new ArxEntityReference("country", region);   // a lookup to the country, by its code
        price["sku"] = $"SKU-{i:D6}";
        price["unit_price"] = region == "BR" ? 9.90m + i % 50 : 12.50m + i % 40;
        price["valid_from"] = new DateOnly(2026, 1, 1);
        prices.Add(price);
    }
}

var priceIds = await service.CreateMultipleAsync(prices);
```

Check it in the database:

```sql
SELECT region, count(*) AS prices
FROM price_list
WHERE region IN ('ES', 'BR', 'PH')
GROUP BY region
ORDER BY region;
```

Expect: 3 rows, 1000 prices each.

#### 4. The same region and SKU again: the table's primary key refuses it

The same region and SKU again: the table's primary key refuses it.

In scope: `service` (ArxPgDbService).

```csharp
var duplicate = new ArxEntity("price_list");
duplicate["region"] = new ArxEntityReference("country", "ES");
duplicate["sku"] = "SKU-000001";
duplicate["unit_price"] = 1.00m;
duplicate["valid_from"] = new DateOnly(2026, 6, 1);

try
{
    await service.CreateAsync(duplicate);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT region, sku, unit_price, valid_from
FROM price_list
WHERE region = 'ES' AND sku = 'SKU-000001';
```

Expect: 1 row, the original: 13.50 from 2026-01-01 (the duplicate was refused).

#### 5. One part of the key missing: the NOT NULL column refuses it

One part of the key missing: the NOT NULL column refuses it (Create doesn't use keys; the database enforces them).

In scope: `service` (ArxPgDbService).

```csharp
var noRegion = new ArxEntity("price_list");
noRegion["sku"] = "SKU-999999";
noRegion["unit_price"] = 5.00m;
noRegion["valid_from"] = new DateOnly(2026, 6, 1);

try
{
    await service.CreateAsync(noRegion);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.NotNullViolation)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM price_list
WHERE sku = 'SKU-999999';
```

Expect: 0: refused by the NOT NULL region column.

### Catch an SQL injection attempt on create

Real case 8 of Create.

Catching an SQL injection attempt on create, in four scenarios. ARX refuses a name (table or attribute) that carries SQL with its own code, ARX1017 SuspectedInjection, before anything is sent to the database, so you can log it as a security event instead of a bug. Values are never refused: they are parameters, and SQL in a value is just text.

Usings:

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

#### 1. A field name from a form carries SQL

A form whose field names are copied straight into attributes (the mistake), and an attacker who adds a field named with SQL. The whole create is refused with ARX1017; a plain typo in a field name is ARX1003.

In scope: `service` (ArxPgDbService).

```csharp
var posted = new Dictionary<string, object>
{
    ["first_name"] = "Mallory",
    ["last_name"] = "Injection-C1",
    ["notes\") VALUES ('x'); DROP TABLE contact; --"] = "x",
};

var typo = new Dictionary<string, object>
{
    ["first_name"] = "Ted",
    ["last name"] = "Typo-C1",
};

foreach (var form in new[] { posted, typo })
{
    var contact = new ArxEntity("contact");

    foreach (var field in form)
    {
        contact[field.Key] = field.Value;
    }

    try
    {
        await service.CreateAsync(contact);
    }
    catch (ArxValidationException error) when (error.Code is ArxErrorCode.SuspectedInjection or ArxErrorCode.InvalidIdentifier)
    {
        Report(error);
    }
}
```

Defined in the sample, used above:

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

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE last_name = 'Injection-C1' OR first_name = 'Ted';
```

Expect: 0: neither contact was created, and the contact table is still there.

#### 2. A table name from the request carries SQL

A table name taken from the request (`?type=…`). ARX refuses the SQL in it; the fix is to map the request's word to a table from a fixed list, so user input never becomes a name.

In scope: `service` (ArxPgDbService).

```csharp
var requested = "contact; DROP TABLE account; --";

var fromTheRequest = new ArxEntity(requested);
fromTheRequest["first_name"] = "Mallory";
fromTheRequest["last_name"] = "Injection-C2";

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

// The fix: a fixed list. Anything else is refused by the application, before ARX.
string? table = requested switch
{
    "person" => "contact",
    "company" => "account",
    _ => null,
};

Console.WriteLine(table == null ? $"the allow-list refused type '{requested}'" : $"would create in {table}");
```

Check it in the database:

```sql
SELECT to_regclass('public.account') IS NOT NULL AS account_exists,
       (SELECT count(*) FROM contact WHERE last_name = 'Injection-C2') AS created;
```

Expect: account_exists t, created 0.

#### 3. One bad row in an import refuses the whole CreateMultiple

An import of three contacts where one row's column header carries SQL: the whole call is refused, nothing is written.

In scope: `service` (ArxPgDbService).

```csharp
var first = new ArxEntity("contact");
first["first_name"] = "Ann";
first["last_name"] = "Import-C3";

var bad = new ArxEntity("contact");
bad["first_name"] = "Bob";
bad["last_name\" text, \"x"] = "Import-C3";

var third = new ArxEntity("contact");
third["first_name"] = "Cy";
third["last_name"] = "Import-C3";

try
{
    await service.CreateMultipleAsync(new List<ArxEntity> { first, bad, third });
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    Report(error);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE last_name = 'Import-C3';
```

Expect: 0: Ann and Cy weren't created either.

#### 4. SQL in a value is stored as plain text

SQL in a value isn't an attack on ARX: values are parameters, so it's stored exactly as typed and nothing is refused.

In scope: `service` (ArxPgDbService).

```csharp
var contact = new ArxEntity("contact");
contact["first_name"] = "Robert'); DROP TABLE contact; --";
contact["last_name"] = "Value-C4";
var id = await service.CreateAsync(contact);

Console.WriteLine($"created contact {id}");
```

Check it in the database:

```sql
SELECT first_name
FROM contact
WHERE last_name = 'Value-C4';
```

Expect: 1 row: Robert'); DROP TABLE contact; -- (stored as typed; the contact table is still there).

### Register ARX in a DI container and inject it

Advanced case 1 of Create.

Registering ARX in a dependency injection container (Microsoft.Extensions.DependencyInjection, as in ASP.NET Core and worker services) and using it from an application service, in two scenarios: register, then use.

Good to know:

Register one ArxPgDbService as a singleton: it holds no connection, so it is shared safely across requests; each call takes a connection from the Npgsql pool. Register the catalog once too. Inject ArxPgDbService itself for Create; inject IArxBatchDataService where the code only needs batch scopes. Code that should not depend on the provider can take IArxDataService instead: see "Set the service up for production".

Usings:

```csharp
using ARX.SDK.PostgreSql.Services;
using ARX.SDK.Services;
using Microsoft.Extensions.DependencyInjection;
```

#### 1. Register the schema catalog and the service (in ASP.NET Core: builder.Services)

Register the schema catalog and the service once, as singletons (in ASP.NET Core: builder.Services).

In scope: `connectionString` (string).

```csharp
var services = new ServiceCollection();

services.AddSingleton(SampleCatalog.Build());   // the schema catalog: table ids, keys and selection sets
services.AddSingleton(provider => new ArxPgDbService(connectionString, provider.GetRequiredService<ArxSchemaCatalog>()));
services.AddSingleton<IArxBatchDataService>(provider => provider.GetRequiredService<ArxPgDbService>());
services.AddScoped<CustomerOnboarding>();

return services.BuildServiceProvider();
```

Defined in the sample, used above:

```csharp
private sealed class CustomerOnboarding
{
    private readonly ArxPgDbService _service;

    public CustomerOnboarding(ArxPgDbService service)
    {
        _service = service;
    }

    /// <summary>Creates an account and its primary contact together: both or neither.</summary>
    public async Task<ArxRecordId> SignUpAsync(string company, string accountNumber, string firstName, string lastName, string email)
    {
        ArxRecordId accountId = null!;

        await _service.RunBatchAsync(async batch =>
        {
            var account = new ArxEntity("account");
            account["name"] = company;
            account["account_number"] = accountNumber;
            account["status"] = new ArxSelectionSetValue("AccountStatus", "ACTIVE");
            accountId = await batch.CreateAsync(account);

            var contact = new ArxEntity("contact");
            contact["account_id"] = new ArxEntityReference("account", accountId);
            contact["first_name"] = firstName;
            contact["last_name"] = lastName;
            contact["email"] = email;
            await batch.CreateAsync(contact);
        });

        return accountId;
    }
}
```

Note: Nothing is stored: this scenario only registers the catalog and the service.

#### 2. Resolve the application service in a scope (in ASP.NET Core: one per request) and use it

Resolve the application service in a scope (in ASP.NET Core: one per request) and use it.

In scope: `provider` (ServiceProvider).

```csharp
using var scope = provider.CreateScope();
var onboarding = scope.ServiceProvider.GetRequiredService<CustomerOnboarding>();

var accountId = await onboarding.SignUpAsync("Proseware Inc.", "ACC-30001", "Olivia", "Grant", "olivia.grant@proseware.example");

Console.WriteLine($"signed up account {accountId} with its primary contact");
Console.WriteLine($"same service instance everywhere: {ReferenceEquals(provider.GetRequiredService<ArxPgDbService>(), provider.GetRequiredService<IArxBatchDataService>())}");
```

Check it in the database:

```sql
SELECT a.name, a.account_number, a.status, c.first_name, c.last_name, c.email
FROM account a
JOIN contact c ON c.account_id = a.id
WHERE a.account_number = 'ACC-30001';
```

Expect: 1 row, Proseware Inc. with Olivia Grant: both or neither.

### Several calls in one ARX transaction: commit or roll back

Advanced case 2 of Create.

Several ARX calls in one transaction, with ARX types only, in three scenarios: commit, roll back, and a failed call that aborts the transaction. Open an `ArxPgConnection`, begin a transaction on it, pass the transaction to each call, then commit or roll back. No Npgsql type appears in application code.

Good to know:

Use a transaction when your code decides, step by step, whether the work is kept: create the account, create its contacts, check something, then commit or roll back. For imports in batches and processes ARX runs from start to finish, the batch scope samples (3 and 4) do the same with less code.

Usings:

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

#### 1. Commit: an account and its contacts, kept together

Commit: an account and its contacts, kept together.

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

```csharp
// The connection is ARX's own: it opens on BeginTransactionAsync and closes when disposed.
await using (var connection = new ArxPgConnection(connectionString))
await using (var transaction = await connection.BeginTransactionAsync())
{
    var account = new ArxEntity("account");
    account["name"] = "Woodgrove Bank";
    account["industry"] = new ArxSelectionSetValue("Industry", Industry.Finance);

    // Pass the transaction to each call: the rows are written inside it.
    var accountId = await service.CreateAsync(account, transaction);

    var ines = new ArxEntity("contact");
    ines["account_id"] = new ArxEntityReference("account", accountId);
    ines["first_name"] = "Ines";
    ines["last_name"] = "Moreau";

    var tom = new ArxEntity("contact");
    tom["account_id"] = new ArxEntityReference("account", accountId);
    tom["first_name"] = "Tom";
    tom["last_name"] = "Becker";

    await service.CreateMultipleAsync(new List<ArxEntity> { ines, tom }, transaction);

    await transaction.CommitAsync();
}
```

Check it in the database:

```sql
SELECT a.name, a.industry, c.first_name, c.last_name
FROM contact c
JOIN account a ON a.id = c.account_id
WHERE c.last_name IN ('Moreau', 'Becker');
```

Expect: 2 rows, Ines Moreau and Tom Becker of Woodgrove Bank, industry 4 (Finance).

#### 2. Roll back: your own check fails after the account was created

Roll back: your own check fails after the account was created.

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

```csharp
await using (var connection = new ArxPgConnection(connectionString))
await using (var transaction = await connection.BeginTransactionAsync())
{
    var account = new ArxEntity("account");
    account["name"] = "Margie's Travel";
    account["credit_limit"] = 50_000m;
    await service.CreateAsync(account, transaction);

    var creditCheckPassed = false;   // say your credit check service says no

    if (creditCheckPassed)
    {
        await transaction.CommitAsync();
    }
    else
    {
        await transaction.RollbackAsync();
    }

    // Leaving the block without committing would roll back too: disposing an active transaction rolls it back.
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name = 'Margie''s Travel';
```

Expect: 0: rolled back.

#### 3. A failed call aborts the transaction: ARX refuses to commit it

In PostgreSQL, a statement that fails aborts the whole transaction: the earlier rows are discarded too, and the database would turn a later COMMIT into a silent rollback. ARX tracks this: the transaction is marked aborted, further calls are refused, and CommitAsync throws instead of pretending to commit.

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

```csharp
await using (var connection = new ArxPgConnection(connectionString))
await using (var transaction = await connection.BeginTransactionAsync())
{
    var account = new ArxEntity("account");
    account["name"] = "Alpine Ski House";
    account["account_number"] = "ACC-40001";
    await service.CreateAsync(account, transaction);

    var duplicate = new ArxEntity("account");
    duplicate["name"] = "Alpine Ski House (copy)";
    duplicate["account_number"] = "ACC-40001";

    try
    {
        await service.CreateAsync(duplicate, transaction);
    }
    catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
    {
        Console.WriteLine(error.Message);
        Console.WriteLine($"transaction.IsAborted = {transaction.IsAborted}");
    }

    try
    {
        await transaction.CommitAsync();
    }
    catch (ArxValidationException error) when (error.Code == ArxErrorCode.TransactionAborted)
    {
        Console.WriteLine(error.Message);
    }
}
```

Check it in the database:

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

Expect: 0: the failed call aborted the transaction, so nothing was committed.

### Load ids and keys from JSON and validate them at startup

Advanced case 3 of Create.

Preloading the schema catalog, in three scenarios: each table's id column and keys come from JSON, as an application might keep them in a file or a settings store; they are checked against the database at startup with ValidateSchemaAsync; and a declaration that drifted is found before any write.

Good to know:

The ids and keys are always the developer's declaration: ARX reads them from the database only when asked to preload them (Advanced6). Loading them from JSON is plain application code (below), like the selection sets in SampleCatalog. ValidateSchemaAsync is the explicit check: ARX never runs it by itself. Run it at startup or in a health check, so a declaration that drifted from the schema is found before any write.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.PostgreSql.Services;
using ARX.SDK.Services;
using System.Text.Json;
```

#### 1. Build the schema catalog from JSON: ids and keys, plus the selection sets loaded the same way

Build the schema catalog from JSON: ids and keys, plus the selection sets loaded the same way.

```csharp
var builder = ArxSchemaCatalog.Create();
AddTablesFromJson(builder, TablesJson);

foreach (var set in SampleCatalog.FromJson(SampleCatalog.SetsJson))
{
    builder.Add(set);
}

var schema = builder.Build();

foreach (var table in schema.Tables)
{
    var keys = table.Keys.Count == 0 ? "no keys" : "keys " + string.Join(" ", table.Keys);
    Console.WriteLine($"{table.Name,-12} id {table.IdColumn}; {keys}");
}

return schema;
```

Defined in the sample, used above:

```csharp
private static void AddTablesFromJson(ArxSchemaCatalogBuilder builder, string json)
{
    var tables = JsonSerializer.Deserialize<List<JsonTable>>(json, new JsonSerializerOptions { PropertyNameCaseInsensitive = true })
        ?? throw new InvalidOperationException("The table JSON is empty.");

    foreach (var table in tables)
    {
        builder.Table(table.Name, table.Id == null ? ArxIdColumn.NoIdColumn : IdColumn(table.Id.Column, table.Id.Type, table.Id.Generated));

        foreach (var key in table.Keys ?? new List<string[]>())
        {
            builder.Key(table.Name, key);
        }
    }
}

private static ArxIdColumn IdColumn(string name, string type, bool generated) => (type.ToLowerInvariant(), generated) switch
{
    ("long", true) => ArxIdColumn.Generated<long>(name),
    ("long", false) => ArxIdColumn.Supplied<long>(name),
    ("int", true) => ArxIdColumn.Generated<int>(name),
    ("int", false) => ArxIdColumn.Supplied<int>(name),
    ("guid", true) => ArxIdColumn.Generated<Guid>(name),
    ("guid", false) => ArxIdColumn.Supplied<Guid>(name),
    ("string", true) => ArxIdColumn.Generated<string>(name),
    ("string", false) => ArxIdColumn.Supplied<string>(name),
    ("date", _) => ArxIdColumn.Supplied<DateOnly>(name),
    _ => throw new InvalidOperationException($"Id column '{name}' has an unknown type '{type}' in the JSON.")
};

private sealed record JsonTable(string Name, JsonIdColumn Id, List<string[]> Keys);

private sealed record JsonIdColumn(string Column, string Type, bool Generated = false);
```

Note: Nothing is stored: the catalog is built in memory from JSON.

#### 2. At startup, check the declarations against the database

At startup, check the declarations against the database, then use the catalog.

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

```csharp
var service = new ArxPgDbService(connectionString, schema);
await service.ValidateSchemaAsync();
Console.WriteLine("ValidateSchemaAsync: every declared id and key matches its table");

var device = new ArxEntity("device");
device["serial_number"] = "LOADED-0001";
var deviceId = await service.CreateAsync(device);
Console.WriteLine($"created device {deviceId} through the loaded catalog");
```

Check it in the database:

```sql
SELECT id, serial_number
FROM device
WHERE serial_number = 'LOADED-0001';
```

Expect: 1 row, created through the loaded catalog.

#### 3. A declaration that drifted from the schema is found before any write

Someone changed the JSON by mistake: the uuid id is declared long, and a key names a column that isn't unique.

In scope: `connectionString` (string).

```csharp
const string drifted = """
    [
      { "name": "device",  "id": { "column": "id", "type": "long", "generated": true } },
      { "name": "account", "id": { "column": "id", "type": "long", "generated": true }, "keys": [ [ "name" ] ] }
    ]
    """;

var driftedBuilder = ArxSchemaCatalog.Create();
AddTablesFromJson(driftedBuilder, drifted);

try
{
    await new ArxPgDbService(connectionString, driftedBuilder.Build()).ValidateSchemaAsync();
}
catch (ArxException error) when (error.Code == ArxErrorCode.SchemaMismatch)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
-- Nothing is stored. What ValidateSchemaAsync compared the drifted JSON with:
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE (table_name, column_name) IN (('device', 'id'), ('account', 'name'));
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'account';
```

Expect: device.id is uuid (declared long in the JSON); no unique index on account.name alone.

### Write to two databases, each with its own service

Advanced case 4 of Create.

Two databases in one application, in three scenarios: one service per database, an order placed in the CRM (the source of truth) and recorded in a reporting database (an event log), and a retry of the copy. One `ArxPgDbService` per database, each with its own connection string and schema catalog, registered as keyed services and injected by key.

Good to know:

Each database has its own transactions: ARX can't make a write to two databases atomic (there's no distributed transaction). Write the source of truth first and commit it, then write the copy. Make the copy safe to repeat (here the event is unique per order number), so a failed copy can simply be retried.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.PostgreSql.Services;
using ARX.SDK.Services;
using Microsoft.Extensions.DependencyInjection;
```

Set up by the sample before its scenarios:

```csharp
await dbContext.Direct.EnsureDatabaseExistsAsync(REPORTING_DATABASE);
var reportingDirect = dbContext.Direct.ForDatabase(REPORTING_DATABASE);
await reportingDirect.ExecuteAsync(
    "DROP TABLE IF EXISTS order_event; "
    + "CREATE TABLE order_event (id bigserial PRIMARY KEY, order_number varchar(20) NOT NULL UNIQUE, account_name text NOT NULL, "
    + "total numeric(14, 2) NOT NULL, recorded_at timestamptz NOT NULL DEFAULT now())");
using var scope = provider.CreateScope();
var desk = scope.ServiceProvider.GetRequiredService<OrderDesk>();
```

#### 1. Register one service per database, by key (in ASP.NET Core: builder.Services)

Register one service per database, by key (in ASP.NET Core: builder.Services).

In scope: `connectionString` (string).

```csharp
var services = new ServiceCollection();

// Each database has its own schema catalog: only the tables that database has.
services.AddKeyedSingleton("crm", (_, _) => new ArxPgDbService(connectionString, SampleCatalog.Build()));
services.AddScoped<OrderDesk>();

return services.BuildServiceProvider();
```

Defined in the sample, used above:

```csharp
private sealed class OrderDesk
{
    private readonly ArxPgDbService _crm;
    private readonly ArxPgDbService _reporting;

    public OrderDesk([FromKeyedServices("crm")] ArxPgDbService crm, [FromKeyedServices("reporting")] ArxPgDbService reporting)
    {
        _crm = crm;
        _reporting = reporting;
    }

    /// <summary>The source of truth first, in one CRM transaction; then the copy in reporting.</summary>
    public async Task<ArxRecordId> PlaceOrderAsync(string accountName, string orderNumber, decimal total)
    {
        ArxRecordId orderId = null!;

        await _crm.RunBatchAsync(async batch =>
        {
            var account = new ArxEntity("account");
            account["name"] = accountName;
            await batch.CreateAsync(account);

            var order = new ArxEntity("sales_order");
            order["order_number"] = orderNumber;
            order["account_id"] = new ArxEntityReference("account", account.RecordId);
            order["ordered_on"] = DateTime.UtcNow;
            order["total"] = total;
            orderId = await batch.CreateAsync(order);
        });

        // Committed in the CRM. If this next write fails, the order still stands: retry the event later.
        await RecordEventAsync(accountName, orderNumber, total);
        return orderId;
    }

    /// <summary>Records the order in reporting. Returns false when it was already recorded (safe to call again).</summary>
    public async Task<bool> RecordEventAsync(string accountName, string orderNumber, decimal total)
    {
        var orderEvent = new ArxEntity("order_event");
        orderEvent["order_number"] = orderNumber;
        orderEvent["account_name"] = accountName;
        orderEvent["total"] = total;

        try
        {
            await _reporting.CreateAsync(orderEvent);
            return true;
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
        {
            return false;
        }
    }
}
```

Check it in the database:

```sql
SELECT datname
FROM pg_database
WHERE datname IN ('arx_sdk', 'arx_sdk_reporting');
```

Expect: 2 rows: the CRM database and the reporting database.

#### 2. Place an order in the CRM, then record it in reporting

The source of truth first, in one CRM transaction; then the copy in reporting.

In scope: `desk` (OrderDesk).

```csharp
var orderId = await desk.PlaceOrderAsync("Fourth Coffee", "SO-6001", 149.70m);
```

Check it in the database:

```sql
SELECT o.order_number, o.total, a.name AS account
FROM sales_order o
JOIN account a ON a.id = o.account_id
WHERE o.order_number = 'SO-6001';
-- In the arx_sdk_reporting database:
SELECT order_number, account_name, total, recorded_at
FROM order_event
WHERE order_number = 'SO-6001';
```

Expect: the order in the CRM (Fourth Coffee, 149.70), and its event in reporting.

#### 3. Recording the same event again (a retry after a failure) is harmless

Recording the same event again (a retry after a failure) is harmless: the order number is unique there.

In scope: `desk` (OrderDesk).

```csharp
var recordedAgain = await desk.RecordEventAsync("Fourth Coffee", "SO-6001", 149.70m);
```

Check it in the database:

```sql
-- In the arx_sdk_reporting database:
SELECT count(*)
FROM order_event
WHERE order_number = 'SO-6001';
```

Expect: 1: the retry didn't record a second event.

### A security check around creates: log, block, handle false positives

Advanced case 5 of Create.

A security check around every create (`SecurityGuard`, in the project root), in four scenarios: a normal request passes through; an attack is logged and the user sees a plain message; repeated attacks block the user; and a false positive, a harmless file header flagged as SQL, is triaged and fixed without blocking anyone.

Good to know:

ARX1017 is only raised for a name that is already invalid, so a false positive never stops a call that would otherwise have worked: without the check it would fail with ARX1003. What can be wrong is the label: a harmless name (`E-mail (work)`, `Price (EUR)`, `notes;` pasted into a setting) reported as an attack. Handle it with:

- **The source of the name:** `NameSource.Code` for names from your code or configuration, so an ARX1017 there is logged as a bug and blocks nobody; `NameSource.UserInput` for names built from what a user sent.
- **Strikes, not one event:** a single ARX1017 never blocks anyone.
- **Triage:** the log has ARX's evidence (`contains '('`); if it's harmless, `Unblock` the user and fix what let the name through.
- **The real fix:** map user input to names from a fixed list (headers to attributes, request types to tables), so user input never becomes a name, and neither attacks nor false positives can happen.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var guard = new SecurityGuard(blockAfter: 3);
```

#### 1. A normal request passes through

A normal request goes through the guard untouched.

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

```csharp
var result = await CreateContactAsync(service, guard, "ana", new Dictionary<string, object>
{
    ["first_name"] = "Ana",
    ["last_name"] = "Guard-C1",
    ["email"] = "guard-ana@example.com",
});

Console.WriteLine($"ana: {result.Outcome}, contact {result.Value}");
```

Defined in the sample, used above:

```csharp
private static Task<GuardResult<ArxRecordId>> CreateContactAsync(ArxPgDbService service, SecurityGuard guard, string user, Dictionary<string, object> posted)
{
    var contact = new ArxEntity("contact");

    foreach (var field in posted)
    {
        contact[field.Key] = field.Value;
    }

    return guard.RunAsync(user, "create contact", NameSource.UserInput, () => service.CreateAsync(contact));
}
```

Check it in the database:

```sql
SELECT first_name, email
FROM contact
WHERE last_name = 'Guard-C1';
```

Expect: 1 row: Ana, guard-ana@example.com.

#### 2. An attack is logged; the user sees a plain message

A field name carrying SQL: ARX refuses it (ARX1017), the guard logs it and the user sees a plain message.

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

```csharp
var result = await CreateContactAsync(service, guard, "mallory", new Dictionary<string, object>
{
    ["first_name"] = "Mallory",
    ["last_name"] = "Guard-C2",
    ["notes\") VALUES ('x'); DROP TABLE contact; --"] = "x",
});

Console.WriteLine($"mallory sees: \"{result.ForTheUser}\"");
var logged = guard.Events[^1];
Console.WriteLine($"security log: {logged.Kind}, {logged.User}, {logged.Action}");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE last_name = 'Guard-C2';
```

Expect: 0: nothing created.

#### 3. Repeated attacks block the user

Two more attempts make three strikes: the user is blocked, and even a valid request isn't attempted.

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

```csharp
foreach (var probe in new[] { "last_name; DROP TABLE account", "email UNION SELECT 1" })
{
    await CreateContactAsync(service, guard, "mallory", new Dictionary<string, object> { ["first_name"] = "Mallory", [probe] = "x" });
}

var valid = await CreateContactAsync(service, guard, "mallory", new Dictionary<string, object>
{
    ["first_name"] = "Mallory",
    ["last_name"] = "Guard-C3",
});

Console.WriteLine($"mallory blocked: {guard.IsBlocked("mallory")}; a valid request: {valid.Outcome}, \"{valid.ForTheUser}\"");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE first_name = 'Mallory';
```

Expect: 0: no request from mallory created anything, the last one wasn't even attempted.

#### 4. A false positive: a harmless file header flagged as SQL

A false positive: a supplier file's header "E-mail (work)" is used as an attribute name. The parentheses make ARX flag it as SQL, though it's harmless. One strike doesn't block anyone; triage reads the log, and the fix is the mapping that should have been there: file headers mapped to known attributes, so user input never becomes a name.

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

```csharp
var header = new[] { "first_name", "last_name", "E-mail (work)" };
var row = new object[] { "Sam", "Guard-C4", "guard-sam@example.com" };

var asIs = header.Zip(row).ToDictionary(x => x.First, x => x.Second);
var refused = await CreateContactAsync(service, guard, "sam", asIs);

Console.WriteLine($"sam: {refused.Outcome}; blocked: {guard.IsBlocked("sam")} (one strike blocks nobody)");
Console.WriteLine($"triage reads: {guard.Events[^1].Detail}");

// The fix: known headers mapped to attributes; anything else is ignored (or reported to the user), never a name.
var headerMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
{
    ["first_name"] = "first_name",
    ["last_name"] = "last_name",
    ["E-mail (work)"] = "email",
    ["E-mail"] = "email",
};

var mapped = header.Zip(row).Where(x => headerMap.ContainsKey(x.First)).ToDictionary(x => headerMap[x.First], x => x.Second);
var created = await CreateContactAsync(service, guard, "sam", mapped);

Console.WriteLine($"after the fix: {created.Outcome}, contact {created.Value}");
```

Check it in the database:

```sql
SELECT first_name, email
FROM contact
WHERE last_name = 'Guard-C4';
```

Expect: 1 row: Sam, guard-sam@example.com (created once, after the fix).

### Preload the schema at service setup: keys only, data types, Strict

Advanced case 6 of Create.

The schema preloaded when the service is set up, in four scenarios: what a preload of "account" finds, including the contact table it added because account.primary_contact_id references it (with that reason, in the JSON); the convenient setup, keys only; the typed setup, data types for the columns you name; and Strict, where the preloaded columns are an allow list.

Good to know:

`new ArxPreLoadTable(tables)` names the tables: an empty array loads every column, a list loads only those columns (the id is always loaded). `LoadAsync` reads them from the database once, at startup, and returns an ordinary `ArxSchemaCatalog`; no operation reads the database's catalog afterwards.

Two modes, the developer's choice. **KeysOnly** (convenient): the id, the business keys and the lookups, what you would otherwise declare by hand. **DataTypes** (the default, typed): those plus every loaded column's type, so a value of the wrong type is refused before the database, a string goes into an enum or jsonb column, and enum columns are read as text from the first read. `Strict` needs DataTypes.

Usings:

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

#### 1. Preload "account": what it found, and the table it added

Preload "account" with every column: contact comes too, because a column of account references it.

In scope: `connectionString` (string).

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

var preload = new ArxPreLoadTable(tables);
var schema = await preload.LoadAsync(connectionString);

foreach (var table in schema.Tables)
{
    Console.WriteLine($"{table.Name,-8} {table.Reason}");
    Console.WriteLine($"         id {table.IdColumn}; keys {string.Join(" ", table.Keys)}; {table.Columns.Count} columns");
    Console.WriteLine($"         lookups {string.Join(", ", table.Lookups.Select(x => $"{x.Key} -> {x.Value}"))}");
}

// The catalog as JSON: keep it in a file or a settings store and reload it with AddJson (RetrieveMultiple Advanced10).
var json = schema.ToJson();
Console.WriteLine($"ToJson: {json.Length:N0} characters; contact's entry starts:");

foreach (var line in json.Split('\n').SkipWhile(x => !x.Contains("\"name\": \"contact\"")).Take(3))
{
    Console.WriteLine("  " + line.TrimEnd());
}
```

Check it in the database:

```sql
SELECT conname, conrelid::regclass AS from_table, confrelid::regclass AS to_table
FROM pg_constraint
WHERE conname = 'account_primary_contact_fkey';
```

Expect: 1 row, account -> contact: the reference the preload followed to add contact (reason "added: referenced by account.primary_contact_id").

#### 2. The convenient setup: ArxPreLoadMode.KeysOnly

Convenient: keys only. The ids, keys and lookups you would otherwise declare by hand.

In scope: `connectionString` (string).

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

var schema = await new ArxPreLoadTable(tables, ArxPreLoadMode.KeysOnly).LoadAsync(connectionString);
var service = new ArxPgDbService(connectionString, schema);

var contact = new ArxEntity("contact");
contact["first_name"] = "Paz";
contact["last_name"] = "Preload";
contact["email"] = "paz.preload@example.com";
var contactId = await service.CreateAsync(contact);

// primary_contact_id is a lookup the preload found: the reference is checked against contact's id type first.
var account = new ArxEntity("account");
account["name"] = "Preloaded Pantry";
account["account_number"] = "ACC-PRE81";
account["primary_contact_id"] = new ArxEntityReference("contact", contactId);
var accountId = await service.CreateAsync(account);
Console.WriteLine($"created contact {contactId} and account {accountId}, its primary contact");
```

Check it in the database:

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

Expect: 1 row: Preloaded Pantry, Paz Preload.

#### 3. The typed setup: ArxPreLoadMode.DataTypes for four columns

Typed: data types for the columns you name. A wrong type is refused before the database.

In scope: `connectionString` (string).

```csharp
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("account", ["name", "account_number", "employee_count", "founded_on"]);

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

schema.TryGetTable("account", out var table);
Console.WriteLine("loaded: " + string.Join(", ", table!.Columns.Values));

var account = new ArxEntity("account");
account["name"] = "Typed Tailors";
account["account_number"] = "ACC-PRE82";
account["employee_count"] = 12;
account["founded_on"] = new DateOnly(2019, 4, 1);
Console.WriteLine($"created account {await service.CreateAsync(account)}");

var wrong = new ArxEntity("account");
wrong["name"] = "Wrongly Typed";
wrong["account_number"] = "ACC-PRE83";
wrong["employee_count"] = "twelve";

try
{
    await service.CreateAsync(wrong);
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.DataTypeMismatch)
{
    Console.WriteLine(exception.Message);
}
```

Check it in the database:

```sql
SELECT account_number, employee_count, founded_on
FROM account
WHERE account_number IN ('ACC-PRE82', 'ACC-PRE83');
```

Expect: 1 row, ACC-PRE82, 12, 2019-04-01. ACC-PRE83 was refused before the database (ARX4004).

#### 4. Strict: the preloaded columns are an allow list

Strict: a column that wasn't preloaded is refused (ARX1018); the same service not Strict writes it.

In scope: `connectionString` (string).

```csharp
// A sign-up endpoint may write these three columns, nothing else.
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("account", ["name", "account_number", "website"]);

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

var signUp = new ArxEntity("account");
signUp["name"] = "Strict Stationers";
signUp["account_number"] = "ACC-PRE84";
signUp["website"] = "https://stationers.example.com";
signUp["credit_limit"] = 1_000_000m;

try
{
    await strict.CreateAsync(signUp);
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.NotPreloaded)
{
    Console.WriteLine(exception.Message);
}

// Not Strict (the default): the preload is what ARX knows ahead of time, and other columns still work.
var relaxed = new ArxPgDbService(connectionString, schema);
Console.WriteLine($"not Strict: created account {await relaxed.CreateAsync(signUp)} with its credit limit");
```

Check it in the database:

```sql
SELECT account_number, website, credit_limit
FROM account
WHERE account_number = 'ACC-PRE84';
```

Expect: 1 row, credit_limit 1000000.00: written by the service that isn't Strict; the Strict one refused credit_limit before the database (ARX1018).

### Set the service up for production

Advanced case 7 of Create.

Set the service up for production, in three scenarios: depend on IArxDataService, check the server at startup, and trace and measure every call.

Good to know:

`IArxDataService` is what every ARX service does, whatever its database: application code can depend on it, register the provider behind it, and mock it in tests (the provider type still has what is its own, such as its connections). `CheckServerAsync` asks the server, once, what it allows: its version, and the capabilities it removes, with the reason (a standalone MongoDB runs no transactions, for one). Every operation is an activity of the `ARX.SDK` ActivitySource and a measurement of the `ARX.SDK` Meter: OpenTelemetry reads them with `AddSource("ARX.SDK")` and `AddMeter("ARX.SDK")`, and so does a plain `ActivityListener`, as here. They carry the operation, the table and, on failure, the ARX code: never the values.

Usings:

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

Set up by the sample before its scenarios:

```csharp
IArxDataService data = service;
```

#### 1. Depend on IArxDataService

An application service that depends on IArxDataService, not on the provider's type.

In scope: `data` (IArxDataService).

```csharp
var onboarding = new Onboarding(data);
await onboarding.AddAsync("Fabrikam Robotics", "ACC-60001");

Console.WriteLine($"added Fabrikam Robotics through IArxDataService ({data.Capabilities.ProviderName})");
```

Defined in the sample, used above:

```csharp
private sealed class Onboarding(IArxDataService data)
{
    public Task<ArxRecordId> AddAsync(string name, string key)
    {
        var record = new ArxEntity("account");
        record["name"] = name;
        record["account_number"] = key;
        return data.CreateAsync(record);
    }
}
```

Check it in the database:

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

Expect: 1 row, Fabrikam Robotics.

#### 2. Check the server at startup

Ask the server once, at startup, what it allows.

In scope: `data` (IArxDataService).

```csharp
ArxServerCheck check = await data.CheckServerAsync();

Console.WriteLine($"ready: {check.IsReady}; transactions here: {check.Supports(ArxCapability.Transactions)}; limits: {check.Limits.Count}");
```

Check it in the database:

```sql
SELECT version();
```

Expect: the server's version, the same as check.ServerVersion.

#### 3. Trace and measure every call

Listen to ARX's activities and metrics: one per call, with its table, and its code when it fails.

In scope: `data` (IArxDataService).

```csharp
var calls = new List<string>();
using var tracing = new ActivityListener
{
    ShouldListenTo = source => source.Name == ArxDiagnostics.SourceName,
    Sample = (ref ActivityCreationOptions<ActivityContext> _) => ActivitySamplingResult.AllDataAndRecorded,
    ActivityStopped = activity => calls.Add(activity.DisplayName + (activity.GetTagItem("arx.error.code") is { } code ? $" ({code})" : string.Empty))
};
ActivitySource.AddActivityListener(tracing);

var durations = 0;
using var metrics = new MeterListener
{
    InstrumentPublished = (instrument, listener) =>
    {
        if (instrument.Meter.Name == ArxDiagnostics.SourceName && instrument.Name == "arx.operation.duration")
        {
            listener.EnableMeasurementEvents(instrument);
        }
    }
};
metrics.SetMeasurementEventCallback<double>((_, _, _, _) => Interlocked.Increment(ref durations));
metrics.Start();

await new Onboarding(data).AddAsync("Litware Labs", "ACC-60002");

try
{
    var nobody = new ArxEntity("account") { RecordId = ArxRecordId.Of("id", 999999999L) };
    nobody["name"] = "Nobody";
    await data.UpdateAsync(nobody);
}
catch (ArxException exception) when (exception.Code == ArxErrorCode.RecordNotFound)
{
    Console.WriteLine(exception.Message);
}

Console.WriteLine($"traced: {string.Join("; ", calls)}; durations recorded: {durations}");
```

Check it in the database:

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

Expect: 1 row, Litware Labs (the update found nothing to change).

### Use columns named outside the name rule, through aliases

Advanced case 8 of Create.

Use a table whose columns are named outside ARX's name rule, in three scenarios: declare an alias for each such column and create a record through them, read and query by the aliases, and see that the database's own name is still refused when a call uses it.

Good to know:

ARX accepts only names of ASCII letters, digits and underscores, so no name can break out of its quotes. A table made outside ARX may have columns such as `first-name`, `Customer Code` or `order.total`: declare an alias for each in the schema catalog, `.Alias(table, attribute, databaseName)`. Code uses the attribute; ARX sends the column's name, quoted by PostgreSQL's rules, and reads the column back as the attribute. The id and keys are declared by their attributes.

Only declared names pass: the database name never comes from a call. A link entity's columns keep the name rule in this version.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("legacy_customer", ArxIdColumn.Supplied<string>("customer_code"))
    .Alias("legacy_customer", "customer_code", "Customer Code")
    .Alias("legacy_customer", "first_name", "first-name")
    .Alias("legacy_customer", "last_name", "last-name")
    .Alias("legacy_customer", "order_total", "order.total")
    .Build();
var service = new ArxPgDbService(connectionString, schema);
```

#### 1. Create through the aliases

Create two customers: each attribute is written to the column its alias names.

In scope: `service` (ArxPgDbService).

```csharp
var ana = new ArxEntity("legacy_customer");
ana["customer_code"] = "C-1001";
ana["first_name"] = "Ana";
ana["last_name"] = "Silva";
ana["order_total"] = 120.50m;

var id = await service.CreateAsync(ana);

var ben = new ArxEntity("legacy_customer");
ben["customer_code"] = "C-1002";
ben["first_name"] = "Ben";
ben["order_total"] = 75.00m;

await service.CreateAsync(ben);

Console.WriteLine($"created {id} and C-1002");
```

Check it in the database:

```sql
SELECT "first-name", "order.total" FROM legacy_customer WHERE "Customer Code" IN ('C-1001', 'C-1002');
```

Expect: 2 rows: Ana with 120.50, Ben with 75.00.

#### 2. Read and query by the aliases

Read a record by id and query by the aliases: every column comes back under its alias.

In scope: `service` (ArxPgDbService).

```csharp
var ana = await service.RetrieveAsync("legacy_customer", ArxRecordId.Of("customer_code", "C-1001")) ?? throw new InvalidOperationException("The legacy_customer record isn't there.");

Console.WriteLine($"{ana["customer_code"]}: {ana["first_name"]} {ana["last_name"]}, total {ana["order_total"]}");

var query = new ArxQueryExpression("legacy_customer");
query.ColumnSet.AddColumns("first_name", "order_total");
query.Criteria.AddCondition("order_total", ArxConditionOperator.GreaterThan, 50m);
query.AddOrder("first_name", ArxOrderType.Descending);

var customers = await service.RetrieveMultipleAsync(query);

foreach (var customer in customers.Entities)
{
    Console.WriteLine($"{customer["first_name"]}: {customer["order_total"]}");
}
```

Check it in the database:

```sql
SELECT "first-name", "order.total" FROM legacy_customer WHERE "order.total" > 50 ORDER BY "first-name" DESC;
```

Expect: Ben, then Ana.

#### 3. The database name itself is refused

A call that uses the column's own name is refused before the database: only declared aliases pass.

In scope: `service` (ArxPgDbService).

```csharp
var change = new ArxEntity("legacy_customer");
change["customer_code"] = "C-1002";
change["first-name"] = "Benjamin";

try
{
    await service.UpdateAsync(change);
}
catch (ArxException exception) when (exception.Code == ArxErrorCode.InvalidIdentifier)
{
    Console.WriteLine(exception.Message);
}
```

Check it in the database:

```sql
SELECT "first-name" FROM legacy_customer WHERE "Customer Code" = 'C-1002';
```

Expect: Ben (the update was refused).

### Log every call through ILogger

Advanced case 9 of Create.

Log every ARX call through ILogger, in three scenarios: one line per call with its table and how long it took, a failed call with its ARX code (and the exception logged where it is caught), and only the calls worth keeping in production.

Good to know:

ARX has no logger of its own and takes no logging package: every call is an activity of the `ARX.SDK` ActivitySource. `ArxCallLog`, below, listens to those activities and writes one log line per call: copy it into your application and give it the `ILogger` you already have (`ILogger<T>` from dependency injection), once, at startup. Here a small logger prints to the console, so the sample's output stays in order.

A line names the operation and the table, never the values sent: what ARX logs is safe to keep. An exception's message is written the same way, so it can be logged where it is caught.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.Services;
using Microsoft.Extensions.Logging;
using System.Diagnostics;
```

Set up by the sample before its scenarios:

```csharp
ILogger logger = new ConsoleLineLogger();
```

#### 1. One line per call

Create, read and query an account: one Information line each, with the table and the time it took.

In scope: `data` (IArxDataService), `logger` (ILogger).

```csharp
using var callLog = new ArxCallLog(logger);

var account = new ArxEntity("account");
account["name"] = "Tailspin Toys";
account["account_number"] = "ACC-61001";

ArxRecordId id = await data.CreateAsync(account);
await data.RetrieveAsync("account", id);

var query = new ArxQueryExpression("account");
query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-61001");
await data.RetrieveMultipleAsync(query);
```

Defined in the sample, used above:

```csharp
private sealed class ArxCallLog : IDisposable
{
    private readonly ActivityListener _listener;

    public ArxCallLog(ILogger logger, bool logSuccessfulCalls = true)
    {
        _listener = new ActivityListener
        {
            ShouldListenTo = source => source.Name == ArxDiagnostics.SourceName,
            Sample = (ref ActivityCreationOptions<ActivityContext> _) => ActivitySamplingResult.AllData,
            ActivityStopped = activity => Write(logger, activity, logSuccessfulCalls)
        };

        ActivitySource.AddActivityListener(_listener);
    }

    public void Dispose() => _listener.Dispose();

    private static void Write(ILogger logger, Activity activity, bool logSuccessfulCalls)
    {
        var milliseconds = Math.Round(activity.Duration.TotalMilliseconds, 1);

        if (activity.Status == ActivityStatusCode.Error)
        {
            logger.LogWarning("ARX {Call} failed with {Code} after {Milliseconds} ms", activity.DisplayName, activity.GetTagItem("arx.error.code"), milliseconds);
        }
        else if (logSuccessfulCalls)
        {
            logger.LogInformation("ARX {Call} took {Milliseconds} ms", activity.DisplayName, milliseconds);
        }
    }
}
```

Check it in the database:

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

Expect: 1 row, Tailspin Toys.

#### 2. A failed call, with its code

A call that fails: the listener logs a Warning with its ARX code; the code that caught it logs the exception.

In scope: `data` (IArxDataService), `logger` (ILogger).

```csharp
using var callLog = new ArxCallLog(logger);

var nobody = new ArxEntity("account") { RecordId = ArxRecordId.Of("id", 999999999L) };
nobody["name"] = "Nobody";

try
{
    await data.UpdateAsync(nobody);
}
catch (ArxException exception) when (exception.Code == ArxErrorCode.RecordNotFound)
{
    logger.LogError(exception, "Renaming account {AccountId} failed: {Code}, transient: {IsTransient}",
        999999999L, ArxException.FormatCode(exception.Code), exception.IsTransient);
}
```

Check it in the database:

```sql
SELECT count(*) FROM account WHERE name = 'Nobody';
```

Expect: 0 (the update found no account to change).

#### 3. Only what production needs

In production, log failures only: a create that works writes nothing; an update that fails writes its line.

In scope: `data` (IArxDataService), `logger` (ILogger).

```csharp
using var callLog = new ArxCallLog(logger, logSuccessfulCalls: false);

var account = new ArxEntity("account");
account["name"] = "Wingtip Toys";
account["account_number"] = "ACC-61002";
await data.CreateAsync(account);

Console.WriteLine("created Wingtip Toys: nothing logged");

var gone = new ArxEntity("account") { RecordId = ArxRecordId.Of("id", 999999999L) };
gone["name"] = "Gone";

try
{
    await data.UpdateAsync(gone);
}
catch (ArxException exception) when (exception.Code == ArxErrorCode.RecordNotFound)
{
    Console.WriteLine(exception.Message);
}
```

Check it in the database:

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

Expect: 1 row, Wingtip Toys.

### Remember resolved tables, or preload them

Advanced case 10 of Create.

The three ways to set up a service, from the least code to the fastest, in three scenarios: without a catalog, reading the tables on every call; without a catalog, remembering what was read (RememberResolvedTables); and the tables preloaded into a catalog once, at startup.

Good to know:

**Re-read (the default).** Every call reads its tables from the database first: always current, one more round trip per call. Measured on PostgreSQL, a create or an update by id took 5–7 times as long as with a catalog (the benchmark project's NoCatalog results).

**Remembered.** `RememberResolvedTables = true`: each table is read the first time a call needs it, then kept for the service's lifetime, so later calls were level with a catalog. A schema change after that needs a new service; keep one service for the application (a singleton) so the tables are read once.

**Preloaded.** `ArxPreLoadTable` reads the tables once, at startup, into a catalog: no call reads the database's catalog, and a table left out is refused (ARX1016) instead of resolved. It is the setup for an application that grows past simple CRUD (Create Advanced6 shows its options).

Usings:

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

#### 1. Re-read on every call (the default)

The default: both creates read the account table first, so each sees the table as it is now.

In scope: `connectionString` (string).

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

var first = await service.CreateAsync(Account("Re-read 1", "ACC-N081"));
var second = await service.CreateAsync(Account("Re-read 2", "ACC-N082"));

Console.WriteLine($"created {first} and {second}; the table was read twice");
```

Defined in the sample, used above:

```csharp
private static ArxEntity Account(string name, string number)
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    return account;
}
```

Check it in the database:

```sql
SELECT account_number, name
FROM account
WHERE account_number IN ('ACC-N081', 'ACC-N082');
```

Expect: 2 rows.

#### 2. Remember what was read

Remembered: the first call reads the account table, the later ones use what it read.

In scope: `connectionString` (string).

```csharp
// One service for the application's lifetime, so the tables are read once.
var service = new ArxPgDbService(connectionString) { RememberResolvedTables = true };

var id = await service.CreateAsync(Account("Remembered", "ACC-N083"));

var change = new ArxEntity("account");
change.RecordId = id;
change["employee_count"] = 12;
await service.UpdateAsync(change);

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

Console.WriteLine($"{read["name"]}, {read["employee_count"]} employees; the table was read once, by the create");
```

Check it in the database:

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

Expect: 1 row, Remembered, 12.

#### 3. Preload a catalog at startup

Preloaded: the tables read once, at startup, into a catalog the service is created with.

In scope: `connectionString` (string).

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

ArxSchemaCatalog schema = await new ArxPreLoadTable(tables).LoadAsync(connectionString);

var service = new ArxPgDbService(connectionString, schema);

var id = await service.CreateAsync(Account("Preloaded", "ACC-N084"));

Console.WriteLine($"created {id}; no call reads the database's catalog");
```

Check it in the database:

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

Expect: 1 row, Preloaded.