Table of Contents

Data modification (INSERT)

nextorm adds a small, explicit DML surface: CreateInsertBuilder<TEntity>() builds a parameterised INSERT ... VALUES and returns the affected-row count, the generated key or the inserted rows. There is no change tracking or unit of work: every terminal issues exactly one command.

Prerequisites: Querying and projections · Entities and metadata · Provider overview

Overview

nextorm has always been a query builder; writing a row is the same idea applied to INSERT. The builder collects the target table and the columns to write, the active dialect renders the statement (parameterising every value, exactly like a query), and the context executes it once:

using var ctx = new DataContextBuilder().UsePostgres(connectionString).CreateDataContext();

var affected = ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Id, 1)
    .Value(x => x.Name, "a")
    .Insert();

There is deliberately no unit of work, change tracker or entity graph: commands are issued and executed explicitly, one at a time. See Limitations and out-of-scope features.

Declaring keys, identity and computed columns

The insert builder uses the entity mapping, so it needs to know which columns are database-generated. Declare them with the standard data-annotation attributes (on the interface when the entity is interface-mapped) or fluently on the metadata builder:

Concept Attribute Fluent
Entity key [Key] Property(x => x.Id).Key()
Identity / auto-increment [DatabaseGenerated(DatabaseGeneratedOption.Identity)] Property(x => x.Id).Identity()
Computed (never written) [DatabaseGenerated(DatabaseGeneratedOption.Computed)] Property(x => x.Total).Computed() 
[SqlTable("simple_entity")]
public interface ISimpleEntity
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("id")]
    long Id { get; set; }
    [Column("name")]
    string? Name { get; set; }
}

Or declare the same mapping fluently on a plain POCO, with no attributes at all - the delegate goes to CreateInsertBuilder<T> (the identical cfg can be passed to From<T> to register the mapping beforehand):

public class Order            // no attributes
{
    public long Id { get; set; }
    public decimal Total { get; set; }
    public decimal TotalWithVat { get; set; }
}

ctx.CreateInsertBuilder<Order>(cfg =>
{
    cfg.Table("orders");
    cfg.Property(x => x.Id).HasColumnName("id").Key().Identity();
    cfg.Property(x => x.Total).HasColumnName("total");
    cfg.Property(x => x.TotalWithVat).HasColumnName("total_with_vat").Computed();
});

When no property is declared with [Key], the metadata infers the key from the Id or <TypeName>Id convention. Identity and Computed are not inferred: declare them explicitly, or they are treated as ordinary writable columns.

Writing values

Every value is bound as a parameter. Several shapes are available; they are mutually exclusive within one builder.

Column by column (single row). The value is a CLR value:

ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Id, 1)
    .Value(x => x.Name, "a")
    .Insert();

-- PostgreSQL
insert into simple_entity (id, name) values (@p0, @p1)

The value may also be another mapped column of the same entity, which renders a column reference instead of a parameter:

ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Name, x => x.OtherName)
    .Insert();

-- PostgreSQL: the value is a column reference, not a parameter
insert into simple_entity (name) values (other_name)

Only a mapped property access (or a parameter-free expression) is accepted here; anything else throws NotSupportedException.

A whole entity. All mapped, writable columns are written; Identity and Computed columns are excluded automatically:

ctx.CreateInsertBuilder<ISimpleEntity>().Values(entity).Insert();

-- PostgreSQL: Identity/Computed columns are excluded
insert into simple_entity (name) values (@p0)

A batch of entities. The same as the previous form, repeated over a sequence:

ctx.CreateInsertBuilder<ISimpleEntity>().Values(new[] { e1, e2, e3 }).Insert();

-- PostgreSQL
insert into simple_entity (name) values (@p0), (@p1), (@p2)

A batch from a source. Values(source, mapping) writes one row per element and lets the mapping select the columns. A concrete entity uses an object initializer, an interface-mapped entity uses an anonymous type; every member name must match an entity property:

ctx.CreateInsertBuilder<Order>()
    .Values(dtos, d => new Order { Total = d.Total })
    .Insert();

ctx.CreateInsertBuilder<ISimpleEntity>()
    .Values(dtos, d => new { Name = d.Name })
    .Insert();

-- PostgreSQL: one row per element, every value a parameter
insert into orders (total) values (@p0), (@p1)

insert into simple_entity (name) values (@p0), (@p1)

A batch from a query (server-side). The same Values(source, mapping) signature also accepts an EntityBuilder<TSource>, which renders INSERT ... SELECT and reads the rows in the database instead of the client - useful for copying or filtering server-side. The mapping selects the written columns exactly as above:

ctx.CreateInsertBuilder<IOrder>()
    .Values(ctx.From<OrderDto>().Where(d => d.Active), d => new { d.Id, d.Amount })
    .Insert();

-- PostgreSQL: rendered as INSERT ... SELECT
insert into orders (id, amount) select id, amount
 from order_dto
 where active

The source is an ordinary query builder (Where/Join/GroupBy/OrderBy/...), and its parameters are carried into the insert. A source that declares a CTE (With) is supported: the WITH clause is hoisted to precede INSERT (with c as (...) insert into ... select ... from c), because a data-modifying CTE must sit at the top level. A runtime SqlFunctions.Parameter placeholder is not supported, and DEFAULT is not valid as a selected value - both throw NotSupportedException. Returning/ReturningIdentity/ReturningKey compose with it wherever the provider supports them, and .Single() throws when the query writes more than one row. The target-column rules (member names, no Computed target) are the same as the client-side mapping.

A CTE source is a valid server-side source: build the scope with With, read the CTE by name and pass the resulting EntityBuilder<TableAlias> to Values. CTE columns are read by name, so the mapping must use the CTE body's output aliases:

var source = ctx
    .With("recent", ctx.From<ISimpleEntity>()
        .Where(s => s.Id > 1)
        .Select(s => new { s.Id }))
    .From("recent");

ctx.CreateInsertBuilder<IOrder>()
    .Values(source, t => new { CustomerId = t.GetInt32("id") })
    .Insert();
// with recent as (select id
// from simple_entity where (id > 1))
// insert into "order" (customer_id) select id
// from recent as "t1"

On PostgreSQL the source may even be a data-modifying CTE (ctx.With(name, insert.Returning(...))); see Data-modifying CTE (PostgreSQL).

A table-valued function is a valid source too: the runtime-factory source yields an EntityBuilder<TSource>, so unnest/generate_series can feed the insert. The array is a real parameter, so pass it as a captured variable - the runtime SqlFunctions.Parameter placeholder is rejected (as above):

var ids = new long[] { 1, 2, 3 };

ctx.CreateInsertBuilder<IdRow>()
    .Values(ctx.From(() => SqlFunctions.Postgres.unnest(ids)), r => new { r.Value })
    .Insert();
// insert into id_row (value) select unnest as "Value"
// from (select unnest
// from unnest(@ids)) as "t1"

unnest and generate_series are PostgreSQL-only.

A scalar column. When the entity has exactly one writable column (the others are Identity/ Computed), pass the value or values directly, without a selector or a projection:

ctx.CreateInsertBuilder<ISimpleEntity>().Value("a").Insert();                      // one row
ctx.CreateInsertBuilder<ISimpleEntity>().Values(new[] { "a", "b", "c" }).Insert(); // three rows

-- PostgreSQL
insert into simple_entity (name) values (@p0)

insert into simple_entity (name) values (@p0), (@p1), (@p2)

The scalar form needs exactly one writable column: with several, use a mapping or name the column instead (both throw InvalidOperationException when called on a scalar). With none (every column is Identity/Computed) there is nothing to pass - insert the all-defaults row with ctx.CreateInsertBuilder<T>().Insert() (see Defaults).

A batch by column. For column-oriented data, chain one call per column; every column must supply the same number of values:

ctx.CreateInsertBuilder<Product>()
    .Values(x => x.Name, new[] { "a", "b" })
    .Values(x => x.Price, new[] { 1m, 2m })
    .Insert();

-- PostgreSQL
insert into product (name, price) values (@p0, @p1), (@p2, @p3)

Writing to a Computed column always throws NotSupportedException; an Identity column may be set explicitly (its value is then taken from the input, not generated).

Defaults. A single value can be the column's database DEFAULT, and an entity whose every column is generated (Identity/Computed) needs no values at all - it inserts a single all-defaults row:

ctx.CreateInsertBuilder<ISimpleEntity>().Value(x => x.Name, SqlDefault.Value).Insert();

// every column is Identity/Computed:
ctx.CreateInsertBuilder<AuditRow>().Insert();

-- PostgreSQL
insert into simple_entity (name) values (default)

insert into audit_row default values

The all-defaults row uses the provider's native form (DEFAULT VALUES on PostgreSQL, SQL Server and SQLite; () VALUES () on MySQL/MariaDB). Writing DEFAULT as a value is not expressible on SQLite (omit the column instead - the same default is applied), and ClickHouse supports neither form; both throw NotSupportedException. SqlDefault.Value also works as a mapped member in a Values(source, mapping) projection.

Data-modifying CTE (PostgreSQL)

PostgreSQL is the only supported provider that accepts a data-modifying statement as a CTE body (WITH <name> AS (<INSERT|UPDATE|DELETE> ... RETURNING ...)), gated by SupportsDataModifyingCtes. Start the scope with the With(name, insert), With(name, update) or With(name, delete) overload: each takes a row-returning mutation and returns a MutationCteQuery<TResult> typed by the RETURNING projection. Read the returned rows with From(name) (typed):

var rows = dataContext
    .With("ins", dataContext.CreateInsertBuilder<IOrder>()
        .Value(x => x.CustomerId, 7)
        .Returning(x => new { x.Id, x.Total }))
    .From("ins")
    .Where(r => r.Total > 0)
    .Select(r => new { r.Id })
    .ToList();

-- PostgreSQL
with ins as (insert into orders (customer_id) values (@p0) returning id, total) select id
 from ins as "t1"
 where (t1.total > 0)

From(name) reads the mutation's RETURNING columns with the full operator set — filter, join, aggregate, order and page — and the projection may rename them (Returning(x => new { x.Total }) is read back as r.Total). FromTable reads an accompanying read CTE as a regular TableAlias source:

var rows = dataContext
    .With("ins", dataContext.CreateInsertBuilder<IOrder>()
        .Value(x => x.CustomerId, 7)
        .Returning(x => new { x.Id }))
    .With("src", dataContext.From<IOrder>().Select(x => new { x.Total }))
    .FromTable("src")
    .Select(t => new { total = t["Total"].AsInt })
    .ToList();

-- PostgreSQL
with ins as (insert into orders (customer_id) values (@p0) returning id), src as (select total
 from orders) select Total
 from src

The rows a data-modifying CTE inserts are not visible to the other CTEs of the same statement: PostgreSQL executes the sub-statements concurrently against the same snapshot. Read them back through From(name), or in a later statement.

The body may be a VALUES insert or an INSERT ... SELECT (Values(source, mapping) composes with Returning); any CTE the source declares is dropped, because it is already declared by the enclosing WITH. Declare read CTEs first so the mutation can reference them: CteQuery.With(name, insert) appends the data-modifying CTE after the read CTEs collected so far, so its body may read them:

var scope = dataContext.With("src",
    dataContext.From<ICustomer>().Where(c => c.Active).Select(c => new { c.Id }));

var rows = scope
    .With("ins", dataContext.CreateInsertBuilder<IOrder>()
        .Values(scope.From("src"), a => new { CustomerId = a.GetInt32("Id") })
        .Returning(x => new { x.Id }))
    .From("ins")
    .Select(r => new { r.Id })
    .ToList();

-- PostgreSQL
with src as (select id
 from customers
 where active), ins as (insert into orders (customer_id) select Id
 from src returning id) select id
 from ins as "t1"

A data-modifying CTE can also feed a main INSERT ... SELECT: the WITH is hoisted to precede INSERT (PostgreSQL requires the data-modifying statement at the top level):

var source = dataContext
    .With("ins", dataContext.CreateInsertBuilder<IOrder>()
        .Value(x => x.CustomerId, 7)
        .Returning(x => new { x.CustomerId }))
    .From("ins");

dataContext.CreateInsertBuilder<IOrder>()
    .Values(source, r => new { r.CustomerId })
    .Insert();

-- PostgreSQL: WITH hoisted before the main INSERT
with ins as (insert into orders (customer_id) values (@p0) returning customer_id) insert into orders (customer_id) select "CustomerId"
 from ins as "t1"

The CTE body may also be an UPDATE or DELETE (With(name, update) / With(name, delete)); those bodies carry their own statements, so they are shown with them — Data modification (UPDATE) and Data modification (DELETE).

A statement whose WITH contains a data-modifying CTE is never stored in the plan cache (it is side-effecting). Other providers reject a data-modifying CTE body with NotSupportedException, because their CTE body must be a SELECT. For general read CTEs see Common table expressions.

Reading the generated key

The key-reading terminals all return an InsertReturningBuilder<TEntity,TResult> and are read through its Single()/ToList() terminals (see Returning the inserted rows):

Terminal Selects Requires
ReturningIdentity(x => x.Id) a column named by the selector the column is declared Identity; the provider has RETURNING/OUTPUT
ReturningIdentity<long>() the provider's scalar identity function ISqlDialect.SupportsIdentityFunction
ReturningKey<long>() the key column resolved from metadata the entity has a key; RETURNING/OUTPUT or the identity-function fallback

The provider's native form is used:

Provider Column selector / key Identity function
SQLite INSERT ... RETURNING id SELECT last_insert_rowid()
PostgreSQL INSERT ... RETURNING id SELECT lastval()
SQL Server OUTPUT inserted.id SELECT SCOPE_IDENTITY() (same batch as the insert)
MySQL / MariaDB SELECT LAST_INSERT_ID() fallback SELECT LAST_INSERT_ID()
ClickHouse not expressible not expressible
In-memory not expressible not expressible (read-only) 
long fromColumn = ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Name, "a")
    .ReturningIdentity(x => x.Id)
    .Single();

long fromFunction = ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Name, "a")
    .ReturningIdentity<long>()
    .Single();

long fromMetadata = ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Name, "a")
    .ReturningKey<long>()
    .Single();

-- PostgreSQL
-- ReturningIdentity(x => x.Id) and ReturningKey<long>() name the column:
insert into simple_entity (name) values (@p0) returning id
-- ReturningIdentity<long>() appends the provider's identity function in the same batch:
insert into simple_entity (name) values (@p0); select lastval()
  • ReturningIdentity(selector) requires the selected column to be declared Identity; ReturningKey<TKey>() resolves the key from metadata ([Key], .Key() or the Id/<Type>Id convention) and rejects an entity with no key or several keys.
  • Where the provider has no RETURNING/OUTPUT (MySQL/MariaDB) an identity column falls back to LAST_INSERT_ID(); a non-identity key is rejected with NotSupportedException rather than returning the wrong value.
  • ReturningIdentity<TKey>() never names a column: it appends the provider's identity function to the insert in the same batch, so SQL Server keeps SCOPE_IDENTITY() correct in the presence of triggers. Prefer the selector form there.
  • ClickHouse and the in-memory provider throw NotSupportedException.

Returning the inserted rows

Beyond the single generated key, Returning() materialises the rows the database actually wrote - including identity and computed columns - through the provider's native RETURNING/OUTPUT form, without a second SELECT:

var row = ctx.CreateInsertBuilder<Order>()
    .Value(x => x.Name, "a")
    .Returning()
    .Single();                        // an Order carrying the generated Id

var projected = ctx.CreateInsertBuilder<Order>()
    .Value(x => x.Name, "a")
    .Returning(x => new { x.Id, x.Name })
    .Single();                        // an anonymous { Id, Name }

var rows = ctx.CreateInsertBuilder<Order>()
    .Values([o1, o2])
    .Returning()
    .ToList();                        // IReadOnlyList<Order>, one entry per inserted row
  • Returning() returns the whole mapped entity; Returning(projection) returns the projected shape. The projection may be the identity, a single mapped property, an anonymous type, a positional constructor or a member-init, and may only reference mapped properties.
  • Terminals: Single()/SingleAsync() expect a single-row insert and throw InvalidOperationException on a batch; ToList()/ToListAsync() work for both and return IReadOnlyList<TResult>.
  • The rows are materialised with the same projection pipeline as a query, so the entity form returns the entity and the projection form returns its shape.
  • ToSql() renders the statement - including the RETURNING/OUTPUT list - without executing it.
Provider Form Support
SQLite INSERT ... RETURNING <cols> (3.35.0+) yes
PostgreSQL INSERT ... RETURNING <cols> yes
SQL Server INSERT ... OUTPUT inserted.<cols> (placed between the column list and VALUES) yes
MySQL — NotSupportedException
MariaDB — (the dialect is MySQL-based) NotSupportedException
ClickHouse — NotSupportedException
In-memory — (read-only) NotSupportedException

Ordering. The rows arrive in result-set order, but no provider guarantees that order matches the input order of a multi-row insert (SQLite documents the output order as arbitrary). Treat ToList() as an unordered set and do not rely on rows[i] matching input i.

Whole entity versus interface. Returning() needs a concrete TEntity to materialise. For an interface-mapped entity either project the columns (Returning(x => new { x.Id, x.Name })) or use the class that implements the interface; Returning() on the interface itself throws a NotSupportedException at execution.

Writing the modified rows into a table (OUTPUT INTO)

SQL Server can write the modified rows into an existing table instead of (or in addition to) returning them to the client, through the OUTPUT ... INTO <target>(columns) form. Start from a returning builder and call OutputInto(targetTable):

// write the inserted rows into audit_log; nothing is returned to the client
var written = ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Name, "a")
    .Returning(x => new { x.Id, x.Name })
    .OutputInto("audit_log")
    .Execute();                       // int: affected-row count

// write into audit_log and also return the same rows to the client (second OUTPUT)
var rows = ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Name, "a")
    .Returning(x => new { x.Id, x.Name })
    .OutputIntoThenOutput("audit_log")
    .ToList();                        // IReadOnlyList<{ Id, Name }>

-- SQL Server
insert into simple_entity (name) output inserted.id, inserted.name into audit_log (id, name) values (@p0)

insert into simple_entity (name) output inserted.id, inserted.name into audit_log (id, name) output inserted.id, inserted.name values (@p0)
  • OutputInto(...) returns an OutputIntoBuilder whose only terminals are Execute()/ExecuteAsync() (affected-row count) and ToSql(). It has no row terminals, because nothing reaches the client.
  • OutputIntoThenOutput(...) returns the ordinary returning builder, so Single()/ToList() still work; the statement carries both the INTO and the client OUTPUT clauses.
  • The target is an explicit table name that must already exist with columns that have the same names as the selected output columns (the selected column list is reused as the target column list); the target name and columns are quoted like any other identifier.
  • The same two methods are available on the UPDATE and DELETE returning builders (a DELETE reads the removed row through the deleted alias). A target name that is null or empty, or the identity-function form (ReturningIdentity<TKey>(), which selects no column), throws.

Writing into a table variable (OutputIntoTableVariable)

The target may also be a table variable declared in the same batch. Call OutputIntoTableVariable(variableName, columnDefinitions) instead of OutputInto; nextorm emits the declaration, the DML and the read-back as one batch, so the variable is still in scope when the rows are read:

var sql = ctx.CreateInsertBuilder<ISimpleEntity>()
    .Value(x => x.Name, "a")
    .Returning(x => new { x.Id, x.Name })
    .OutputIntoTableVariable("@t", "id bigint, name nvarchar(100)")
    .ToSql();

-- SQL Server (one batch)
declare @t table (id bigint, name nvarchar(100)); insert into simple_entity (name) output inserted.id, inserted.name into @t (id, name) values (@p0); select id, name
 from @t

The same method is available on the UPDATE and DELETE returning builders (a DELETE reads the removed row through the deleted alias), keyed to the mutation the same way as OutputInto.

  • variableName must be a bare table-variable name: @ followed by a letter or underscore and then letters, digits and underscores (@[A-Za-z_]\w*). A bracketed or quoted identifier ([@t], "@t") is an existing table, not a variable, and is rejected with ArgumentException; so is a name without the leading @, and a null or empty declaration.
  • columnDefinitions is the caller-supplied, trusted body of the DECLARE @t TABLE (...) text. It is embedded verbatim in the SQL and is not a parameter and not validated or escaped — never pass user input there. The declared columns are ordinary storage columns: nextorm does not copy IDENTITY, COMPUTED or DEFAULT markers from the source, so a generated identity value is simply stored into the plain declared column.
  • The selected output columns are reused as the target and read-back column list, so the declared columns must have the same names and compatible types. The read-back selects exactly those mapped columns — never SELECT *.
  • No row order is guaranteed: the rows arrive in whatever order the engine writes them into the variable, so treat the read-back as an unordered set.
  • OutputIntoTableVariable(...) returns the same OutputIntoBuilder as OutputInto, with the same terminals Execute()/ExecuteAsync() (the DML affected-row count) and ToSql() — there are no row terminals. The batch's final SELECT is what a raw execution reads back; Execute() discards that row set, and the read-back offers no @@ROWCOUNT/rows-affected handle of its own.
  • Only SQL Server implements ISqlDialect.SupportsOutputInto; every other provider throws NotSupportedException when the statement renders.

The three forms differ in where the modified rows go and what reaches the client:

Form Target Returns to the client Terminals
OutputInto(targetTable) An existing table that must already be present Nothing Execute()/ExecuteAsync() (affected-row count), ToSql()
OutputIntoThenOutput(targetTable) An existing table The modified rows (second OUTPUT) Single()/ToList()/… plus ToSql()
OutputIntoTableVariable("@t", definitions) A table variable declared in the same batch The read-back rows of the final SELECT, visible to a raw execution (Execute() discards them) Execute()/ExecuteAsync() (affected-row count), ToSql()

SQL Server OUTPUT/OUTPUT INTO support is tracked in issue #15 (closed).

Batch-scoped capture (BatchBuilder.OutputIntoTableVariable)

The fluent form above owns the mutation: it declares the variable, writes the modified rows into it and appends an automatic SELECT read-back that its terminals then discard. When the captured rows are needed by a later statement of the same batch — to filter, join, aggregate or return them — register the capture on the batch instead and compose an ordinary query over it:

public sealed class CapturedRow
{
    public long Id { get; set; }
    public string? Name { get; set; }
}

var batch = ctx.CreateBatchBuilder();

batch.OutputIntoTableVariable(
    ctx.CreateInsertBuilder<ISimpleEntity>()
        .Value(x => x.Name, name)
        .Returning(x => new CapturedRow { Id = x.Id, Name = x.Name }),
    "@t",
    "id bigint, name nvarchar(100)");

var rows = batch
    .Query(batch.FromOutput<CapturedRow>("@t")
        .Where(r => r.Id > minId)
        .OrderBy(r => r.Name)
        .Select(r => new CapturedRow { Id = r.Id, Name = r.Name }))
    .ToList();

-- SQL Server (one batch, one round trip)
declare @t table (id bigint, name nvarchar(100)); insert into simple_entity (name) output inserted.id, inserted.name into @t (id, name) values (@p0); select id, name
 from @t as [t1]
 where (t1.id > @b1_minId)
 order by t1.name
  • One capture overload per mutation kind. BatchBuilder.OutputIntoTableVariable(...) accepts an InsertReturningBuilder<TEntity, TResult>, an UpdateReturningBuilder<TEntity, TResult>, a DeleteReturningBuilder<TEntity, TResult> or a MergeReturningBuilder<TEntity, TResult>; the captured row shape is the mutation's Returning(...) projection (Returning() captures the whole mapped entity). A DELETE reads the removed rows through the deleted alias.
  • Batch-only visibility. The table variable exists only inside the batch that declares it. FromOutput<TResult>(name) and FromOutputTable(name) are accepted only by that batch's Query/AddQuery, and only for a capture registered earlier on the same builder. Reading the source outside its owning batch is refused with NotSupportedException (batch output source) before a command is allocated; a name never captured on this batch, a duplicate capture name, or a mutation bound to another context is rejected with InvalidOperationException/ArgumentException while the batch is composed.
  • Two source forms. FromOutput<TResult>("@t") is a typed source: TResult must be exactly the capture's projection type (use a named class or record, not an anonymous type), and the ordinary operators — Where, OrderBy, Join, GroupBy, Limit — apply. FromOutputTable("@t") is a TableAlias-based source (t["id"].AsInt), which is the practical choice for an anonymous projection, a join or an aggregate.
  • No automatic read-back, no extra result set. The capture step emits only DECLARE @t TABLE (...); <mutation> OUTPUT ... INTO @t (...); the composed query is the read-back. The whole batch — capture plus every composed query — is a single server round trip (the naive shape, capture into a real table and then SELECT it, needs two).
  • Per-step parameter isolation. Every step's captured variables carry a per-step b{i}_ prefix (@b0_min, @b1_min, …), so the same closure member name in two steps never collapses into one placeholder; a mutation's own bound values continue a single batch-wide @p0… sequence.
  • Order is explicit. The table variable has no order of its own: a composed query returns rows in whatever order the engine produces unless you add OrderBy(...).
  • The definitions are trusted SQL. columnDefinitions is embedded verbatim in the DECLARE @t TABLE (...) text and is never parameterised or validated; the caller's declaration names are reused as the INTO column list, positionally against the mutation's projection order. The declaration must match the returning projection by position and count (a name and a compatible type per column) — a mismatch is a database error, not a client-side diagnostic. Never pass user input there.
  • Provider and form gates. Only SQL Server implements ISqlDialect.SupportsOutputInto; every other provider refuses the capture with NotSupportedException, and a mutation whose projection yields no output column (the parameterless ReturningIdentity<TKey>()) is refused the same way. PostgreSQL keeps its own data-modifying CTE route for reading modified rows, and this API refuses it. There is no fluent OutputInto for a MERGE — the batch capture is its only form (see Capturing the merged rows).

Bulk insert

Writing a whole set — native bulk paths, chunking, Returning, IgnoreDuplicates and KeepIdentity — is documented in its own guide: Bulk insert.

Upsert and MERGE

The same builder family also writes rows through a MERGE: a portable key upsert (OnKeys() + WhenMatchedUpdate() + WhenNotMatchedInsert(), rendered as INSERT ... ON CONFLICT / ON DUPLICATE KEY / MERGE) and a general full MERGE with WHEN MATCHED/WHEN NOT MATCHED branches, arbitrary conditions, THEN DELETE/THEN DO NOTHING and RETURNING/OUTPUT. Both start from ctx.CreateMergeBuilder<TEntity>() and are documented in their own guide:

Under an active global query filter, the portable key upsert on ON CONFLICT (PostgreSQL, SQLite) and ON DUPLICATE KEY (MySQL, MariaDB) refuses with NotSupportedException, because those forms cannot carry a target predicate; IgnoreFilters() restores the native upsert. The full MERGE form filters its target instead (see Write-target isolation).

Inspecting the SQL

ToSql() renders the parameterised SQL a plain Insert() would execute, without opening a connection. It is useful for diagnostics and for SQL-generation tests.

var sql = ctx.CreateInsertBuilder<ISimpleEntity>().Value(x => x.Name, "a").ToSql();
// insert into simple_entity (name) values (@p0)

Provider and capability summary

Provider INSERT ... VALUES Generated key Returns rows Notes
SQLite yes RETURNING (also last_insert_rowid()) yes
PostgreSQL yes RETURNING yes
SQL Server yes OUTPUT inserted.<col> yes
MySQL yes LAST_INSERT_ID() — no RETURNING
MariaDB yes LAST_INSERT_ID() — RETURNING (10.5+) is not used
ClickHouse yes (small batches) — — bulk uses the portable INSERT ... VALUES path; bound it with MaxBatchSize
In-memory — — — read-only context; NotSupportedException

INSERT ... VALUES itself is cross-provider and ungated: the same CreateInsertBuilder<T>() API works on every SQL provider. Only the generated-key form differs, and a provider that cannot express it rejects ReturningIdentity/ReturningKey with NotSupportedException instead of emitting invalid SQL. SQL Server additionally writes the modified rows into an existing table with OUTPUT INTO, and into a same-batch table variable for a later statement of the same batch; see Writing the modified rows into a table and Batch-scoped capture.

Notes and limitations

  • Values are parameters. A constant passed to Value(...) is never inlined; it becomes a named parameter (@p0, $p0, ...) bound on execution.
  • null overloads. Value(x => x.Name, null) is ambiguous between the value and the column-expression overload; cast the literal: Value(x => x.Name, (string?)null).
  • Key upsert and full MERGE — ON CONFLICT/ON DUPLICATE KEY/MERGE key upsert is implemented (see Upsert (key merge)), and a full MERGE with WHEN MATCHED/WHEN NOT MATCHED/WHEN NOT MATCHED BY SOURCE branches is available on SQL Server and PostgreSQL 15+ (see Full MERGE). INSERT ... SELECT is implemented (see A batch from a query above). (An all-defaults row is supported: see Writing values.)
  • Mutations are not prepared or plan-cached. Optimisation in nextorm targets read-only queries only (Prepare, the implicit plan cache, benchmarks); a mutation always renders and executes one command per call.
  • Affected-row count. Insert()/InsertAsync() return the number of rows the provider reports. ClickHouse does not report one for INSERT ... VALUES, so it returns 0 even though the row is written — do not use the count to confirm a ClickHouse insert.
  • Chunking a large batch. CreateInsertBuilder<T>() writes one statement; use Bulk insert for a whole set, including optional chunking (MaxBatchSize/MaxParameters/MaxSqlLength) and native bulk paths.
  • Global query filters are not injected into the target. A filter declared for the entity type is never added to the INSERT; instead the written values are validated against the target's active filters before execution and a violation raises QueryFilterException. IgnoreFilters on the builder disables them from validation too. On the key-upsert providers that cannot express a target predicate (ON CONFLICT / ON DUPLICATE KEY) an active filter makes the upsert refuse with NotSupportedException; IgnoreFilters() restores it, and the full MERGE form filters the target instead. See INSERT and MERGE (validation) and Write-target isolation.
  • The in-memory provider is query-only: INSERT/UPDATE/DELETE and the full MERGE throw NotSupportedException; only the key-upsert merge is applied to the registered sequence in the context.

See also

Edit this page