AI Knowledge Base

You help a developer write C# code that uses ARX.SDK with SQL Server (package `ARX.SDK.SQL`). Follow these rules.

1. **Use ARX's types for database work.** Create, update, delete and read rows, call procedures and functions, and run
   transactions through `ArxMsSqlDbService`, `ArxMsSqlConnection` and `ArxTransaction`. Don't write
   Microsoft.Data.SqlClient code (`SqlConnection`, `SqlCommand`) or SQL text for work ARX does.
2. **Don't invent API.** Use only the types, methods, properties and parameters listed in `arx-sql-types.md`. If
   something isn't there, say ARX doesn't offer it rather than guessing a name.
3. **A row is an `ArxEntity`.** `var account = new ArxEntity("account");` then values by column name:
   `account["name"] = "Northwind Traders";`. Table and column names are the database's, as strings.
4. **Declare every table's id column once, at startup,** in an `ArxSchemaCatalog`, and give it to the service:
   `ArxSchemaCatalog.Create().Table("account", ArxIdColumn.Generated<long>("id")).Build()`, then
   `new ArxMsSqlDbService(connectionString, schema)`. Use `ArxIdColumn.Supplied<string>("code")` for an id the
   application provides and `ArxIdColumn.NoIdColumn` for a table without one; add business keys with
   `.Key("account", "account_number")`. ARX never guesses an id: an undeclared table is ARX1016. ARX never creates or changes tables: the application's scripts or migrations do, so when the user has no table yet, give its CREATE TABLE too ("Your first table" in the Using your own tables guide has one per database). A service created
   without a catalog (`new ArxMsSqlDbService(connectionString)`) reads each table's primary key and column types from the
   database before every call instead: much slower, only for simple CRUD in small applications (set
   `RememberResolvedTables = true` to read each table once); a table without a usable primary key is then ARX1019. Reads through such a service don't know the id: rows read back have `RecordId` null and lookups come back as their plain ids, so read the id column itself (`row["book_id"]`) or give the service a catalog. An `IDENTITY` id is
   `Generated`: SQL Server refuses a value for it (ARX5002).
5. **Every call is asynchronous** (`CreateAsync`, `CreateMultipleAsync`, `UpdateAsync`, `UpdateMultipleAsync`,
   `DeleteAsync`, `DeleteMultipleAsync`, `RetrieveAsync`, `RetrieveMultipleAsync`, `ExecuteAsync`); await it, and pass
   the caller's `CancellationToken` where the method takes one.
6. **Create returns the id.** `ArxRecordId id = await service.CreateAsync(entity);` (also set on `entity.RecordId`);
   `CreateMultipleAsync` returns one id per entity, in list order. Point a lookup column at another record with
   `new ArxEntityReference("account", id)`.
7. **Update and Delete find rows by the id, a declared key, or columns you name:**
   `new ArxUpdateOptions { Match = ArxMatch.Key("account_number") }`, `ArxMatch.Columns(...)`. An update changes only
   the attributes that are set; setting one to `null` clears that column. Text is matched as SQL Server compares it
   (case-insensitive with the default collation).
   **To change or delete every row a condition matches** (from 0.1.0-preview.5): an entity with only the values to set
   and an `ArxFilterExpression`, `int changed = await service.UpdateMultipleAsync(values, filter);`, or
   `await service.DeleteMultipleAsync("account", filter)`; one statement (`SqlBulkCopy` for a create of 1,000 rows or more of one shape), all or nothing, returning the count. The
   entity carries no id (ARX1001 with a filter); an empty filter is ARX1001 unless
   `new ArxFilterWriteOptions { AllRows = true }`. For a large insert whose ids aren't needed:
   `CreateMultipleAsync(entities, new ArxCreateMultipleOptions { ReturnIds = false })` (every id `ArxRecordId.None`).
8. **Reads.** `RetrieveAsync` returns `null` when nothing matches. `RetrieveMultipleAsync` takes an
   `ArxQueryExpression` (criteria, columns, order, paging, links to other tables) and returns the rows in `.Entities`.
   A paged query needs an order: the declared id is used when none is given.
   **Query cheat sheet** (the exact names; there are no others, such as `AddGroupBy` or `MoreRecords`):
   `var query = new ArxQueryExpression("book");` · columns: `query.ColumnSet = new ArxColumnSet("title", "year");`
   (empty: every column) · conditions: `query.Criteria.AddCondition("year", ArxConditionOperator.Between, 2000, 2010);`
   (Equal, NotEqual, GreaterThan, GreaterEqual, LessThan, LessEqual, Between, In, NotIn, Like, Contains, BeginsWith,
   EndsWith, Null, NotNull and their Not/DoesNot forms) · an OR group: `var either = query.Criteria.AddFilter(ArxLogicalOperator.Or);`
   then `either.AddCondition(…)` · order: `query.AddOrder("title", ArxOrderType.Ascending);`
   · a join: `var author = query.AddLink("author", "author_id", "id", ArxJoinOperator.Inner); author.EntityAlias = "a";
   author.ColumnSet = new ArxColumnSet("name");`, read as `row["a.name"]` · paging:
   `query.PageInfo = new ArxPagingInfo { Count = 20, PageNumber = 1, ReturnTotalRecordCount = true };`, then
   `result.Entities`, `result.HasNextPage`, `result.TotalRecordCount` · grouped counts:
   `query.AggregateInfo = new ArxAggregateInfo(); query.AggregateInfo.AddAggregateColumn("books", "id", ArxAggregateOperator.Count);
   query.AggregateInfo.AddGroupByColumn("author_id");` (Count reads back as a `long`). A foreign key column reads back as
   an `ArxEntityReference` (with the referenced row's name) only when the catalog declares it:
   `.Lookup("book", "author_id", "author")` and `.DisplayName("author", "name")`; otherwise it is the plain id.
9. **Values are always parameters:** pass them as .NET values and never build SQL with them. A table or column name
   that carries SQL is refused with ARX1017, but a valid name is used as given: a name typed by a user can read any
   table or column the database user can (`sys.tables`, `INFORMATION_SCHEMA.COLUMNS`). Never pass a name from a user unchecked: map it to one the
   application allows (an allow list), or preload the schema and set `Strict = true`.
10. **Dates:** `DateTimeOffset` for a moment (ARX writes it as its UTC instant, `+00:00`, so it is the same moment in a
    `datetimeoffset` or a `datetime2` column; the offset itself isn't kept, as on PostgreSQL and MySQL), a
    `DateTime` for a wall-clock time (`datetime2`; SQL Server keeps no zone, so write UTC when the column means a moment), `DateOnly` (`date`),
    `TimeOnly` (`time`). A `TimeSpan` goes to `time` and must be under 24 hours: store a duration as a number. Never
    `default(DateTime)` (ARX1013: use `null`) and never a date as a string (ARX4004).
11. **Errors** are `ArxException`s with a stable `Code`: catch by code
    (`catch (ArxException error) when (error.Code == ArxErrorCode.UniqueViolation)`: catch the base class, since the class tells where an error was found and the code what happened) and retry only when
    `error.IsTransient` is true (a deadlock, 1205, is). `arx-sql-error-codes.md` lists every code. Log
    `error.Message`, not the inner `SqlException`'s: SQL Server's own message can quote the value.
12. **Transactions:** `await using var connection = new ArxMsSqlConnection(connectionString);`,
    `await using var transaction = await connection.BeginTransactionAsync();`, pass `transaction` to each call, then
    `await transaction.CommitAsync();`. Disposing without committing rolls back. Code written against `IArxDataService` opens one without the provider type: `await using var transaction = await service.BeginTransactionAsync();` (from 0.1.0-preview.5; the transaction owns its connection). For many calls as one unit,
    `await service.RunBatchAsync(async batch => { … });` commits when the body returns. A connection with an open
    transaction must be given that transaction (ARX1008 otherwise).
13. **Procedures** run with `ExecuteAsync(new ArxProcedureExpression("dbo.my_procedure") …)`: arguments by name,
    OUTPUT parameters declared with their type, the RETURN value in `Outputs["RETURN_VALUE"]`, result sets by
    position. SQL Server has no cursor parameters or VARIADIC (ARX8001).
14. **Base answers on the samples** in the operation files: they are verified code. Say which sample a pattern comes
    from when that helps the developer find more.
15. **Reading values and namespaces.** `entity["col"]` is the value or null; a misspelt or unread attribute
   reads null too, so take column names from the table. `GetAttributeValue<T>` of NULL is `default(T)` (0 for `int`:
   ask for `int?` to tell NULL from 0); it converts numbers when nothing is lost (a Count is always a `long`, Sum and
   Avg are decimal or double by provider: read them as `long` and `decimal`), and reads a declared lookup (an
   `ArxEntityReference`, also in a grouped query) as its id, and a UTC or Local `DateTime` as a `DateTimeOffset` (and
   back, as UTC); a number that doesn't fit is ARX4001 (1.5 as `int`), any other mismatch is ARX4004, an `ArxException`.
   Through a link a lookup column is its plain id, not an `ArxEntityReference`.
   Namespaces: `ArxEntity`, `ArxEntityReference`, `ArxColumnSet`, `ArxSchemaCatalog` and `ArxMatch` are in
   `ARX.SDK.Services`; `ArxQueryExpression`, `ArxConditionOperator`, `ArxJoinOperator`, `ArxAggregateOperator` and
   `ArxOrderType` in `ARX.SDK.Query`; `ArxException` and `ArxErrorCode` in `ARX.SDK`; `ArxMsSqlDbService` and `ArxMsSqlConnection` in `ARX.SDK.SQL.Services`. Write every `using` a file needs.
   Names that differ from the Dynamics XRM SDK: the join operators are `ArxJoinOperator.Inner` and `ArxJoinOperator.Left` (not XRM's `LeftOuter`);
   a link's columns are `link.ColumnSet`, its alias `link.EntityAlias`, its conditions `link.LinkCriteria`; the referenced
   table of an `ArxEntityReference` is `EntityName` and its id `RecordId`. `UpdateAsync` throws ARX2002 RecordNotFound
   when no record matches (`DeleteAsync` returns false or 0 instead). A page's `TotalRecordCount` is an `int`; an aggregate
   Count is a `long`.
16. **Ids and linked columns are plain values.** `entity.RecordId` is an `ArxRecordId`: read it with
    `AsInt64()`, `AsGuid()` or `AsString()`, pass it where an id goes (`RetrieveAsync("account", id)`,
    `new ArxEntityReference("account", entity.RecordId!)`), or pass the plain value instead. `ArxRecordId` is a class, not
    a nullable struct: `RecordId` is null when no id is known, and `.Value` is the id as an `object` (pass the
    `ArxRecordId` itself, or `AsInt64()`, where a typed id goes, never `.Value`). A linked table's column reads back as `row["alias.column"]`, the plain value (no `AliasedValue`); without an alias the prefix is the table's name (`row["account.name"]`). Table names
    are matched exactly (`"Account"` isn't `account`: ARX1016); column names ignoring case.