# DELETE

How to delete rows from a database table using the generated Delete() builder in Socigy.OpenSource.DB v0.2.0.

## Overview

The library provides two ways to delete rows: an instance-based builder that deletes by primary key, and a static non-instance builder for filtered deletes. Both produce parameterized SQL — no values are string-interpolated.

## Delete by Primary Key (Instance Method)

Call `.Delete()` on a loaded instance, attach a connection or transaction, and execute. The `WHERE` clause is built automatically from the PK column(s) on the instance.

```csharp
// C# — delete the row matching user.Id
await user.Delete()
    .WithConnection(conn)
    .ExecuteAsync();
// SQL: DELETE FROM "users" WHERE "id" = $1
```

The instance is not nulled out or modified after the delete. The database row is gone; the C# object remains in memory as a plain object.

## Filtered Delete (Non-Instance)

When you do not have a loaded instance, use the static `DeleteNonInstance()` builder. It requires a `.Where()` predicate and returns the number of rows deleted.

```csharp
// C# — delete all rows matching a filter
int deleted = await User.DeleteNonInstance()
    .WithConnection(conn)
    .Where(x => x.CreatedAt < cutoff)
    .ExecuteAsync();

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

The full [WHERE operator set](where) is available, including logical operators, string methods, and null checks.

## Return Values

| Builder | Return type | Value |
|---|---|---|
| `instance.Delete().ExecuteAsync()` | `Task<int>` | Number of rows deleted (usually `1`) |
| `User.DeleteNonInstance()...ExecuteAsync()` | `Task<int>` | Number of rows deleted |

## Composite Primary Keys

When an entity has multiple `[PrimaryKey]` columns, all of them are included in the `WHERE` clause of an instance delete automatically.

```csharp
// C# — enrollment has [PrimaryKey] on both UserId and CourseId
await enrollment.Delete()
    .WithConnection(conn)
    .ExecuteAsync();
// SQL: DELETE FROM "user_course" WHERE "user_id" = $1 AND "course_id" = $2
```

## Cascade Deletes via Foreign Keys

If a related table defines a foreign key with `ON DELETE CASCADE`, PostgreSQL removes the dependent rows automatically when the parent row is deleted. No extra code is needed.

```csharp
// C# — deleting a user cascades to all user_course rows (FK = ON DELETE CASCADE)
await user.Delete()
    .WithConnection(conn)
    .ExecuteAsync();
// PostgreSQL also deletes rows in "user_course" where user_id = user.Id
```

> **WARNING** Cascade behavior is defined in your schema DDL or schema generator attributes, not in the builder. Verify your FK constraints before relying on cascades in production.

## Bulk Delete Pattern

For deleting a set of known instances, share a single transaction to keep the operation atomic and reduce round-trip overhead.

```csharp
// C# — bulk delete in a transaction
await using var tx = await conn.BeginTransactionAsync();

foreach (var user in usersToRemove)
{
    await user.Delete()
        .WithTransaction(tx)
        .ExecuteAsync();
}

await tx.CommitAsync();
```

For set-based bulk deletes (delete all rows matching a condition rather than a list of instances), prefer `DeleteNonInstance()` with a `Where()` predicate — it issues a single SQL statement instead of N statements.

```csharp
// C# — single-statement bulk delete
int deleted = await User.DeleteNonInstance()
    .WithConnection(conn)
    .Where(x => x.Status == "inactive" && x.LastLoginAt < cutoff)
    .ExecuteAsync();
```

## Using With Transactions

Both delete approaches accept `.WithTransaction(tx)` in place of `.WithConnection(conn)`.

```csharp
// C# — coordinated delete inside a transaction
await using var tx = await conn.BeginTransactionAsync();

await post.Delete().WithTransaction(tx).ExecuteAsync();
await User.DeleteNonInstance()
    .WithTransaction(tx)
    .Where(x => x.Id == post.AuthorId && x.PostCount == 0)
    .ExecuteAsync();

await tx.CommitAsync();
```

## See Also

- [INSERT](insert) — adding new rows
- [UPDATE](update) — modifying existing rows
- [WHERE Operators](where) — full predicate operator reference