Table of Contents

Data modification (UPDATE)

nextorm changes rows with the same explicit-command model as INSERT and DELETE: CreateUpdateBuilder<TEntity>() builds a parameterised UPDATE <table> SET ... [WHERE ...], and the context extension Update<TEntity>(entity) updates by the entity's declared key. There is no change tracking and no SaveChanges: every terminal issues exactly one command.

Prerequisites: Data modification (INSERT) · Data modification (DELETE) · Entities and metadata · Provider overview

Updating rows by predicate

CreateUpdateBuilder<TEntity>() returns a UpdateBuilder<TEntity>. Add one Set per written column, then Where, and finish with Update()/UpdateAsync() to get the affected-row count:

var updated = ctx.CreateUpdateBuilder<ISimpleEntity>()
    .Set(x => x.Name, "renamed")
    .Set(x => x.Age, 42)
    .Where(x => x.Id == 1)
    .Update();

await ctx.CreateUpdateBuilder<ISimpleEntity>()
    .Set(x => x.Name, "renamed")
    .Where(x => x.Age > 10)
    .UpdateAsync(cancellationToken);
  • Set(column, value) always binds the value as a parameter, never inlines it.
  • Where accepts the same predicate expressions as a query (From<T>().Where(...)), and repeating it combines the predicates with and. Captured variables become parameters, inline literals are emitted verbatim, exactly as in a query WHERE.
  • Update()/UpdateAsync() return the affected-row count (0 when nothing matched). Omitting Where updates every row of the table that matches the target's global query filters (if any), exactly like a read.
  • ToSql() renders the statement without opening a connection.

Assignments

The right-hand side of Set is a value, a mapped column, or an expression:

// value (parameter)
.Set(x => x.Name, "renamed")

// another mapped column of the same row
.Set(x => x.UpdatedAt, x => x.CreatedAt)

// arbitrary expression: other columns and captured values
.Set(x => x.Counter, x => x.Counter + 1)
.Set(x => x.Total, x => x.Price * quantity)

An expression is rendered by the same translator as a SELECT/WHERE expression, so it may only use mapped columns, captured values and the supported scalar functions. Set(entity) writes every mapped, non-key, non-identity, non-computed column of the entity at once:

ctx.CreateUpdateBuilder<ISimpleEntity>()
    .Set(entity)
    .Where(x => x.Id == entity.Id)
    .Update();

Assigning a computed column throws NotSupportedException. A repeated Set for the same column replaces the earlier assignment.

Updating every row

Unlike DELETE, which requires the explicit All() marker, UPDATE without Where updates the whole table:

ctx.CreateUpdateBuilder<ISimpleEntity>().Set(x => x.Archived, true).Update();   // update simple_entity set archived = @p0

When the entity declares global query filters, the predicate still applies, so an update without Where touches only the filtered rows; call IgnoreFilters() on the builder to update the unfiltered set. See UPDATE and DELETE (DML).

Global query filters

A global query filter declared for the updated entity is and-ed into the statement's WHERE — with the predicate form, with the key form (WHERE <pk> = @p and <filter>) and when Where is omitted. A row excluded by the filter is not updated.

UpdateBuilder<TEntity> and UpdateJoinBuilder<TProjection> expose the four IgnoreFilters overloads (all, by entity type, by filter key, and the key-and-type intersection). The call is stateful and repeated calls accumulate:

ctx.CreateUpdateBuilder<Document>()
    .IgnoreFilters(["soft-delete"])   // keep the tenant filter, drop soft-delete
    .Set(d => d.Archived, true)
    .Where(d => d.IsDeleted)
    .Update();

For the multi-table CreateUpdateJoinBuilder, the target (first table) and every joined source are filtered. INSERT and MERGE do not filter their target. See UPDATE and DELETE (DML) for the full matrix.

Updating by key

The key form updates exactly the row identified by the entity's declared key ([Key]/.Key()), writing every non-key, non-identity, non-computed column and rendering the key equality as a parameter:

ctx.Update(new SimpleEntity { Id = 1, Name = "renamed" });   // update simple_entity set name = @p0 where id = @p1
await ctx.UpdateAsync(new SimpleEntity { Id = 1, Name = "renamed" });

The entity type must declare a key; otherwise the call throws InvalidOperationException.

Returning the updated rows

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

var updated = ctx.CreateUpdateBuilder<ISimpleEntity>()
    .Set(x => x.Archived, true)
    .Where(x => x.Age > 10)
    .Returning(x => new { x.Id, x.Name })
    .ToList();

var one = ctx.CreateUpdateBuilder<ISimpleEntity>()
    .Set(x => x.Archived, true)
    .Where(x => x.Id == 1)
    .Returning()
    .Single();

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 update touched no row or more than one. Returning is only available on the predicate form: the key form (Update(entity)) does not expose it.

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

Updating from a join

CreateUpdateJoinBuilder() on a joined query builds a multi-table UPDATE whose target is the first table of the join and whose SET values may read any joined table:

var updated = ctx.From<IOrder>()
    .Join<ICustomer>((o, c) => o.CustomerId == c.Id)
    .CreateUpdateJoinBuilder()
    .Set(p => p.Item1.Status, "priority")
    .Set(p => p.Item1.Total, p => p.Item1.Total + p.Item2.Credit)
    .Where(p => p.Item2.Tier == "gold")
    .Update();

-- PostgreSQL
update orders as "t1" set status = @p0, total = (t1.total + t2.credit) from customers as "t2" where t1.customer_id = t2.id and t2.tier = 'gold'

Set selects a column of the first (Item1) table; the value may be a constant or an expression that references any joined table (p.Item2...). Where filters on the whole projection, and the terminal is Update()/UpdateAsync() (affected-row count) or ToSql().

Multi-table Returning()/Returning(projection) is available on PostgreSQL only (UPDATE ... FROM ... 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, where Item1 is the update target and every repeated CLR type stays distinct by slot, so a self-join of one entity keeps its Item1/Item2 values apart. An update returns the post-update target values. Returning(p => new { ... }) returns an explicit projection instead; returned columns get deterministic per-slot aliases, and explicit projections are unchanged. No Returning() call adds a RETURNING list implicitly. 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. It works both as a normal terminal and as a data-modifying CTE body through With(name, update):

var updated = ctx.From<IOrder>()
    .Join<ICustomer>((o, c) => o.CustomerId == c.Id)
    .CreateUpdateJoinBuilder()
    .Set(p => p.Item1.Status, "priority")
    .Where(p => p.Item2.Tier == "gold")
    .Returning(p => new { OrderId = p.Item1.Id, CustomerName = p.Item2.Name })
    .ToList();

-- PostgreSQL
update orders as "t1" set status = @p0 from customers as "t2" where t1.customer_id = t2.id and t2.tier = 'gold' returning t1.id as "OrderId", t2.name as "CustomerName"

The parameterless form returns every returnable column of both slots under deterministic per-slot aliases (__s1_*, __s2_*, …):

var whole = ctx.From<IOrder>()
    .Join<ICustomer>((o, c) => o.CustomerId == c.Id)
    .CreateUpdateJoinBuilder()
    .Set(p => p.Item1.Status, "priority")
    .Where(p => p.Item2.Tier == "gold")
    .Returning()          // equivalent to .Returning(p => p)
    .ToList();

Only INNER Join joins are supported: the join conditions are folded into the filter (or kept as the join's ON), so an outer join would silently change which rows are updated — LeftJoin/RightJoin/ FullJoin/CrossJoin throw NotSupportedException. PostgreSQL and SQLite render UPDATE ... FROM, SQL Server renders UPDATE <alias> ... FROM ... JOIN, and MySQL/MariaDB render UPDATE ... JOIN ... SET; ClickHouse and the in-memory provider throw.

The joined side may be a CTE: its declaration is hoisted before the UPDATE while the target stays the first (physical) table.

var recent = ctx.With("recent", ctx.From<IOrder>().Where(o => o.Id > 1000).Select(o => new { o.Id }));

var updated = ctx.From<IOrder>()
    .Join(recent.From("recent"), (o, r) => o.Id == r.GetInt64("id"))
    .CreateUpdateJoinBuilder()
    .Set(p => p.Item1.Status, "priority")
    .Update();

-- PostgreSQL
with recent as (select id
 from orders where (id > 1000)) update orders as "t1" set status = @p0 from recent as "t2" where t1.id = t2.id

This works on every provider that supports UPDATE ... FROM/JOIN, including when the CTE sits on the second or a later join, and for recursive CTEs (SQL Server appends OPTION (MAXRECURSION n)). Two distinct CTE declarations sharing a name on the two sides of a join are rejected with an InvalidOperationException, because a single WITH cannot bind one name to two definitions. See Common table expressions.

UPDATE as a data-modifying CTE body (PostgreSQL)

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

var updated = dataContext
    .With("upd", dataContext.CreateUpdateBuilder<IOrder>()
        .Set(x => x.Total, 0)
        .Where(x => x.CustomerId == 7)
        .Returning(x => new { x.Id, x.Total }))
    .From("upd")
    .Select(r => new { r.Id, r.Total })
    .ToList();

-- PostgreSQL
with upd as (update orders set total = @p0 where customer_id = 7 returning id, total) select id, total
 from upd as "t1"

The joined form works the same way — With(name, update) over a multi-table CreateUpdateJoinBuilder().Returning(...). 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.

Provider support at a glance

Provider UPDATE ... SET ... WHERE RETURNING / OUTPUT UPDATE ... FROM
PostgreSQL yes UPDATE ... RETURNING <cols> UPDATE <target> AS t1 SET ... FROM ... WHERE ...
SQL Server yes UPDATE ... OUTPUT inserted.<col> ... (between SET and WHERE) UPDATE <alias> SET ... FROM <target> JOIN ...
SQLite yes UPDATE ... RETURNING <cols> UPDATE <target> AS t1 SET ... FROM ... WHERE ... (3.33+)
MySQL / MariaDB yes NotSupportedException UPDATE <target> JOIN ... SET <alias>.<col> = ...
ClickHouse ALTER TABLE ... UPDATE ... SETTINGS mutations_sync = 1 (reports no affected-row count) NotSupportedException NotSupportedException
In-memory NotSupportedException (query-only) NotSupportedException NotSupportedException

Notes and out-of-scope

  • UPDATE writes only the columns named in Set — there is no change tracking, so "the modified properties" cannot be inferred.
  • ClickHouse updates through ALTER TABLE ... UPDATE; the mutation is applied synchronously (SETTINGS mutations_sync = 1) but reports no affected-row count, so Update()/UpdateAsync() return 0. A predicate is required by the engine, so an update without Where renders WHERE 1.
  • Optimistic concurrency (rowversion) is a pattern on top of this surface, not a built-in API: put the expected token in Where and read 0 affected rows as a conflict. See Optimistic concurrency and change tracking. Global query filters are honoured (see Global query filters).
  • The in-memory context is query-only: Update/UpdateAsync (like every other write) throw NotSupportedException; query your own collections instead.
  • UPDATE 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.

See also

Edit this page