MigsLab SQL Server
DeleteMultiple
5 samples, from the least code that works to complete workflows.
What a DeleteMultiple does
| Many by id | DeleteMultipleAsync("account", ids): up to 2,000 ids as one parameter each in an IN list; more as one JSON parameter, IN (SELECT v FROM OPENJSON(@p0) WITH (v bigint '$')), up to 1,000,000 a statement; never a concatenated list. Returns the count; duplicates count once |
| Without a catalog | By ids, the table is read once for the whole list (Basic2) |
| By a filter | DeleteMultipleAsync("account", filter): one DELETE … FROM … WHERE … for every row the filter matches, returning the count, 0 when none (Basic3). An empty filter needs AllRows. From 0.1.0-preview.5 |
The samples
Basics
The least code that works.
1Delete many records in one callStandaloneMany records in one call, in two scenarios: by the ids CreateMultiple returned; and a list with an id that doesn't exist and one given twice, both counted honestly.
Many records in one call on a service created with only a connection string: ARX reads the table's id column once, then deletes every id in the list and returns how many went.
Every row a condition matches, deleted in one call: the table and a filter. The call returns how many rows went, and 0 when none matched, which isn't an error.
Real cases
What an application does: a small CRM and product catalogue.
1Remove the contacts a user selectedProgressiveRemoving the contacts a user ticked, in two scenarios: a list screen that has their ids, and an unsubscribe file that has only email addresses (the contact table's declared key).
Advanced cases
Dependency injection, transactions, batch scopes and setting the service up.
1Deletes in one ARX transaction: roll back or commitAdvancedDeletes inside an ARX transaction, in two scenarios: delete, check, roll back (nothing is lost); and delete several tables' rows and commit them together.