MigsLab SQL Server · Work with AI
AI Instructions
Copyable rules for configuring AI coding assistants to use ARX.SDK accurately and consistently.
An instruction file tells a coding assistant, in every conversation, how the project works. These are for a project that uses ARX.SDK 1.0.0-rc.1 with SQL Server (ARX.SDK.SQL): they name the provider, send the assistant to the knowledge files before it writes code, allow only the API those files list, and ask it to build and check its work. Each is generated from the same rules as the knowledge base and the skill; copy the one for your tool into the file named above it.
What the instructions require
- The right provider: the package, service and knowledge files of SQL Server, not another database's.
- Read before writing: the provider's guide and types file, then the operation's file and its closest verified sample.
- No invented API: only types, constructors, properties and methods the types file lists; anything else is reported as not offered.
- Verify: build, run the tests, check every ARX call against the types file, and say what could not be checked.
- Keep them current: when the ARX.SDK version changes, replace the knowledge files or the skill and update the version in the instructions.
Claude Code
CLAUDE.md: Add to the project's CLAUDE.md. With the arx-sdk skill in .claude/skills/arx-sdk, Claude Code loads the skill when it works on ARX.SDK code; this section makes the project's provider and the verification steps explicit.
## Data access: ARX.SDK
This project uses ARX.SDK 1.0.0-rc.1 with SQL Server (package `ARX.SDK.SQL`, service `ArxMsSqlDbService`).
- Use the `arx-sdk` skill for any code that reads or writes the database. Its references for this provider are in
`.claude/skills/arx-sdk/references/sql/`: read `arx-sql-guide.md` and `arx-sql-types.md` first.
- Use only the ARX types and members listed in `arx-sql-types.md`. If something isn't there, say ARX doesn't
offer it; don't invent a name.
- Start from the closest verified sample in the operation's file (`arx-sql-create.md` and the others).
- After changing ARX code: `dotnet build`, run the tests, and check every ARX call against the types file.
- When the ARX.SDK package version changes, download the skill again and update the version above.
GitHub Copilot
.github/copilot-instructions.md: Copilot's repository-wide instructions, read with every request in the repository.
## Data access: ARX.SDK
This project uses ARX.SDK 1.0.0-rc.1 with SQL Server (package `ARX.SDK.SQL`, service `ArxMsSqlDbService`). Its knowledge
files, generated from the library and its verified samples, are in `docs/arx-sdk/references/sql/`; the files named
below are there. Read `arx-sql-guide.md` and `arx-sql-types.md` before writing data access code,
and the operation's file (`arx-sql-create.md`, `-create-multiple.md`, `-update.md`, `-update-multiple.md`,
`-delete.md`, `-delete-multiple.md`, `-retrieve.md`, `-retrieve-multiple.md`, `-execute.md`) for its samples.
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.
Before finishing: `dotnet build`, run the tests, and check every ARX call against `arx-sql-types.md`. When the
ARX.SDK package version changes, replace the knowledge files with the new version's and update this section.
.github/instructions/arx-sdk.instructions.md: Applied to C# files only (applyTo). On GitHub.com, path-specific instructions are used by Copilot's cloud agent and code review; editors such as VS Code use them in chat.
---
applyTo: "**/*.cs"
---
# ARX.SDK data access
This project uses ARX.SDK 1.0.0-rc.1 with SQL Server (package `ARX.SDK.SQL`, service `ArxMsSqlDbService`). Its knowledge
files are in `docs/arx-sdk/references/sql/`; the files named below are there.
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.
Check every ARX call against `arx-sql-types.md`, and build before finishing.
Cursor
.cursor/rules/arx-sdk.mdc: A Cursor project rule (.mdc, in .cursor/rules), attached automatically when a C# file is in context. Cursor also reads AGENTS.md.
---
description: Writing or changing data access code that uses ARX.SDK (ARX.SDK.SQL)
globs: **/*.cs
alwaysApply: false
---
# ARX.SDK data access
This project uses ARX.SDK 1.0.0-rc.1 with SQL Server (package `ARX.SDK.SQL`, service `ArxMsSqlDbService`). Its knowledge
files are in `docs/arx-sdk/references/sql/`; the files named below are there. Read `arx-sql-guide.md` and
`arx-sql-types.md` before writing data access code.
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.
Check every ARX call against `arx-sql-types.md`, and build before finishing.
General AI coding assistants
AGENTS.md: For agents that read AGENTS.md (Codex, Cursor, GitHub Copilot's coding agent, Gemini CLI and others; Claude Code too when the project has no CLAUDE.md). Keep the arx-sdk folder from the skill package in the repository where the agent can open it.
## Data access: ARX.SDK
This project uses ARX.SDK 1.0.0-rc.1 with SQL Server (package `ARX.SDK.SQL`, service `ArxMsSqlDbService`). Its knowledge
files, generated from the library and its verified samples, are in `docs/arx-sdk/references/sql/`; the files named
below are there. Read `arx-sql-guide.md` and `arx-sql-types.md` before writing data access code,
and the operation's file (`arx-sql-create.md`, `-create-multiple.md`, `-update.md`, `-update-multiple.md`,
`-delete.md`, `-delete-multiple.md`, `-retrieve.md`, `-retrieve-multiple.md`, `-execute.md`) for its samples.
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.
Before finishing: `dotnet build`, run the tests, and check every ARX call against `arx-sql-types.md`. When the
ARX.SDK package version changes, replace the knowledge files with the new version's and update this section.
An assistant's own custom instructions (a chat assistant's project or profile instructions): paste the rules alone.
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.Where the knowledge files go
- Claude Code: install the arx-sdk skill in
.claude/skills/arx-sdk/; theCLAUDE.mdsection points at its references. - AGENTS.md, GitHub Copilot and Cursor: the templates expect the skill's folder in
docs/arx-sdk/(itsreferences/hold the knowledge files). Put it elsewhere if you like and change the path in the instructions. - Chat assistants: add the knowledge base files to the project or conversation, with the rules above as its instructions.
Keep them current
The instructions name ARX.SDK 1.0.0-rc.1. When the project moves to a newer version, copy them again from this page (they are regenerated with every release of these pages), replace the knowledge files or the skill, and keep any rules of your own in a section of your own so the next update is a plain replacement.