# Delete with ARX.SDK on MySQL

## What a delete does

| | |
|---|---|
| **By id** | `DeleteAsync("account", id)`: the id CreateAsync returned, or a `long`, `Guid` or `string` of the declared id type. Returns `true` when a record went (a `BEFORE DELETE` trigger can refuse a row only by raising an error: ARX3005) |
| **By key or columns** | `DeleteAsync(entity, new ArxDeleteOptions { Match = ArxMatch.Key(...) })`: the entity carries the values to match, other attributes are ignored (so an entity you created or read can be passed as it is). Returns the count; by columns it may be many. Text is matched as MySQL compares it (with the default collation, ignoring case and accents, not trailing spaces). Many entities go in one statement (`DELETE t FROM t JOIN (VALUES ROW(…), …)` the values) |
| **Nothing to delete** | Not an error: `false` or `0`. A retry, or two users deleting at once, is a normal outcome |
| **Strict** | `RequireSingleRow = true`: exactly one row per entity, else ARX2002 or ARX2003, and nothing is deleted |
| **A referenced row** | `ARX3002 ForeignKeyViolation`, naming the table that still references it. Cascading is the schema's choice (`ON DELETE CASCADE`), and the count covers only the table you deleted from. InnoDB checks a foreign key row by row: a parent and its child deleted in one call fail when the parent's id comes first (ARX sends ids in ascending order), so delete the children first |
| **Soft delete** | Not built in: it's an update of a status column (RealCase4) |
| **Without a catalog** | By the id, the table's primary key is read from the database first (one more round trip per call, so much slower than with a catalog: meant for simple CRUD and small applications); `ARX1019` when it has none ARX can use. By a key or columns, any table (Basic4) |
| **Rows by a condition** | Delete finds rows by equal values: the id, a declared key, or columns you give values for (`ArxMatch.Columns`). To delete every row that meets a condition (`amount > 100`), call `DeleteMultipleAsync(table, filter)`: one call, all or nothing, from 0.1.0-preview.5 (DeleteMultiple, Basics). Before that version: read the rows with `RetrieveMultipleAsync` and pass them to `DeleteMultipleAsync`. |

## Reference

### Error codes a delete can raise

| Code | When |
|---|---|
| ARX1001 InvalidArgument | An id of the wrong type or one that can't exist (0, negative for a generated id, empty Guid or text, `ArxRecordId.None`), a NoIdColumn table deleted by id, a missing or NULL match value, a key the table doesn't declare, duplicates in a strict call |
| ARX1002, ARX1003 | Missing or invalid table or column name |
| ARX1017 SuspectedInjection | A table or match column name carrying SQL (a quote, `;`, a comment, `=`, an attack word after a space); nothing sent to the database (RealCase5) |
| ARX1016 IdNotDeclared | Deleting by id or key from a table nobody declared |
| ARX2002, ARX2003 | Strict calls only: an entity matched nothing, or several rows (nothing deleted) |
| ARX1008 InvalidConnection | A connection with an open transaction, given without that transaction |
| ARX3002 ForeignKeyViolation | The row is still referenced (MySQL has no deferred constraints, so it fails at the statement, never at commit) |
| ARX5001, ARX6001, ARX7001, ARX7003, ARX8001 | Missing table, no permission, a deadlock (the whole transaction rolled back), lock or command timeout, a view MySQL can't delete from (several base tables, GROUP BY or aggregates) |

## The samples

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

### Delete one record

Basics 1 of Delete.

The smallest delete: one record, in three scenarios. The usual way first (the id CreateAsync returned), then a text id, then the entity itself.

Usings:

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

#### 1. By the id Create returned

The usual way: the table and the id. True when a record was deleted.

In scope: `connectionString` (string).

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

var account = new ArxEntity("account");
account["name"] = "Temporary account";
account["account_number"] = "ACC-D101";
ArxRecordId id = await service.CreateAsync(account);

bool deleted = await service.DeleteAsync("account", id);

Console.WriteLine($"deleted = {deleted}");
```

Defined in the sample, used above:

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

Check it in the database:

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

Expect: 0.

#### 2. A text id

A text id: delete a country by its code.

In scope: `connectionString` (string).

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

var finland = new ArxEntity("country");
finland["code"] = "FI";
finland["name"] = "Finland";
await service.CreateAsync(finland);

bool deleted = await service.DeleteAsync("country", "FI");

Console.WriteLine($"country FI deleted = {deleted}");
```

Check it in the database:

```sql
SELECT count(*)
FROM country
WHERE code = 'FI';
```

Expect: 0.

#### 3. The entity itself

The entity itself: an entity you created (or read) carries its id, so you can pass it back to delete it. The row is found by the table's declared id only ("id" in the schema above), never by the other attributes.

In scope: `connectionString` (string).

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

var account = new ArxEntity("account");
account["name"] = "Another temporary account";
account["account_number"] = "ACC-D103";
await service.CreateAsync(account); // sets account.RecordId to the new row's id

// Finds the row by account.RecordId: DELETE FROM account WHERE id = <that id>.
// "name" and "account_number" are not part of the match and are ignored: no other account is deleted because
// it has the same name or number, and account_number is not a key (it isn't declared as one in the schema).
// To delete by account_number instead, ask for it: new ArxDeleteOptions { Match = ArxMatch.Columns("account_number") }
// (see Basic3_HowToFindTheRows on this page).
int deleted = await service.DeleteAsync(account);

Console.WriteLine($"{deleted} row deleted");
```

Check it in the database:

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

Expect: 0 (the account created above is gone). account_number is used here only to find the row by hand, since you don't know its generated id; the delete itself matched on id.

### Nothing to delete is not an error

Basics 2 of Delete.

Deleting something that is already gone is a normal outcome, not an error (a retry, or two users at once). Two scenarios: one record (false), and a list (0).

Usings:

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

Set up by the sample before its scenarios:

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

#### 1. One record

Delete twice: the second call returns false, so a retry is safe.

In scope: `service` (ArxMySqlDbService).

```csharp
var account = new ArxEntity("account");
account["name"] = "Deleted twice";
account["account_number"] = "ACC-D301";
var id = await service.CreateAsync(account);

var first = await service.DeleteAsync("account", id);
var second = await service.DeleteAsync("account", id);

Console.WriteLine($"first {first}, then {second}");
```

Check it in the database:

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

Expect: 0.

#### 2. A list

A list of ids none of which exists: 0. An empty list is 0 too, without touching the database.

In scope: `service` (ArxMySqlDbService).

```csharp
var none = await service.DeleteMultipleAsync("account", new List<long> { 999_999_994, 999_999_993 });
var empty = await service.DeleteMultipleAsync("account", new List<long>());

Console.WriteLine($"{none} deleted, empty list {empty}");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE id IN (999999994, 999999993);
```

Expect: 0.

### Delete by key, by compound key, by columns

Basics 3 of Delete.

How a delete finds its rows when you don't have the id (ArxMatch), in four scenarios: by a declared key, by a compound key on a table with no id, by columns on a table with no id and no key, and strict: exactly one row or nothing.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var service = new ArxMySqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id")).Key("account", "account_number")
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
    .Table("activity_log", ArxIdColumn.NoIdColumn)
    .Build());
```

#### 1. By a key

By a declared key: the account number.

In scope: `service` (ArxMySqlDbService).

```csharp
var account = new ArxEntity("account");
account["name"] = "Found by number";
account["account_number"] = "ACC-D401";
await service.CreateAsync(account);

var byNumber = new ArxEntity("account");
byNumber["account_number"] = "ACC-D401";

int deleted = await service.DeleteAsync(byNumber, new ArxDeleteOptions { Match = ArxMatch.Key("account_number") });

Console.WriteLine($"{deleted} deleted");
```

Check it in the database:

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

Expect: 0.

#### 2. By a compound key

A price has no id: region and SKU together find it.

In scope: `service` (ArxMySqlDbService).

```csharp
var poland = new ArxEntity("country");
poland["code"] = "PL";
poland["name"] = "Poland";
await service.CreateAsync(poland);

foreach (var sku in new[] { "SKU-D401", "SKU-D402" })
{
    var price = new ArxEntity("price_list");
    price["region"] = "PL";
    price["sku"] = sku;
    price["unit_price"] = 3.00m;
    price["valid_from"] = new DateOnly(2026, 1, 1);
    await service.CreateAsync(price);
}

var one = new ArxEntity("price_list");
one["region"] = "PL";
one["sku"] = "SKU-D401";

int deleted = await service.DeleteAsync(one, new ArxDeleteOptions { Match = ArxMatch.Key("region", "sku") });

Console.WriteLine($"{deleted} deleted");
```

Check it in the database:

```sql
SELECT sku
FROM price_list
WHERE region = 'PL';
```

Expect: 1 row, SKU-D402.

#### 3. By columns

The activity log has no id and no key: delete by any columns. Every matching row goes.

In scope: `service` (ArxMySqlDbService).

```csharp
foreach (var action in new[] { "test login", "test login", "test logout" })
{
    var entry = new ArxEntity("activity_log");
    entry["action"] = action;
    await service.CreateAsync(entry);
}

var logins = new ArxEntity("activity_log");
logins["action"] = "test login";

int deleted = await service.DeleteAsync(logins, new ArxDeleteOptions { Match = ArxMatch.Columns("action") });

Console.WriteLine($"{deleted} log entries deleted");
```

Check it in the database:

```sql
SELECT action, count(*)
FROM activity_log
WHERE action LIKE 'test log%'
GROUP BY action;
```

Expect: 1 row, test logout, 1.

#### 4. Strict

Strict: RequireSingleRow refuses a delete that would remove more than one row, and deletes nothing.

In scope: `service` (ArxMySqlDbService).

```csharp
foreach (var action in new[] { "strict delete", "strict delete" })
{
    var entry = new ArxEntity("activity_log");
    entry["action"] = action;
    await service.CreateAsync(entry);
}

var entries = new ArxEntity("activity_log");
entries["action"] = "strict delete";

try
{
    await service.DeleteAsync(entries, new ArxDeleteOptions { Match = ArxMatch.Columns("action"), RequireSingleRow = true });
}
catch (ArxException error) when (error.Code == ArxErrorCode.MultipleRecordsMatched)
{
    Console.WriteLine($"{error.Message}");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM activity_log
WHERE action = 'strict delete';
```

Expect: 2: nothing was deleted.

### Delete without a catalog

Basics 4 of Delete.

Deletes on a service created with only a connection string, in three scenarios: by the id, read from the table's primary key; a table with no primary key, by columns; and the same table by an id, refused with ARX1019 before anything is deleted.

Good to know:

Every call reads its table from the database first, which makes it much slower than a service with a catalog (Create Basic6 says when that is fine).

Usings:

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

Set up by the sample before its scenarios:

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

#### 1. By the id

The usual way: the table and the id Create returned.

In scope: `service` (ArxMySqlDbService).

```csharp
var account = new ArxEntity("account");
account["name"] = "Woodgrove Bank";
account["account_number"] = "ACC-N041";
ArxRecordId id = await service.CreateAsync(account);

bool deleted = await service.DeleteAsync("account", id);

Console.WriteLine($"account {id.AsInt64()} deleted: {deleted}");
```

Check it in the database:

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

Expect: 0.

#### 2. A table with no primary key, by columns

activity_log has no primary key: find the rows by a column, and every matching row goes.

In scope: `service` (ArxMySqlDbService).

```csharp
for (var i = 0; i < 2; i++)
{
    var entry = new ArxEntity("activity_log") { IdColumn = ArxIdColumn.NoIdColumn };
    entry["action"] = "opened the trial";
    await service.CreateAsync(entry);
}

var match = new ArxEntity("activity_log");
match["action"] = "opened the trial";

int deleted = await service.DeleteAsync(match, new ArxDeleteOptions { Match = ArxMatch.Columns("action") });

Console.WriteLine($"{deleted} log entries deleted");
```

Check it in the database:

```sql
SELECT count(*)
FROM activity_log
WHERE action = 'opened the trial';
```

Expect: 0.

#### 3. A table with no primary key, by an id

By an id on that table: there is no id column to find it by, so ARX1019, and nothing is deleted.

In scope: `service` (ArxMySqlDbService).

```csharp
try
{
    await service.DeleteAsync("activity_log", 1L);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.TableNotResolvable)
{
    Console.WriteLine($"refused: {error.Message}");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM activity_log;
```

Expect: the same count as before the call.

### Remove a contact by id or by email

Real case 1 of Delete.

Removing contacts, in two scenarios: from the contact screen (by id), and an "unsubscribe and forget me" request that only gives the email address (the declared key).

Usings:

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

Set up by the sample before its scenarios:

```csharp
var first = new ArxEntity("contact");
first["first_name"] = "Rosa";
first["last_name"] = "Delgado";
first["email"] = "rosa.delgado@example.com";
var second = new ArxEntity("contact");
second["first_name"] = "Omar";
second["last_name"] = "Haddad";
second["email"] = "omar.haddad@example.com";
var ids = await service.CreateMultipleAsync(new List<ArxEntity> { first, second });
```

#### 1. From the contact screen, by id

The screen has the contact's id.

In scope: `service` (ArxMySqlDbService), `id` (ArxRecordId).

```csharp
var deleted = await service.DeleteAsync("contact", id);
Console.WriteLine($"deleted: {deleted}");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email = 'rosa.delgado@example.com';
```

Expect: 0.

#### 2. A forget-me request, by email

A "forget me" request has only the email: delete by the key, strictly one row.

In scope: `service` (ArxMySqlDbService).

```csharp
var request = new ArxEntity("contact");
request["email"] = "omar.haddad@example.com";

var deleted = await service.DeleteAsync(request, new ArxDeleteOptions { Match = ArxMatch.Key("email"), RequireSingleRow = true });
Console.WriteLine($"{deleted} contact forgotten");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email = 'omar.haddad@example.com';
```

Expect: 0.

### Undo an import of 5,000 contacts

Real case 2 of Delete.

A contact import went wrong and must be undone, in two scenarios: all 5,000 imported contacts by their lead source in one call; and the same call again, which finds nothing and says so with 0.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var imported = Enumerable.Range(1, 5_000).Select(i =>
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = "Imported";
    contact["last_name"] = $"Row {i}";
    contact["email"] = $"bad-import-{i}@example.com";
    contact["lead_source"] = "Import 2026-11-08 (bad)";
    return contact;
}).ToList();
await service.CreateMultipleAsync(imported);
```

#### 1. Every contact of the bad import

The import tagged every row with its lead source: delete by that column. One statement.

In scope: `service` (ArxMySqlDbService).

```csharp
var deleted = await service.DeleteAsync(TheBadImport(), new ArxDeleteOptions { Match = ArxMatch.Columns("lead_source") });
Console.WriteLine($"{deleted} contacts deleted");
```

Defined in the sample, used above:

```csharp
private static ArxEntity TheBadImport()
{
    var match = new ArxEntity("contact");
    match["lead_source"] = "Import 2026-11-08 (bad)";
    return match;
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE lead_source = 'Import 2026-11-08 (bad)';
```

Expect: 0.

#### 2. Run the clean-up again

Running the clean-up again is safe: nothing matches, 0 deleted, no error.

In scope: `service` (ArxMySqlDbService).

```csharp
var deleted = await service.DeleteAsync(TheBadImport(), new ArxDeleteOptions { Match = ArxMatch.Columns("lead_source") });
Console.WriteLine($"{deleted} deleted the second time");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email LIKE 'bad-import-%';
```

Expect: 0.

### A referenced record: ARX3002, then delete in order

Real case 3 of Delete.

An account that contacts still point at can't be deleted, in two scenarios: the database refuses it (ARX3002, with the constraint's name); delete the contacts first, by their account, then the account.

Usings:

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

#### 1. Delete the account: refused

The account still has contacts: the foreign key refuses the delete, and names itself.

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

```csharp
try
{
    await service.DeleteAsync("account", accountId);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.ForeignKeyViolation)
{
    Console.WriteLine($"{error.Code}, constraint {error.ConstraintName}");
}
```

Check it in the database:

```sql
SELECT (SELECT count(*) FROM account WHERE account_number = 'ACC-D501') AS accounts,
       (SELECT count(*) FROM contact WHERE last_name = 'Kept') AS contacts;
```

Expect: 1, 2: still there.

#### 2. Contacts first, then the account

Delete the contacts by their account (a column match), then the account, in one batch scope: all or nothing.

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

```csharp
await service.RunBatchAsync(async scope =>
{
    var ofTheAccount = new ArxEntity("contact");
    ofTheAccount["account_id"] = new ArxEntityReference("account", accountId);

    var contacts = await scope.DeleteAsync(ofTheAccount, new ArxDeleteOptions { Match = ArxMatch.Columns("account_id") });
    var account = await scope.DeleteAsync("account", accountId);

    Console.WriteLine($"{contacts} contacts and {(account ? 1 : 0)} account deleted");
});
```

Check it in the database:

```sql
SELECT (SELECT count(*) FROM account WHERE account_number = 'ACC-D502') AS accounts,
       (SELECT count(*) FROM contact WHERE last_name = 'Closing') AS contacts;
```

Expect: 0, 0.

### Soft delete: close and restore an account

Real case 4 of Delete.

A soft delete keeps the row and marks it closed: it's an update of a status column, not a delete. Two scenarios: close an account; restore it.

Usings:

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

Set up by the sample before its scenarios:

```csharp
foreach (var number in new[] { "ACC-D601", "ACC-D602" })
{
    var account = new ArxEntity("account");
    account["name"] = "Soft deleted customer " + number;
    account["account_number"] = number;
    await service.CreateAsync(account);
}
```

#### 1. Close the account instead of deleting it

"Delete" in the app closes the account: an update of its status, by the account number.

In scope: `service` (ArxMySqlDbService).

```csharp
await service.UpdateAsync(Status("ACC-D601", "CLOSED"), new ArxUpdateOptions { Match = ArxMatch.Key("account_number") });
Console.WriteLine("ACC-D601 closed; its history stays");
```

Defined in the sample, used above:

```csharp
private static ArxEntity Status(string number, string status)
{
    var change = new ArxEntity("account");
    change["account_number"] = number;
    change["status"] = new ArxSelectionSetValue("AccountStatus", status);
    return change;
}
```

Check it in the database:

```sql
SELECT account_number, status
FROM account
WHERE account_number = 'ACC-D601';
```

Expect: 1 row, CLOSED.

#### 2. Restore it

Undo is another update.

In scope: `service` (ArxMySqlDbService).

```csharp
var byNumber = new ArxUpdateOptions { Match = ArxMatch.Key("account_number") };
await service.UpdateAsync(Status("ACC-D602", "CLOSED"), byNumber);
await service.UpdateAsync(Status("ACC-D602", "ACTIVE"), byNumber);
Console.WriteLine("ACC-D602 closed, then active again");
```

Check it in the database:

```sql
SELECT account_number, status
FROM account
WHERE account_number = 'ACC-D602';
```

Expect: 1 row, ACTIVE.

### Catch an SQL injection attempt on delete

Real case 5 of Delete.

Catching an SQL injection attempt on delete, in four scenarios. A table or match column that carries SQL is refused with ARX1017 SuspectedInjection before the database. SQL in the value to delete by, the classic `' OR '1'='1` that would empty a table when concatenated, is a parameter: it deletes nothing.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var accounts = new List<ArxEntity>();
foreach (var number in new[] { "ACC-I301", "ACC-I302", "ACC-I303" })
{
    var account = new ArxEntity("account");
    account["name"] = "Injection: keep me";
    account["account_number"] = number;
    accounts.Add(account);
}
var ids = await service.CreateMultipleAsync(accounts);
```

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

`DELETE /api/{type}/{id}` with the type used as the table: the SQL in it is refused before the database.

In scope: `service` (ArxMySqlDbService), `id` (ArxRecordId).

```csharp
var type = "account WHERE 1=1; --";

try
{
    await service.DeleteAsync(type, id);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    SecurityLog(error);
}
```

Defined in the sample, used above:

```csharp
private static void SecurityLog(ArxValidationException error) => Console.WriteLine($"SECURITY: {error.Message}");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number IN ('ACC-I301', 'ACC-I302', 'ACC-I303');
```

Expect: 3: nothing was deleted.

#### 2. SQL in the value to delete by deletes nothing

The classic: "delete the account with this number", and the user types `ACC-I301' OR '1'='1`. Concatenated into SQL it would delete every account; as a parameter it is an account number nobody has.

In scope: `service` (ArxMySqlDbService).

```csharp
var target = new ArxEntity("account");
target["account_number"] = "ACC-I301' OR '1'='1";

int deleted = await service.DeleteAsync(target, new ArxDeleteOptions { Match = ArxMatch.Key("account_number") });

Console.WriteLine($"{deleted} rows deleted");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number IN ('ACC-I301', 'ACC-I302', 'ACC-I303');
```

Expect: 3: the text was compared as a value, so it matched no account.

#### 3. The column to delete by, from the request, carries SQL

The column to delete by comes from the request: the SQL in it is refused; only allow columns from a fixed list.

In scope: `service` (ArxMySqlDbService).

```csharp
var by = "name = name OR account_number";

var target = new ArxEntity("account");
target[by] = "x";

try
{
    await service.DeleteAsync(target, new ArxDeleteOptions { Match = ArxMatch.Columns(by) });
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    SecurityLog(error);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name = 'Injection: keep me';
```

Expect: 3.

#### 4. DeleteMultiple with a table name carrying SQL

DeleteMultiple of the three ids with a table name carrying SQL: refused before the database, all three kept.

In scope: `service` (ArxMySqlDbService), `ids` (IReadOnlyList<ArxRecordId>).

```csharp
try
{
    await service.DeleteMultipleAsync("account\"; TRUNCATE account; --", ids.ToList());
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    SecurityLog(error);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE name = 'Injection: keep me';
```

Expect: 3: every account is still there.

### Replace an order's lines: delete, create, update, all or nothing

Advanced case 1 of Delete.

A customer changes an order: its old lines are deleted, the new lines created and the total updated, in one batch scope. Two scenarios: the replacement succeeds; a new line is refused, and the order is left as it was.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var customer = new ArxEntity("account");
customer["name"] = "Replace order customer";
customer["account_number"] = "ACC-D801";
var customerId = await service.CreateAsync(customer);
var product = new ArxEntity("product");
product["sku"] = "SKU-D801";
product["name"] = "Replaceable widget";
product["unit_price"] = 2.00m;
var productId = await service.CreateAsync(product);
var order = new ArxEntity("sales_order");
order["order_number"] = "SO-D801";
order["account_id"] = new ArxEntityReference("account", customerId);
order["ordered_on"] = DateTime.UtcNow;
order["total"] = 4.00m;
var orderId = await service.CreateAsync(order);
var line = new ArxEntity("order_line");
line["order_id"] = new ArxEntityReference("sales_order", orderId);
line["product_id"] = new ArxEntityReference("product", productId);
line["quantity"] = 2;
line["unit_price"] = 2.00m;
await service.CreateAsync(line);
```

#### 1. Replace the lines

Old line out, two new lines in, total updated: committed together.

In scope: `service` (ArxMySqlDbService), `orderId` (ArxRecordId), `productId` (ArxRecordId).

```csharp
await service.RunBatchAsync(scope => ReplaceAsync(scope, orderId, productId, 1, 4));
Console.WriteLine("SO-D801 now has two lines, total 10.00");
```

Defined in the sample, used above:

```csharp
private static async Task ReplaceAsync(IArxBatchScope scope, ArxRecordId orderId, ArxRecordId productId, params int[] quantities)
{
    var ofTheOrder = new ArxEntity("order_line");
    ofTheOrder["order_id"] = new ArxEntityReference("sales_order", orderId);
    await scope.DeleteAsync(ofTheOrder, new ArxDeleteOptions { Match = ArxMatch.Columns("order_id") });

    var lines = quantities.Select(quantity =>
    {
        var line = new ArxEntity("order_line");
        line["order_id"] = new ArxEntityReference("sales_order", orderId);
        line["product_id"] = new ArxEntityReference("product", productId);
        line["quantity"] = quantity;
        line["unit_price"] = 2.00m;
        return line;
    }).ToList();
    await scope.CreateMultipleAsync(lines);

    var total = new ArxEntity("sales_order");
    total.RecordId = orderId;
    total["total"] = quantities.Sum() * 2.00m;
    await scope.UpdateAsync(total);
}
```

Check it in the database:

```sql
SELECT o.total, count(l.id) AS line_count, sum(l.quantity) AS quantity
FROM sales_order o
JOIN order_line l ON l.order_id = o.id
WHERE o.order_number = 'SO-D801'
GROUP BY o.total;
```

Expect: 1 row, total 10.00, 2 lines, quantity 5.

#### 2. A refused line: nothing changes

A quantity of 0 is refused by the database: the delete before it is undone too.

In scope: `service` (ArxMySqlDbService), `orderId` (ArxRecordId), `productId` (ArxRecordId).

```csharp
try
{
    await service.RunBatchAsync(scope => ReplaceAsync(scope, orderId, productId, 3, 0));
}
catch (ArxDatabaseException error)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT o.total, count(l.id) AS line_count
FROM sales_order o
JOIN order_line l ON l.order_id = o.id
WHERE o.order_number = 'SO-D801'
GROUP BY o.total;
```

Expect: 1 row, total 10.00, 2 lines: as after scenario 1.

### A security check around deletes: probing, blocking, unblocking a false positive

Advanced case 2 of Delete.

A security check around deletes (`SecurityGuard`, in the project root), in four scenarios: a delete through an allowed list of types passes; an endpoint that forgot the list is probed, and the attacker is blocked after three attempts; and a false positive, an old client sending a harmless type name, is triaged: support unblocks the user and the endpoint maps the old name.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var guard = new SecurityGuard(blockAfter: 3);
var accounts = new List<ArxEntity>();
foreach (var number in new[] { "ACC-G301", "ACC-G302", "ACC-G303", "ACC-G304" })
{
    var account = new ArxEntity("account");
    account["name"] = $"Guarded delete {number}";
    account["account_number"] = number;
    accounts.Add(account);
}
var ids = await service.CreateMultipleAsync(accounts);
```

#### 1. A delete through the allowed types

`DELETE /api/accounts/{id}` through the allowed types: the account is deleted.

In scope: `service` (ArxMySqlDbService), `guard` (SecurityGuard), `id` (ArxRecordId).

```csharp
var result = await DeleteThroughTheListAsync(service, guard, "ana", "accounts", id);
var unknown = await DeleteThroughTheListAsync(service, guard, "ana", "account; DROP TABLE account", id);

Console.WriteLine($"ana: {result.Outcome}, deleted = {result.Value}; an unknown type: \"{unknown.ForTheUser}\" (never reaches ARX)");
```

Defined in the sample, used above:

```csharp
private static readonly Dictionary<string, string> Types = new(StringComparer.OrdinalIgnoreCase)
{
    ["accounts"] = "account",
    ["contacts"] = "contact",
};

private static Task<GuardResult<bool>> DeleteThroughTheListAsync(ArxMySqlDbService service, SecurityGuard guard, string user, string type, ArxRecordId id)
    => Types.TryGetValue(type, out var table)
        ? guard.RunAsync(user, $"delete {type}", NameSource.Code, () => service.DeleteAsync(table, id))
        : Task.FromResult(new GuardResult<bool>(GuardOutcome.Refused, false, $"404 Not Found: no type '{type}'."));
```

Check it in the database:

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

Expect: 0: deleted.

#### 2. Probing the legacy endpoint gets the user blocked

The legacy endpoint is probed three times: each probe is refused by ARX (ARX1017) and logged; then the user is blocked.

In scope: `service` (ArxMySqlDbService), `guard` (SecurityGuard), `id` (ArxRecordId).

```csharp
foreach (var probe in new[] { "account WHERE 1=1; --", "account\"; TRUNCATE account; --", "account UNION SELECT 1" })
{
    await DeleteDirectlyAsync(service, guard, "mallory", probe, id);
}

var valid = await DeleteDirectlyAsync(service, guard, "mallory", "account", id);

Console.WriteLine($"mallory: {guard.Events.Count(x => x.User == "mallory")} events logged, blocked: {guard.IsBlocked("mallory")}; a valid delete: {valid.Outcome}");
```

Defined in the sample, used above:

```csharp
private static Task<GuardResult<bool>> DeleteDirectlyAsync(ArxMySqlDbService service, SecurityGuard guard, string user, string type, ArxRecordId id)
    => guard.RunAsync(user, $"delete {type}", NameSource.UserInput, () => service.DeleteAsync(type, id));
```

Check it in the database:

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

Expect: 1: nothing was deleted, not even by the valid request after the block.

#### 3. A false positive is triaged: unblock, then map the old name

A false positive: an old mobile client sends the type as "account (archived)". The parentheses make ARX flag it as SQL, the client retries, and after three strikes the user is blocked. Support reads the log, sees the same harmless name three times, unblocks the user, and the endpoint maps the old name to its table.

In scope: `service` (ArxMySqlDbService), `guard` (SecurityGuard), `first` (ArxRecordId), `second` (ArxRecordId).

```csharp
for (var retry = 0; retry < 3; retry++)
{
    await DeleteDirectlyAsync(service, guard, "sam", "account (archived)", first);
}

Console.WriteLine($"sam blocked: {guard.IsBlocked("sam")}; the log shows: {string.Join(" | ", guard.Events.Where(x => x.User == "sam").Select(x => x.Action).Distinct())}");

// Triage: the same harmless name three times, from a known old client. Unblock, and map the old name.
guard.Unblock("sam");
Types["account (archived)"] = "account";

var afterTheFix = await DeleteThroughTheListAsync(service, guard, "sam", "account (archived)", second);

Console.WriteLine($"after the fix: {afterTheFix.Outcome}, deleted = {afterTheFix.Value}");
```

Defined in the sample, used above:

```csharp
private static readonly Dictionary<string, string> Types = new(StringComparer.OrdinalIgnoreCase)
{
    ["accounts"] = "account",
    ["contacts"] = "contact",
};

private static Task<GuardResult<bool>> DeleteThroughTheListAsync(ArxMySqlDbService service, SecurityGuard guard, string user, string type, ArxRecordId id)
    => Types.TryGetValue(type, out var table)
        ? guard.RunAsync(user, $"delete {type}", NameSource.Code, () => service.DeleteAsync(table, id))
        : Task.FromResult(new GuardResult<bool>(GuardOutcome.Refused, false, $"404 Not Found: no type '{type}'."));

private static Task<GuardResult<bool>> DeleteDirectlyAsync(ArxMySqlDbService service, SecurityGuard guard, string user, string type, ArxRecordId id)
    => guard.RunAsync(user, $"delete {type}", NameSource.UserInput, () => service.DeleteAsync(type, id));
```

Check it in the database:

```sql
SELECT account_number
FROM account
WHERE account_number IN ('ACC-G303', 'ACC-G304');
```

Expect: 1 row: ACC-G303 (the blocked attempts deleted nothing); ACC-G304 was deleted after the fix.

### Delete on a preloaded schema: keys only, Strict

Advanced case 3 of Delete.

Delete on a schema preloaded at service setup, in three scenarios: the convenient setup (keys only) deleting by the key the preload found, with the table it added because it is referenced; Strict refusing a delete matched by a column that wasn't preloaded; and the same delete on a service that isn't Strict.

Good to know:

Under Strict, every column that finds the rows to delete must be preloaded, so an endpoint can only delete by the columns you chose. Create Advanced6 shows the preload itself and its JSON.

Usings:

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

Set up by the sample before its scenarios:

```csharp
foreach (var (first, email) in new[] { ("Dora", "dora.delete@example.com"), ("Dev", "dev.delete@example.com"), ("Dee", "dee.delete@example.com") })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Deleteme";
    contact["email"] = email;
    await service.CreateAsync(contact);
}
```

#### 1. KeysOnly: delete by the key the preload found

Keys only: contact.email is unique, so it is a key; account comes too (contact.account_id references it).

In scope: `connectionString` (string).

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

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

foreach (var table in schema.Tables)
{
    Console.WriteLine($"{table.Name,-8} {table.Reason}; keys {string.Join(" ", table.Keys)}");
}

var dora = new ArxEntity("contact");
dora["email"] = "dora.delete@example.com";
Console.WriteLine($"deleted {await service.DeleteAsync(dora, new ArxDeleteOptions { Match = ArxMatch.Key("email") })} row");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email = 'dora.delete@example.com';
```

Expect: 0.

#### 2. Strict: a delete matched by a column that wasn't preloaded

Strict with only email preloaded: deleting every contact named "Deleteme" is refused before the database.

In scope: `connectionString` (string).

```csharp
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("contact", ["email"]);

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

var byLastName = new ArxEntity("contact");
byLastName["last_name"] = "Deleteme";

try
{
    await strict.DeleteAsync(byLastName, new ArxDeleteOptions { Match = ArxMatch.Columns("last_name") });
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.NotPreloaded)
{
    Console.WriteLine(exception.Message);
}

var byEmail = new ArxEntity("contact");
byEmail["email"] = "dev.delete@example.com";
Console.WriteLine($"by the preloaded email: deleted {await strict.DeleteAsync(byEmail, new ArxDeleteOptions { Match = ArxMatch.Key("email") })} row");
```

Check it in the database:

```sql
SELECT email
FROM contact
WHERE last_name = 'Deleteme';
```

Expect: 1 row, dee.delete@example.com: the delete by last_name was refused, dev's by email ran.

#### 3. Not Strict: the same delete works

The same schema, not Strict: last_name isn't preloaded, and the delete by it still works.

In scope: `connectionString` (string).

```csharp
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("contact", ["email"]);

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

var byLastName = new ArxEntity("contact");
byLastName["last_name"] = "Deleteme";
Console.WriteLine($"deleted {await service.DeleteAsync(byLastName, new ArxDeleteOptions { Match = ArxMatch.Columns("last_name") })} row");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE last_name = 'Deleteme';
```

Expect: 0.