/DB

UPDATE

How to update rows in a database table using the generated Update() builder in Socigy.OpenSource.DB v0.1.82.

updated 3 May 20264 min readv0.1.82

Overview

Every generated entity instance exposes an .Update() method that returns a fluent builder. By default, the builder updates all non-PK columns 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 non-PK column. The WHERE clause is always built from the PK column(s) using the values currently on the instance.

// C# — 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 that the default behavior might exclude.

// C# — 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.

// C# — 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.

// C# — 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 non-PK columns except the ones you specify. The inverse of WithFields.

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

Additional WHERE Conditions

.Where() adds extra conditions to the WHERE clause beyond the PK filter. Both the PK condition and the additional conditions must be satisfied for a row to be updated.

// C# — only update if the row is currently active
int rows = await user.Update()
    .WithConnection(conn)
    .WithFields(x => new object[] { x.Score })
    .Where(x => 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 method that updates all non-PK columns for the matching row. It is equivalent to calling .ExecuteAsync() with no field-selection modifier.

// C# — static shorthand
int rows = await User.UpdateAsync(user, conn);
NOTE
The static shorthand does not support field selection or extra WHERE conditions. Use the instance builder when you need those options.

Composite Primary Keys

When an entity has a composite PK (multiple properties marked [PrimaryKey]), all PK columns are automatically included in the WHERE clause. You do not need to do anything special.

// C# — 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.

// C# — 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 
// C# — detect missing row
int rows = await user.Update()
    .WithConnection(conn)
    .ExecuteAsync();

if (rows == 0)
    throw new InvalidOperationException($"User {user.Id} not found.");

See Also