# DeleteMultiple with ARX.SDK on PostgreSQL

## What a DeleteMultiple does

| | |
|---|---|
| **Many by id** | `DeleteMultipleAsync("account", ids)`: one `= ANY($1)` array parameter, 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 … 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

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

### Delete many records in one call

Basics 1 of DeleteMultiple.

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

Usings:

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

Set up by the sample before its scenarios:

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

#### 1. By the ids Create returned

The usual way: the table and the ids. The call returns how many records went.

In scope: `service` (ArxPgDbService).

```csharp
var ids = await CreateAccountsAsync(service, 1, 5);

int deleted = await service.DeleteMultipleAsync("account", ids);

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

Defined in the sample, used above:

```csharp
private static async Task<IReadOnlyList<ArxRecordId>> CreateAccountsAsync(ArxPgDbService service, int first, int count)
{
    var accounts = Enumerable.Range(first, count).Select(i =>
    {
        var account = new ArxEntity("account");
        account["name"] = $"Delete many {i}";
        account["account_number"] = $"ACC-D2{i:00}";
        return account;
    }).ToList();

    return await service.CreateMultipleAsync(accounts);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number BETWEEN 'ACC-D201' AND 'ACC-D205';
```

Expect: 0.

#### 2. Missing and repeated ids

A missing id isn't an error, and an id given twice goes once: the count says what really happened.

In scope: `service` (ArxPgDbService).

```csharp
var ids = (await CreateAccountsAsync(service, 11, 3)).Select(x => x.AsInt64()).ToList();
var list = new List<long> { ids[0], ids[1], ids[2], ids[0], 999_999_995 };

int deleted = await service.DeleteMultipleAsync("account", list);

Console.WriteLine($"asked for {list.Count}, {deleted} deleted");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number BETWEEN 'ACC-D211' AND 'ACC-D213';
```

Expect: 0.

### Delete many records without a catalog

Basics 2 of DeleteMultiple.

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.

Good to know:

The extra round trip is paid once per call, not once per id; still, a service with a catalog never pays it.

Usings:

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

#### 1. By the ids Create returned

The table and the ids CreateMultiple returned.

In scope: `connectionString` (string).

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

var accounts = new List<ArxEntity>();
for (var i = 1; i <= 3; i++)
{
    var account = new ArxEntity("account");
    account["name"] = $"To remove {i}";
    account["account_number"] = $"ACC-N05{i}";
    accounts.Add(account);
}

var ids = await service.CreateMultipleAsync(accounts);

int deleted = await service.DeleteMultipleAsync("account", ids);

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

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number IN ('ACC-N051', 'ACC-N052', 'ACC-N053');
```

Expect: 0.

### Delete every row a filter matches

Basics 3 of DeleteMultiple.

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.

Good to know:

The filter takes the operators of a query on the table's own columns; an empty filter is refused unless new ArxFilterWriteOptions { AllRows = true } is passed.

Needs ARX.SDK 0.1.0-preview.5 or later.

Usings:

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

#### 1. Every row the filter matches

Closed accounts removed with one DELETE; the same filter again deletes nothing and returns 0.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);
var accounts = new List<ArxEntity>();

for (var i = 1; i <= 4; i++)
{
    var account = new ArxEntity("account");
    account["name"] = $"Filter delete {i}";
    account["account_number"] = $"ACC-F20{i}";
    account["status"] = i % 2 == 0 ? "CLOSED" : "ACTIVE";
    accounts.Add(account);
}

await service.CreateMultipleAsync(accounts);

var filter = new ArxFilterExpression();
filter.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-F20");
filter.AddCondition("status", ArxConditionOperator.Equal, "CLOSED");

int deleted = await service.DeleteMultipleAsync("account", filter);
int again = await service.DeleteMultipleAsync("account", filter);

Console.WriteLine($"{deleted} closed accounts deleted; the same filter again deleted {again}");
```

Defined in the sample, used above:

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

Check it in the database:

```sql
SELECT account_number, status
FROM account
WHERE account_number LIKE 'ACC-F20%'
ORDER BY account_number;
```

Expect: 2 rows, ACC-F201 and ACC-F203, both ACTIVE.

### Remove the contacts a user selected

Real case 1 of DeleteMultiple.

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

Good to know:

Both are one call, all or nothing: either every matching contact goes or, when the database refuses one (a reference to it, for example), none does.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var contacts = new List<ArxEntity>();
foreach (var (first, last) in new[] { ("Ines", "Moreau"), ("Kenji", "Sato"), ("Lena", "Fischer"), ("Marco", "Bianchi"), ("Nadia", "Rahman") })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = last;
    contact["email"] = $"{first.ToLowerInvariant()}.{last.ToLowerInvariant()}@example.com";
    contacts.Add(contact);
}
var ids = await service.CreateMultipleAsync(contacts);
```

#### 1. The ticked rows of a list

The list screen sends the ids of the rows the user ticked.

In scope: `service` (ArxPgDbService), `selected` (IReadOnlyList<ArxRecordId>).

```csharp
int deleted = await service.DeleteMultipleAsync("contact", selected);

Console.WriteLine($"{deleted} of {selected.Count} selected contacts removed");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email IN ('ines.moreau@example.com', 'kenji.sato@example.com', 'lena.fischer@example.com');
```

Expect: 0.

#### 2. An unsubscribe file of emails

An unsubscribe file has only emails: one entity per address, found by the declared key.

In scope: `service` (ArxPgDbService).

```csharp
var file = new[] { "marco.bianchi@example.com", "nadia.rahman@example.com", "nobody@example.com" };

var requests = file.Select(email =>
{
    var request = new ArxEntity("contact");
    request["email"] = email;
    return request;
}).ToList();

// An address with no contact isn't an error: the count says how many were really there.
int deleted = await service.DeleteMultipleAsync(requests, new ArxDeleteOptions { Match = ArxMatch.Key("email") });

Console.WriteLine($"{deleted} of {file.Length} addresses removed");
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email IN ('marco.bianchi@example.com', 'nadia.rahman@example.com');
```

Expect: 0.

### Deletes in one ARX transaction: roll back or commit

Advanced case 1 of DeleteMultiple.

Deletes inside an ARX transaction, in two scenarios: delete, check, roll back (nothing is lost); and delete several tables' rows and commit them together.

Usings:

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

#### 1. Delete, check, roll back

Delete three accounts, then your check says it was the wrong list: roll back and they're all back.

In scope: `service` (ArxPgDbService), `connectionString` (string), `ids` (IReadOnlyList<ArxRecordId>).

```csharp
await using var connection = new ArxPgConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var deleted = await service.DeleteMultipleAsync("account", ids, transaction);

if (deleted != 2)
{
    await transaction.RollbackAsync();
    Console.WriteLine($"expected 2, deleted {deleted}: rolled back");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number IN ('ACC-D701', 'ACC-D702', 'ACC-D703');
```

Expect: 3: all back.

#### 2. Delete and log, then commit

The right list this time, and a log entry for it: committed together.

In scope: `service` (ArxPgDbService), `connectionString` (string), `ids` (IReadOnlyList<ArxRecordId>).

```csharp
await using var connection = new ArxPgConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var deleted = await service.DeleteMultipleAsync("account", new[] { ids[0], ids[1] }, transaction);

var log = new ArxEntity("activity_log");
log["action"] = $"deleted {deleted} test accounts";
await service.CreateAsync(log, transaction);

await transaction.CommitAsync();
Console.WriteLine($"{deleted} deleted and logged");
```

Check it in the database:

```sql
SELECT (SELECT count(*) FROM account WHERE account_number IN ('ACC-D711', 'ACC-D712', 'ACC-D713')) AS accounts,
       (SELECT count(*) FROM activity_log WHERE action = 'deleted 2 test accounts') AS logged;
```

Expect: 1, 1.