Delete · MySQL in C#

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

Basics

The least code that works.

1Delete one recordStandalone

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

2Nothing to delete is not an errorStandalone

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).

3Delete by key, by compound key, by columnsStandalone

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.

4Delete without a catalogStandalone

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.

Real cases

What an application does: a small CRM and product catalogue.

1Remove a contact by id or by emailProgressive

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).

2Undo an import of 5,000 contactsProgressive

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.

3A referenced record: ARX3002, then delete in orderProgressive

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.

4Soft delete: close and restore an accountProgressive

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.

5Catch an SQL injection attempt on deleteProgressive

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.

Advanced cases

Dependency injection, transactions, batch scopes and setting the service up.

1Replace an order's lines: delete, create, update, all or nothingAdvanced

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.

2A security check around deletes: probing, blocking, unblocking a false positiveAdvanced

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.

3Delete on a preloaded schema: keys only, StrictAdvanced

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.