Union / Concat / Intersect / Except

Summary#

The LINQ set operators combine two row-queries over the same entity with SQL set semantics. Union is set union (duplicates removed), Concat appends (duplicates kept — SQL UNION ALL), Intersect keeps rows present in both sides, Except keeps left rows not in the right. The operands run as one SQL statement — (left SELECT) UNION [ALL] / INTERSECT / EXCEPT (right SELECT).

Signature#

<rows>.Union(<rows>)      // set union — duplicates removed
<rows>.Concat(<rows>)     // append — duplicates kept (UNION ALL)
<rows>.Intersect(<rows>)  // rows in BOTH sides
<rows>.Except(<rows>)     // left rows NOT in the right

where <rows> is an entity set or an entity-row chain (Where / OrderBy / Skip / Take / Distinct) — both sides the same entity.

Description#

Semantics are exactly C# / EF:

• Entity rows dedup by their full column list — effectively by Id (rows are PK-unique), which is the C# result for object sequences.
• Each operand may carry its own Where / OrderBy / Skip / Take / Distinct chain. A paged operand is parenthesized in the SQL, so its ORDER BY / LIMIT stays scoped to that side.
• A bare entity set is a valid operand (the whole set): Tag.Where(p).Union(Tag).
• Read-your-own-writes: uncommitted new T{} rows fold into the result per the op's semantics — a ghost matching either side joins a Union; one matching both sides appears twice in Concat, joins an Intersect, and is excluded by Except.
• Durable: the read memoizes like any query — a resume does not re-run it.

Boundaries (pointed diagnostics)#

• Operands must be the same entity — Order.Union(Customer) is a compile error (C# requires a common element type).
• Rows only — no Select projection on either side, and no GroupBy/SelectMany/Join before the operator.
• One pair per expression — A.Union(B).Union(C) is refused (chained set operators are not supported yet).
• No clauses over the combined set — A.Union(B).Count() / .OrderBy(…) refuse with materialize with .ToList() first.
• No Include on either side — apply includes after materializing.

Examples#

entity Tag {
  [MaxLength(50)] string Label;
  [MaxLength(10)] string Grp;
}

// Union — duplicates removed (a row matching both sides appears once).
Tag[] ActiveOrGroupB() {
  return Tag.Where(t => t.Label != "archived").Union(Tag.Where(t => t.Grp == "b")).ToList();
}

// Concat — duplicates kept (UNION ALL).
Tag[] Appended() { return Tag.Where(t => t.Grp == "a").Concat(Tag.Where(t => t.Grp == "b")).ToList(); }

// Intersect — rows present in BOTH sides.
Tag[] Both() { return Tag.Where(t => t.Label != "x").Intersect(Tag.Where(t => t.Grp == "b")).ToList(); }

// Except — left rows NOT in the right side.
Tag[] LeftOnly() { return Tag.Where(t => t.Label != "x").Except(Tag.Where(t => t.Grp == "b")).ToList(); }

// A bare entity set is a valid operand (the whole set).
Tag[] All() { return Tag.Where(t => t.Grp == "a").Union(Tag).ToList(); }

See also#

• Distinct — per-side and standalone row dedup
• Where / Single / Count — the operand chains
• ToList — materializing the combined set
• LINQ over a local list — the same set operators over a local List/HashSet (identical spellings)