/DB

DELETE

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

updated 3 May 20263 min readv0.1.82

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.

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

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

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

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

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

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

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