/DB

INSERT

Insert rows with the generated Insert() builder. Choose your fields, propagate DB-generated values back, and batch thousands of rows in a few round-trips.

updated 2 Sept 20269 min readv0.3.8View as Markdown

Overview

Every generated entity instance exposes an .Insert() method that returns a PostgresqlInsertCommandBuilder<T>. You choose which fields to include, optionally request that DB-generated values be propagated back to the instance, then execute. ExecuteAsync() returns Task<bool>.

Basic Insert

The simplest insert includes all columns, including any [Default] columns (those whose values the database generates on insert, such as id, created_at, or sequence numbers).

// insert a new user, including all fields
var user = new User { Id = Guid.NewGuid(), Name = "Alice", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();

Field Selection Strategies

The builder supports five mutually exclusive strategies for choosing which columns appear in the INSERT. Pick exactly one per insert.

ExcludeAutoFields: Skip All [Default] Columns

Omits every column marked [Default] (columns whose value the database supplies automatically). This is the most common strategy, because it lets PostgreSQL handle id, created_at, sequences, and so on.

// let the DB generate Id and CreatedAt
var user = new User { Name = "Bob", Status = "active", Score = 0 };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteAsync();

ExcludeAutoFields With Exceptions

Skips all [Default] columns except those you list explicitly. Useful when you want to override one default (supply your own Id, say) while still letting the database control the others.

// supply Id manually, let DB handle CreatedAt
var user = new User { Id = Guid.NewGuid(), Name = "Carol", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields(include => new object?[] { include.Id })
    .ExecuteAsync();

WithAllFields: Include Everything

Forces all columns (including [Default] ones) into the INSERT. Use when you want to supply every value yourself.

// insert with all fields, including Id and timestamps
var user = new User
{
    Id = Guid.NewGuid(),
    Name = "Dave",
    Status = "active",
    CreatedAt = DateTimeOffset.UtcNow
};

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithAllFields()
    .ExecuteAsync();
WARNING
WithAllFields() and ExcludeAutoFields() cannot appear in the same chain. Combining them is a programming error and throws at runtime.

WithFields: Include Only Listed Columns

Inserts only the columns you specify. Every other column is omitted from the statement.

// insert only Name and Status
var user = new User { Name = "Eve", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .WithFields(x => new object?[] { x.Name, x.Status })
    .ExecuteAsync();

ExcludeFields: Exclude Listed Columns

Includes all columns except those you specify. The inverse of WithFields.

// insert everything except AuditLog
bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeFields(x => new object?[] { x.AuditLog })
    .ExecuteAsync();

Value Propagation (RETURNING *)

.WithValuePropagation() appends RETURNING * to the statement. The database returns the complete row after insert, generated values filled in, and the library writes those values back onto the instance. This is the cleanest way to obtain Id, CreatedAt, sequence numbers, and any other DB-generated columns.

// write generated values back to the instance
var user = new User { Name = "Frank", Status = "active" };

bool ok = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

// user.Id and user.CreatedAt are now populated from the database
Console.WriteLine(user.Id);
Console.WriteLine(user.CreatedAt);

Returning a Specific Column Value

When you only need one DB-generated value (most often the new primary key), use ExecuteReturningAsync<T>() instead of ExecuteAsync(). It returns Task<T?> and requires the database column name as a string argument.

// return the generated Id
var user = new User { Name = "Grace", Status = "active" };

Guid? newId = await user.Insert()
    .WithConnection(conn)
    .ExcludeAutoFields()
    .ExecuteReturningAsync<Guid>("id");

if (newId is not null)
    Console.WriteLine($"Created user {newId}");

The string argument is the column name in the database (snake_case), not the C# property name.

Static Shorthand

User.InsertAsync(instance, conn) is a static convenience that inserts the row over a plain connection and returns Task<bool>. It is the quickest path when you do not need field selection or value propagation.

// static shorthand
bool ok = await User.InsertAsync(user, conn);
NOTE
The static shorthand exposes no field selection or value propagation. If you need ExcludeAutoFields, WithValuePropagation, or any other option, use the instance builder.

Using With Transactions

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

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

bool ok = await user.Insert()
    .WithTransaction(tx)
    .ExcludeAutoFields()
    .WithValuePropagation()
    .ExecuteAsync();

await tx.CommitAsync();

Bulk Insert with InsertMultipleAsync

To insert a whole collection, use the static InsertMultipleAsync instead of a command per row. It builds batched multi-row INSERT ... VALUES (…),(…),… commands, one command per chunk, which is dramatically faster than looping. It returns Task<int>, the total number of rows inserted.

// insert many rows in one (or a few) round-trips
var users = new List<User>
{
    new() { Name = "Alice", Status = "active" },
    new() { Name = "Bob",   Status = "active" },
    // ... thousands more ...
};

int inserted = await User.InsertMultipleAsync(users, conn);

The signature is InsertMultipleAsync(rows, conn, transaction = null, fields = InsertFields.Default, keep = null, cancellationToken = default). The InsertFields enum and keep selector live in Socigy.OpenSource.DB.Core.CommandBuilders.

  • Returns the total number of rows inserted.
  • fields selects which columns you write versus which the server fills, replacing the old includeAutoFields / excludeDbDefaults booleans (new in 0.3.5):
    • InsertFields.Default (the default): auto-increment columns are skipped (the database generates them), but other [Default] columns send the property's current value (the CLR default if you didn't set it), so a [Default] column left unset is written as default(T), not filled by the server.
    • InsertFields.ServerDefaults: omits both auto-increment and [Default] columns so the server default applies, the bulk equivalent of the fluent ExcludeAutoFields(). It omits every [Default] column unconditionally — see Letting the server fill columns.
    • InsertFields.ServerDefaultsWhenUnset (new in 0.3.8): like ServerDefaults, but a [Default] column is omitted only where the row still holds its CLR type default, so a value you actually assigned is written. The fluent equivalent is ExcludeAutoFieldsWhenUnset().
    • InsertFields.IncludeAutoIncrement: additionally writes auto-increment columns yourself, the equivalent of the fluent WithAllFields().
  • keep writes specific [Default] columns from your own values while the server fills the remaining auto/[Default] columns, the convenience equivalent of the fluent ExcludeAutoFields(include). Providing keep implies ServerDefaults behavior for the unlisted columns: keep: u => new object?[] { u.Id } writes your Id and lets the server fill the rest. Because the static methods take transaction before fields, call them with the fields: named argument: await User.InsertMultipleAsync(users, conn, fields: InsertFields.ServerDefaults).
  • Automatically chunks so each command stays under PostgreSQL's 65,535-parameter limit (maxRowsPerBatch ≈ 65535 / columnCount). A 10,000-row insert of a 6-column table becomes a handful of commands rather than 10,000.
  • [Encrypted] and JSON columns are handled (bound as bytea / jsonb).
  • Each command is traced through the diagnostics pipeline as a single INSERT.

Pass a transaction to make the whole batch atomic with other work:

await using var tx = await conn.BeginTransactionAsync();
await User.InsertMultipleAsync(users, conn, tx);
await tx.CommitAsync();
NOTE
InsertMultipleAsync uses the default insert plan (no per-row field selection or value propagation). If you need WithValuePropagation or custom field selection, use the per-instance .Insert() builder.
NOTE
InsertFields.ServerDefaults (and the keep selector) is available on every convenience path so the server default can be honored without the fluent builder: the context db.Users.InsertAsync(user, InsertFields.ServerDefaults) and InsertMultipleAsync(...), the static User.InsertMultipleAsync(rows, conn, fields: InsertFields.ServerDefaults), and BulkCopy.InsertMultipleCopyAsync(rows, conn, fields: InsertFields.ServerDefaults). Use keep: u => new object?[] { u.Id } to write specific [Default] columns yourself (a manual id, say) while the server fills the rest. (New in 0.3.5, replacing the 0.3.4 excludeDbDefaults / includeAutoFields booleans, where previously only the fluent Insert().ExcludeAutoFields() could omit [Default] columns.)
TIP
For extreme volumes (hundreds of thousands of rows) PostgreSQL's binary COPY protocol via NpgsqlBinaryImporter is still the throughput champion. InsertMultipleAsync is the sweet spot for everyday batches up to tens of thousands.

Letting the server fill columns

InsertFields.ServerDefaults omits every [Default] column from the INSERT, whether or not the row carries a value for it. That is deliberate — it is how you say "the database owns these" — but it is decided by an attribute in a different file from the call site, and it is silent when it is not what you meant:

[Table("outbox")]
public partial class OutboxMessage
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)] public Guid Id { get; set; }
    [Default(DbDefaults.Time.Now)] public DateTime OccurredAt { get; set; }
    public string Payload { get; set; } = "";
}

var message = new OutboxMessage { OccurredAt = producedAt, Payload = json };
await db.OutboxMessages.InsertAsync(message, InsertFields.ServerDefaults);
// occurred_at in the database is now the server's now(), NOT producedAt.

Adding [Default] to an existing column therefore changes the behaviour of every ServerDefaults insert that does not name it, with no compile error, no runtime error, and no log line. Three ways to handle it:

Name the column in keep. Explicit, and unaffected by the row's value:

await db.OutboxMessages.InsertAsync(message, [nameof(OutboxMessage.OccurredAt)]);

Use ServerDefaultsWhenUnset. A [Default] column is omitted only where the property still holds its CLR type default (0, false, null, default(DateTime), Guid.Empty), so a value you assigned survives. This is usually what "let the server fill it in" means, and it makes keep unnecessary in the common case:

await db.OutboxMessages.InsertAsync(message, InsertFields.ServerDefaultsWhenUnset);
// occurred_at = producedAt; Id, left at Guid.Empty, still gets the server default.

fields and keep compose — they are not alternatives. keep names the columns you always write by hand; fields decides what happens to the rest. So a call site that already passes a keep list can adopt the new mode without giving it up:

await db.OutboxMessages.InsertAsync(message, InsertFields.ServerDefaultsWhenUnset,
                                    keep: r => new object?[] { r.Id });
// Id: always written (named in keep).
// OccurredAt: written because the row set it; omitted on a row that did not.

// The AOT-safe spelling takes the same option.
await db.OutboxMessages.InsertAsync(message, [nameof(OutboxMessage.Id)],
                                    InsertFields.ServerDefaultsWhenUnset);

// And on the fluent builder.
await message.Insert().ExcludeAutoFieldsWhenUnset(nameof(OutboxMessage.Id))
    .WithConnection(conn).ExecuteAsync();

Audit the existing call sites. The generator reports SCGDB027 at every ServerDefaults insert whose row type has a [Default] column not named in keep. It is Info severity, so it stays out of build output; promote it when you want the list:

[*.cs]
dotnet_diagnostic.SCGDB027.severity = warning
NOTE
On a non-nullable value type, "the caller set false" and "the caller set nothing" are indistinguishable at runtime — nothing can tell them apart. ServerDefaultsWhenUnset resolves that towards the server default only when the value looks genuinely absent, rather than always. A nullable column has no such ambiguity: null means unset, and an explicit 0 is written.
NOTE
On the multi-row and COPY paths, ServerDefaults resolves the column set once and reuses one prepared plan for the whole batch. ServerDefaultsWhenUnset depends on each row, so rows are grouped by which columns they leave unset and one plan is prepared per distinct group. A batch whose rows agree — the usual case — costs exactly the same; a batch that disagrees is logged at Information naming the columns responsible. See Bulk COPY.

See Also