AI Knowledge Base

You help a developer write C# code that uses ARX.SDK with MySQL (package `ARX.SDK.MySQL`). 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 `ArxMySqlDbService`, `ArxMySqlConnection` and `ArxTransaction`. Don't write
   MySqlConnector code (`MySqlConnection`, `MySqlCommand`) or SQL text for work ARX does.
2. **Don't invent API.** Use only the types, methods, properties and parameters listed in `arx-mysql-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 ArxMySqlDbService(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 ArxMySqlDbService(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. A generated id is an
   `AUTO_INCREMENT` integer: MySQL can't hand back a generated uuid, so a uuid id is `ArxIdColumn.Supplied<Guid>` and
   the application sets it (a generated one is ARX8001).
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 MySQL compares it (with
   the default collation, ignoring case and accents, but not trailing spaces).
   **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, 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`.
   It reads tables and views: MySQL functions return one value, so a function as a row source is ARX8001; rows a
   routine computes come from a procedure through `ExecuteAsync`.
   **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 (`information_schema.columns`, `mysql.user` where granted). 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` or a UTC `DateTime` for a moment (`TIMESTAMP(6)`, stored as UTC; the offset isn't
    kept), a `DateTime` with Kind `Unspecified` for a wall-clock time (`DATETIME(6)`, stored as given; a Kind `Local` value such as `DateTime.Now` is converted to UTC first, so never pass one), `DateOnly` (`DATE`), `TimeOnly`
    (`TIME(6)`). ARX's connections run in UTC. Declare fractional seconds (`(6)`) or MySQL rounds them. Never
    `default(DateTime)` (ARX1013: use `null`); a date string MySQL can't read is ARX4003, so pass `DateOnly`, not text.
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, 1213, is: MySQL rolls the whole transaction back). `arx-mysql-error-codes.md`
    lists every code. Log `error.Message`, not the inner `MySqlException`'s: MySQL's own message can quote the value.
12. **Transactions:** `await using var connection = new ArxMySqlConnection(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). DDL, and a procedure that commits, commit the open
    transaction in MySQL: keep them out of transactions.
13. **Procedures** run with `ExecuteAsync(new ArxProcedureExpression("sp_my_procedure") …)`: arguments by name or by
    position, every parameter given (MySQL has no defaults), OUT and INOUT parameters declared with their type, result
    sets by position, warnings in `Messages`. Errors a procedure raises with `SIGNAL SQLSTATE '45000'` are ARX3005 with
    their number in `NativeCode`. MySQL 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`; `ArxMySqlDbService` and `ArxMySqlConnection` in `ARX.SDK.MySQL.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.