Delete · SQL Server 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 (an `INSTEAD OF` trigger that skips the row still counts it: SQL Server reports the rows the statement was for)
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 SQL Server compares it (with the default collation, case-insensitively). Many entities go in one statement (`DELETE … FROM … JOIN` the values), 20 or more as one JSON parameter
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
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 (`NO ACTION`; SQL Server has no deferred constraints, so it fails at the statement, never at commit)
ARX5001, ARX6001, ARX7003, ARX8001	Missing table, no permission (or a row-level security block predicate), lock or command timeout, a view SQL Server 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.