Data modification (DELETE)

nextorm removes rows with the same explicit-command model as INSERT: CreateDeleteBuilder<TEntity>() builds a parameterised DELETE FROM <table> [WHERE ...], the context extension Delete<TEntity>(entity) deletes by the entity's declared key, and a joined query can end in a multi-table delete. There is no change tracking and no SaveChanges: every terminal issues exactly one command.

Prerequisites: Data modification (INSERT) · Filtering (WHERE) · Joins · Provider overview

Deleting rows by predicate

CreateDeleteBuilder<TEntity>() removes rows and returns the number of deleted rows:

var deleted = ctx.CreateDeleteBuilder<ISimpleEntity>()
    .Where(x => x.Id == 1)
    .Delete();

await ctx.CreateDeleteBuilder<ISimpleEntity>()
    .Where(x => x.Age > 10)
    .DeleteAsync(cancellationToken);

-- PostgreSQL
delete from simple_entity where id = 1
delete from simple_entity where (age > 10)

Where accepts the same predicate expressions as a query (From<T>().Where(...)), and repeating it combines the predicates with and. CreateDeleteBuilder<T>().ToSql() renders the statement without opening a connection.

• Values captured in the predicate become parameters (x => x.Id == id renders id = @id); inline literals are emitted verbatim, exactly as in a query WHERE.
• Delete()/DeleteAsync() return the affected-row count (0 when nothing matched). ClickHouse reports no count (its mutation returns none).

Deleting every row

Deleting every row requires the explicit All() marker, so an unfiltered full-table delete cannot be written by accident. Combining Where and All throws InvalidOperationException:

ctx.CreateDeleteBuilder<ISimpleEntity>().All().Delete();   // delete from simple_entity

Deleting by key

The key form deletes exactly the row identified by the entity's declared key ([Key]/.Key()), rendering the key equality as a parameter:

ctx.Delete(new SimpleEntity { Id = 1 });          // delete from simple_entity where id = @p0
await ctx.DeleteAsync(new SimpleEntity { Id = 1 });

-- PostgreSQL
delete from simple_entity where id = @p0

Global query filters

A global query filter declared for the deleted entity is and-ed into the statement's WHERE:

• predicate form — delete from ... where <predicate> and <filter>;
• key form — delete from ... where <pk> = @p and <filter>, so ctx.Delete(entity) removes nothing when the filter excludes that key.

All() is an explicit full-table delete and is never filtered. A joined delete filters the target (the first table) and every joined source. DeleteBuilder<TEntity> exposes the four IgnoreFilters overloads (all, by entity type, by filter key, key-and-type intersection); the call is stateful and repeated calls accumulate:

ctx.CreateDeleteBuilder<Document>()
    .IgnoreFilters(["soft-delete"])   // keep tenant scoping, drop soft-delete
    .Where(d => d.IsDeleted)
    .Delete();

See UPDATE and DELETE (DML) for the full matrix.

Provider support at a glance

Provider	DELETE	RETURNING / OUTPUT	TRUNCATE	DELETE ... USING / join
PostgreSQL	yes	RETURNING	yes	USING
SQL Server	yes	OUTPUT deleted.<col>	yes	DELETE <alias> FROM ... JOIN
SQLite	yes	RETURNING	NotSupportedException	NotSupportedException
MySQL / MariaDB	yes	NotSupportedException	yes	DELETE <alias> FROM ... JOIN
ClickHouse	ALTER TABLE ... DELETE	NotSupportedException	yes	NotSupportedException
In-memory	NotSupportedException (query-only)	NotSupportedException	NotSupportedException	NotSupportedException

Returning the removed rows

Returning() / Returning(projection) materialise the removed rows through the provider's RETURNING/OUTPUT form, read through Single()/ToList():

var removed = ctx.CreateDeleteBuilder<ISimpleEntity>()
    .Where(x => x.Age > 10)
    .Returning(x => new { x.Id, x.Name })
    .ToList();

var one = ctx.CreateDeleteBuilder<ISimpleEntity>()
    .Where(x => x.Id == 1)
    .Returning()
    .Single();

-- PostgreSQL
delete from simple_entity where (age > 10) returning id, name
delete from simple_entity where id = 1 returning id, name, age

Provider	Form
SQLite, PostgreSQL	DELETE ... RETURNING <cols>
SQL Server	DELETE ... OUTPUT deleted.<col> ...
MySQL, MariaDB, ClickHouse, in-memory	NotSupportedException

Returning() returns the whole mapped entity (a concrete TEntity); Returning(x => ...) returns the projected shape (a single member, an anonymous type or a member-init). Single() throws when the delete removed no row or more than one.

To capture the removed rows for a later statement of the same batch instead of returning them to the client, register a batch-scoped capture — the deleted.* columns are the captured rows — and compose a query over it with FromOutput<TResult>/FromOutputTable: see Batch-scoped capture. SQL Server only.

Truncating a table

CreateTruncateBuilder<TEntity>() renders the provider's native TRUNCATE TABLE, resetting a table faster than CreateDeleteBuilder<T>().All():

var cleared = ctx.CreateTruncateBuilder<ISimpleEntity>().Execute();   // truncate table simple_entity

SQL Server, PostgreSQL, MySQL, MariaDB and ClickHouse have TRUNCATE TABLE; SQLite has no TRUNCATE and throws NotSupportedException, and the in-memory context is query-only and throws NotSupportedException too. Execute()/ExecuteAsync() return the affected-row count where the provider reports one (0 otherwise).

Delete based on a join

A joined query can end in Delete()/DeleteAsync(): the first table of the chain is the delete target, and the rows removed are those matching the join condition and any Where. Only INNER joins are supported.

var removed = await ctx.From<ISimpleEntity>()
    .Join<IComplexEntity>((s, c) => s.Id == c.Id)
    .Where(p => p.Item2.String == "archived")
    .DeleteAsync(cancellationToken);

-- PostgreSQL
delete from simple_entity as "t1" using complex_entity as "t2" where cast(t1.id as bigint) = t2.id and t2.somestring = 'archived'

Provider	Rendered form
PostgreSQL	DELETE FROM <t> AS a USING <u> AS b WHERE ...
SQL Server	DELETE a FROM <t> AS a JOIN <u> AS b ON ... WHERE ...
MySQL, MariaDB	DELETE `a` FROM <t> AS `a` JOIN <u> AS `b` ON ... WHERE ...
SQLite, ClickHouse, in-memory	NotSupportedException

• The target is the first table (Item1); only Join (INNER) is accepted. LeftJoin/RightJoin/FullJoin/CrossJoin and the APPLY joins throw NotSupportedException, because they change which rows are deleted.
• The terminals are extension methods on the joined builder for arities 2–8; Delete()/DeleteAsync() return the affected-row count. ToSql() renders the statement without opening a connection and throws on an in-memory context.
• CreateDeleteJoinBuilder() starts the row-returning multi-table delete; its Returning()/Returning(projection) terminals are available on PostgreSQL only (DELETE ... USING ... RETURNING), on INNER joins and for arities 2–8. Returning() — equivalent to Returning(p => p) — returns the whole joined projection (Projection<T1, ...>): every returnable mapped property of every item slot, in source order. Item1 is the delete target and every repeated CLR type stays distinct by slot, so a self-join of one entity keeps its Item1/Item2 values apart. A delete returns the removed-row values. Returning(p => new { p.Item1.Id, p.Item2.Name }) returns an explicit projection instead, read through Single()/ToList(); returned columns get deterministic per-slot aliases, and explicit projections are unchanged. No Returning() call adds a RETURNING list implicitly. It works both as a normal terminal and as a data-modifying CTE body through With(name, delete). A returned item whose mapped property is a multi-column Range<T> is rejected, and a required member with no counterpart in the joined source shape is rejected naming the slot and member. See Common table expressions.
• A joined side may be a CTE, whose declaration is hoisted before the DELETE (PostgreSQL with c as (…) delete from <t> as "t1" using c as "t2" where …); the target stays the first physical table and the CTE may sit on any join position. See Common table expressions.

var all = await ctx.From<IOrder>()
    .Join<ICustomer>((o, c) => o.CustomerId == c.Id)
    .Where(p => p.Item2.Tier == "gold")
    .CreateDeleteJoinBuilder()
    .Returning()          // equivalent to .Returning(p => p)
    .ToListAsync(cancellationToken);

DELETE as a data-modifying CTE body (PostgreSQL)

On PostgreSQL a row-returning joined DELETE can be the body of a data-modifying CTE: pass it to With(name, delete) and read the removed rows typed through From(name):

var doomed = dataContext
    .From<IOrder>()
    .Join<ICustomer>((o, c) => o.CustomerId == c.Id)
    .CreateDeleteJoinBuilder()
    .Returning(p => new { OrderId = p.Item1.Id, CustomerName = p.Item2.Name });

var removed = dataContext
    .With("del", doomed)
    .From("del")
    .Select(r => new { r.OrderId, r.CustomerName })
    .ToList();

-- PostgreSQL
with del as (delete from orders as "t1" using customers as "t2" where t1.customer_id = t2.id returning t1.id as "OrderId", t2.name as "CustomerName") select "OrderId", "CustomerName"
 from del as "t1"

A RETURNING projection is required (a side-effect-only mutation is out of scope), and such a statement is never plan-cached. See Common table expressions.

Delete based on a CTE

var e = ctx.From<IComplexEntity>();
var scope = ctx.With("c", e.Where(x => x.Id > 0).Select(x => new { x.Id }));

var removed = e
    .Join(scope.From("c"), (t, c) => t.Id == c["id"].AsInt)
    .Delete();

-- PostgreSQL
with c as (select id
 from complex_entity where (id > 0)) delete from complex_entity as "t1" using c as "t2" where t1.id = cast(t2.id as bigint)

The with … is emitted before the mutation on every provider with a multi-table delete, and the CTE may sit on any join position. Two different CTEs declared with the same name on the two sides of a join are rejected with InvalidOperationException. See Common table expressions.

Notes and out-of-scope

• The in-memory context is query-only: Delete/DeleteAsync/CreateTruncateBuilder (like every other write) throw NotSupportedException; query your own collections instead. INSERT on the in-memory provider is likewise out of scope by design.
• DELETE is not prepared or plan-cached — optimisation in nextorm targets read-only queries only (Prepare, the implicit plan cache, benchmarks); a mutation always renders and executes one command per call.
• There is deliberately no built-in soft-delete behaviour; soft delete is expressed with a global query filter plus an explicit UPDATE. A full MERGE with arbitrary branches is not part of this surface, and UPDATE lives in its own guide (Data modification (UPDATE)).

See also

• Data modification (INSERT)
• Data merging (MERGE / upsert)
• Joins
• Limitations and out-of-scope features
• Provider overview
• API reference