Mise Returned Rows

Returned rows

A returning command asks the database to send values back from an INSERT, UPDATE, or DELETE. Use it to obtain generated IDs or the row state produced by a write without a separate SELECT.

Return an inserted course

var command = new PostgreSqlCommandBuilder();
command.Returning("course_id", "title", "capacity");

command.InsertInto($"{CourseTbl.Table:raw}")
    .Columns($"title, capacity")
    .Values($"{title}, {capacity}");

Result<CourseRow> course = await writer.ReturningFirstOrNotFoundAsync<CourseRow>(
    command,
    cancellationToken
);

Returning takes unquoted column names and quotes them for the database. Pass "title", rather than CourseTbl.TitleCol, which contains a qualified SQL fragment. Select all names mapped by CourseRow.

Keep the concrete builder variable when calling dialect-specific methods. Common fluent methods return the base CommandBuilder, which does not expose Returning or OutputInserted. Configure output before those calls, or call it through the retained concrete variable as above.

Return changed or deleted rows

var command = new PostgreSqlCommandBuilder();
command.Returning("course_id", "title", "capacity");

command.Update($"{CourseTbl.Table:raw}")
    .Set($"title = {title}")
    .Where($"{CourseTbl.CourseIdCol:raw} = {courseId}");

var course = await writer.ReturningFirstOrNotFoundAsync<CourseRow>(
    command,
    cancellationToken
);

ReturningFirstOrNotFoundAsync reads the first returned row and ignores later ones. It returns NotFound when none are returned. ReturningListAsync returns all rows and succeeds with an empty list when there are none.

Database support

Database	INSERT	UPDATE	DELETE
PostgreSQL	Returning	Returning	Returning
SQLite	Returning	Returning	Returning
SQL Server	OutputInserted	OutputInserted	No fluent output method
MySQL	No fluent returning method	No fluent returning method	No fluent returning method
MariaDB	Returning on 10.5+	No fluent returning method	Returning

SQLite RETURNING requires SQLite 3.35 or later. These methods choose SQL syntax; the connected server must support it. Output columns can be configured once and cannot be empty.

For SQL Server, retain a SqlServerCommandBuilder and call OutputInserted("course_id", "title", "capacity"). It emits INSERTED-qualified OUTPUT columns in the correct statement position.

Return one scalar

var command = new PostgreSqlCommandBuilder();
command.Returning("course_id");

command.InsertInto($"{CourseTbl.Table:raw}")
    .Columns($"title, capacity")
    .Values($"{title}, {capacity}");

Result<int?> courseId = await writer.ExecuteScalarAsync<int?>(command, cancellationToken);

The scalar terminal reads the first cell. SQL NULL or no row produces default(T), so a nullable value type preserves absence. Match T to the provider's returned type.

MySQL generated IDs

MySqlQueryBuilder.LastInsertId() builds SELECT LAST_INSERT_ID(). Execute it through the same writer after the INSERT, before another insert changes that connection's value. DbWriter inherits the reader methods, so it can call ScalarAsync<T> on that query using the same connection and transaction.

Choose the scalar type matching the provider's returned ID value. A separate reader can open a different connection and cannot reliably retrieve the first writer's last insert ID.

See Inserts, Updates, Deletes, and Result rows.