DELETE

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

updated 5 Jun 20263 min readv0.3.2View as Markdown

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.

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

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

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

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

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

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

// 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();