# DELETE

Delete rows with the instance builder (by primary key) or the static DeleteNonInstance() builder (filtered). Always parameterized, never string-interpolated.

## Overview

The library offers two ways to delete rows: an instance builder that deletes by primary key, and a static non-instance builder for filtered deletes. Both produce parameterized SQL, so 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
// 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. Supply a `.Where()` predicate (always do, see the warning below) and it returns the number of rows deleted.

```csharp
// 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](/database/0.3.2/querying/reading/where) is available, including logical operators, string methods, and null checks.

> **WARNING** `DeleteNonInstance()` with no `Where` produces an unfiltered `DELETE FROM "table"` that removes **every** row and is not guarded. Always supply a `.Where()` predicate. (The only built-in guard is on an *instance* delete: if the entity has no primary-key column, `ExecuteAsync()` throws rather than deleting the whole table.)

## 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
// 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
// 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 cut round-trip overhead.

```csharp
// 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 (every row 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
// 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
// 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](/database/0.3.2/querying/writing/insert) for adding new rows
- [UPDATE](/database/0.3.2/querying/writing/update) for modifying existing rows
- [WHERE Operators](/database/0.3.2/querying/reading/where) for the full predicate operator reference