Insert

<query or list>.Insert(s => new T { Property = s.…, … }) → int // one T per matching row or element, at the call; answers how many

Ends a query chain by creating one row of ANOTHER entity per row the chain selects — an INSERT … SELECT in one statement. The chain is the source; the `new T { … }` inside the terminal names the target and its properties, and each value may read the source row. Returns how many rows were created.

Summary#

.Insert(…) closes the other load-and-loop pattern — create a row per matching parent. The chain selects the SOURCE rows; the projection inside the terminal builds one TARGET row from each, in the database, in one statement. The answer is how many rows were created.

Signature#

<Source>.Where(s => …).Insert(s => new <Target> { Property = s.Property, Other = literal, Ref = s })   →   int
<list>.Insert(x => new <Target> { Property = x.Property, … })                                            →   int

Description#

One row per matching row#

The canonical use grants something to everyone who lacks it — the not-exists idiom, which also makes the statement re-runnable (already-covered rows simply do not match):

entity Member {
  [Required] string Email;
  bool Active;
}
entity Grant {
  [Required] Member Grantee;
  [Required] string Level;
}

int GrantAll() {
  return Member.Where(m => m.Active && !Grant.Any(g => g.Grantee == m))
               .Insert(m => new Grant { Grantee = m, Level = "Member" });
}

Grantee = m assigns the source ROW to a reference — each created row points at its own source. Any source property can feed a target property (Label = m.Email), values may be captured locals or literals, may read through the source row's references, and may embed a correlated scalar read — the same value rules as .Update(…), including the answer-for-absence refusal on a hop through a nullable reference. Only a row-returning query is refused: a projection assigns one scalar per column.

What must the projection set?#

Every required target property, and every property whose default is a per-row expression — and the compiler says so, naming the field, before anything runs. That is required-by-default arriving EARLIER than it does for a per-row new, because the projection is statically known. A property with a literal default (string Status = "New";) may be omitted and gets its default, exactly as a per-row create would give it.

Which rows does it read, and may it create?#

The source chain is the caller's ordinary secured read. The target's allow create when is a caller-level gate, checked once — a caller who may not create these rows gets a refusal, not a smaller set. An allow create where (the with-check on the written row) is verified over the created rows inside the same transaction: one violating row rolls the whole statement back.

What about collisions?#

Two answers, and both are good ones. Without more, a [Unique] collision throws for the whole statement — all-or-nothing, carrying the message your [Unique] declares — and the not-exists predicate above avoids minting the duplicate at all. Or say what a collision MEANS with onConflict: — the upsert:

entity Staged { [Required] string Sku; int Qty; bool Ready; }
entity Product {
  [Unique] string Sku;
  decimal Price;
  int Stock;
}

int Sync() =>
  Staged.Where(s => s.Ready)
        .Insert(s => new Product { Sku = s.Sku, Price = 10m, Stock = s.Qty },
                onConflict: (p, inc) => {
                  p.Price = inc.Price;              // take the incoming value
                  p.Stock = p.Stock + inc.Stock;    // or combine with what is already there
                });

p is the EXISTING row; inc is the row that would have been inserted, carrying the projected values — the only shape a conflict can see. The collision key is inferred from the target's own [Unique] declaration (stated once, at the model), the merge may not move the row off its key, and the merge half is judged as the UPDATE it is — your allow update rules, per assigned property. Merged rows are audited as updates, inserted ones as creates.

The key may span several members: an entity-level [Unique(A, B)] is a key exactly as a member's [Unique] is. And a merge that assigns NOTHING means leave the existing row as it is — ON CONFLICT … DO NOTHING — which is the idempotent "give it if it is not there yet". The answer then counts only the rows actually created:

enum Medal { Bronze, Silver }
entity Player { [Required] string Handle; int Points; }

[Unique(Holder, Kind)]
entity Award { [Required] Player Holder; Medal Kind; }

int AwardBronze() =>
  Player.Where(p => p.Points >= 100)
        .Insert(p => new Award { Holder = p, Kind = Medal.Bronze }, onConflict: (a, inc) => { });

Exactly one unique rule must hold for every row: a [Unique(…, Where = …)] holds only inside its predicate, so it is not a key, and an entity with two unique rules is refused — the compiler cannot know which one you meant.

From a local list#

The source need not be stored. A list or array built in the body — of class instances, or of plain scalars — is a source too, with the SAME verb, the same required-field checks at compile, the same stamps, defaults, security and audit per row, and the same onConflict:. The projection runs per element in your function; the rows go to the database as one statement (a very long list goes in several, inside one transaction — a failure anywhere leaves nothing). This is the seeding shape, and the import shape:

class Draft { public string Name; public int Weight; }
entity Tag { [Required] string Name; int Weight; string Status = "New"; }

int Seed() {
  var drafts = new List<Draft> {
    new Draft { Name = "alpha", Weight = 1 },
    new Draft { Name = "beta", Weight = 2 },
  };
  return drafts.Insert(d => new Tag { Name = d.Name, Weight = d.Weight * 10 });
}

A scalar list is the same thing with the element itself as the value:

entity Label { [Unique] string Name; int Seen; }

int Mark(List<string> names) =>
  names.Insert(n => new Label { Name = n, Seen = 1 },
               onConflict: (l, inc) => { l.Seen = l.Seen + 1; });

When does it run?#

At the call, landing with your unit of work's commit — like its two siblings ([[query-delete#immediacy|.Delete()]]), and with the same refusal while your unit of work holds uncommitted changes of the SOURCE type. So a grant written by an upsert and the row recording it land together, or neither does:

entity Crew { [Required] string Name; bool OnDuty; }

[Unique(Holder, Pass)]
entity KeyCard { [Required] Crew Holder; [Required] string Pass; }
entity CardLog { [Required] string Note; int Count; }

int IssueNightPasses() {
  var issued = Crew.Where(c => c.OnDuty)
                   .Insert(c => new KeyCard { Holder = c, Pass = "Night" }, onConflict: (k, inc) => { });
  new CardLog { Note = "night passes issued", Count = issued };   // refused at the commit? the passes go too
  return issued;
}

Examples#

entity Order {
  [Required] string Status;
  decimal Total;
}
entity Settlement {
  [Required] string Kind;
  decimal Amount;
}

int Settle() {
  return Order.Where(o => o.Status == "Closed")
              .Insert(o => new Settlement { Kind = "order", Amount = o.Total });
}