/DB

UPDATE

Update rows with the generated Update() builder. Targets the row by primary key, narrows the column set, and adds extra WHERE conditions.

updated 5 Jun 20264 min readv0.3.2View as Markdown

Overview

Every generated entity instance exposes an .Update() method that returns a fluent builder. By default it updates every mapped column and targets the row whose PK matches the instance. You can narrow or change the set of columns updated and add extra WHERE conditions on top of the PK filter.

Default Behavior

Without any field-selection modifier, Update() generates a SET clause for every mapped column (the primary-key columns are included too, set to their current values). The WHERE clause is always built from the PK column(s) using the values currently on the instance.

// update all non-PK columns for the matching row
user.Name = "Alice Renamed";
user.Score = 42;

int rows = await user.Update()
    .WithConnection(conn)
    .ExecuteAsync();
// SQL: UPDATE "users" SET "name" = $1, "score" = $2, ... WHERE "id" = $n

ExecuteAsync() returns Task<int>, the number of rows affected. A return value of 0 means no row matched the PK (the row does not exist or was already deleted).

Field Selection Strategies

WithAllFields: Explicit All Columns

Explicitly includes every column, including any the default behavior might exclude.

// update all columns explicitly
int rows = await user.Update()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();

WithAllFields().ExceptFields(): All Columns Except Listed

Updates all columns but skips the ones you specify. Useful for preserving immutable audit columns.

// update everything except CreatedAt
int rows = await user.Update()
    .WithConnection(conn)
    .WithAllFields()
    .ExceptFields(x => new object?[] { x.CreatedAt })
    .ExecuteAsync();

WithFields: Only Listed Columns

Updates only the columns you specify. Every other column is left unchanged in the database.

// update only Name and Email
int rows = await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.Name, x.Email })
    .ExecuteAsync();
// SQL: UPDATE "users" SET "name" = $1, "email" = $2 WHERE "id" = $3

ExceptFields: All Except Listed

Updates all columns except the ones you specify. The inverse of WithFields.

// update all columns except UpdatedAt (managed elsewhere)
int rows = await user.Update()
    .WithConnection(conn)
    .ExceptFields(x => new object?[] { x.UpdatedAt })
    .ExecuteAsync();

Custom WHERE Clause

.Where() replaces the default PK-based WHERE clause entirely with your predicate. When you supply .Where(), the PK filter is not added automatically, so include any PK condition yourself if you still need it.

// target by PK and an extra condition, expressed in one predicate
int rows = await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.Score })
    .Where(x => x.Id == user.Id && x.Status == "active")
    .ExecuteAsync();
// SQL: UPDATE "users" SET "score" = $1 WHERE "id" = $2 AND "status" = $3

Static Shorthand

User.UpdateAsync(instance, conn) is a static convenience that updates every mapped column for the matching row (it uses WithAllFields) and returns Task<int>.

// static shorthand
int rows = await User.UpdateAsync(user, conn);
NOTE
The static shorthand supports no field selection or extra WHERE conditions. Use the instance builder when you need those.

Composite Primary Keys

When an entity has a composite PK (multiple properties marked [PrimaryKey]), all PK columns are automatically included in the WHERE clause. No extra work is needed.

// UserCourse has [PrimaryKey] on both UserId and CourseId
enrollment.CompletedAt = DateTimeOffset.UtcNow;

int rows = await enrollment.Update()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.CompletedAt })
    .ExecuteAsync();
// SQL: UPDATE "user_course" SET "completed_at" = $1
//      WHERE "user_id" = $2 AND "course_id" = $3

Update With a Transaction

Pass a DbTransaction instead of a DbConnection when the update is part of a larger unit of work.

// update inside a transaction
await using var tx = await conn.BeginTransactionAsync();

user.Status = "suspended";

int rows = await user.Update()
    .WithTransaction(tx)
    .WithFields(x => new object?[] { x.Status })
    .ExecuteAsync();

await tx.CommitAsync();

Interpreting the Return Value

Return value Meaning
> 0 That many rows were updated (usually 1 for PK-based updates)
0 No matching row found; the instance's PK does not exist in the table 
// detect a missing row
int rows = await user.Update()
    .WithConnection(conn)
    .ExecuteAsync();

if (rows == 0)
    throw new InvalidOperationException($"User {user.Id} not found.");
NOTE
UPDATE uses ExceptFields to exclude columns. It has no ExcludeAutoFields, WithValuePropagation, or ExecuteReturningAsync; those are INSERT-only.

See Also