/DB

INSERT

How to insert rows into a database table using the generated Insert() builder in Socigy.OpenSource.DB v0.2.0.

updated 5 Jun 20265 min readv0.3.0View as Markdown

Overview

Every generated entity instance exposes an .Insert() method that returns a fluent builder. You choose which fields to include, optionally request that DB-generated values be propagated back to the instance, then execute.

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).

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

// C# — 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 — Skip All [Default] Except Listed

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

// C# — 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(x => new object[] { x.Id })
    .ExecuteAsync();

WithAllFields — Include Everything

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

// C# — 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();
ERROR
WithAllFields() and ExcludeAutoFields() cannot be used in the same call chain. Combining them is a programming error and will throw at runtime.

WithFields — Include Only Listed Columns

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

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

// C# — 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 SQL statement. The database returns the complete row after insert (with all generated values filled in), and the library writes those values back into the C# instance. This is the cleanest way to obtain Id, CreatedAt, sequence numbers, and any other DB-generated columns after an insert.

// C# — 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 (the most common case being the new primary key), use ExecuteReturningAsync<T>() instead of ExecuteAsync().

// C# — 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 method that inserts the row using a plain connection. It is the quickest path when you do not need field selection or value propagation.

// C# — static shorthand
bool ok = await User.InsertAsync(user, conn);
NOTE
The static shorthand does not expose field selection or value propagation options. If you need ExcludeAutoFields, WithValuePropagation, or any other option, use the instance builder instead.

Using With Transactions

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

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

// C# — 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);
  • Returns the total number of rows inserted.
  • Uses the entity's default insert plan, so auto-increment / [Default] sequence columns are skipped (the database generates them) — same as ExcludeAutoFields().
  • 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/logged 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