/DB

INSERT

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

updated 3 May 20264 min readv0.1.82

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 Pattern

For bulk inserts, open a single transaction and loop over instances. All inserts share the same transaction and are committed atomically.

// C# — bulk insert with a shared transaction
await using var tx = await conn.BeginTransactionAsync();

foreach (var user in usersToImport)
{
    await user.Insert()
        .WithTransaction(tx)
        .ExcludeAutoFields()
        .ExecuteAsync();
}

await tx.CommitAsync();
TIP
For very large bulk inserts (thousands of rows), consider using PostgreSQL's COPY protocol via NpgsqlBinaryImporter for maximum throughput. The builder-based loop above is appropriate for up to a few hundred rows.

See Also