/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 5 Jun 20265 min readv0.3.2View 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, includeAutoFields = false, cancellationToken = default).

  • Returns the total number of rows inserted.
  • By default (includeAutoFields = false), [Default] and sequence columns are skipped so the database generates them, the same as ExcludeAutoFields(). Pass includeAutoFields: true to send those columns yourself.
  • 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.
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.

See Also