Union, Concat, Intersect, Except, Distinct, and Shape Compatibility Across Set Operations

Learning outcomes

ServiceHub needs a unified “attention feed” combining high-priority work orders with unassigned work orders, then needs overlap/difference reports between several candidate sets. LINQ set operators can translate directly to relational set operations—but only when both sides have compatible server-side shapes. This lesson separates bag semantics from true set semantics and makes the translation boundary observable.

1. Bag versus set semantics

// Existing course model — Chapter 09 extends queries, not persistence identity.public sealed partial class WorkOrder{    public int Id { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public WorkOrderPriority Priority { get; private set; }    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ICollection<WorkOrderTag> WorkOrderTags { get; } = new List<WorkOrderTag>();    public Guid Revision { get; private set; }}public sealed class Technician{    public int Id { get; private set; }    public string EmployeeCode { get; private set; } = string.Empty;    public string DisplayName { get; private set; } = string.Empty;    public IReadOnlyCollection<WorkOrder> WorkOrders => _workOrders;    private readonly List<WorkOrder> _workOrders = new();}public sealed class WorkOrderTag{    public int WorkOrderId { get; private set; }    public WorkOrder WorkOrder { get; private set; } = null!;    public int TagId { get; private set; }    public Tag Tag { get; private set; } = null!;    public DateTime AppliedUtc { get; private set; }    public string AppliedBy { get; private set; } = string.Empty;    public int DisplayOrder { get; private set; }}

SQL query results behave like bags (multisets) unless an operation removes duplicates. Concat preserves duplicates; relational providers generally translate it as UNION ALL. Union removes duplicate rows, generally translating as UNION. Duplicate comparison applies to the entire projected row shape.

2. Build compatible projections first

var highPriority = db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        Reason = "high-priority"    });var unassigned = db.WorkOrders    .Where(w => w.AssignedTechnicianId == null)    .Select(w => new    {        w.Id,        w.WorkOrderNumber,        Reason = "unassigned"    });

Both sides have the same CLR member names/types and provider-mappable store shape. That is the foundation for a translatable set operation.

3. Concat preserves duplicates

var feed = highPriority    .Concat(unassigned)    .OrderBy(x => x.Id);Console.WriteLine(feed.ToQueryString());

A work order that is both high priority and unassigned appears twice because the Reason values differ anyway—and even if the rows were identical, Concat preserves duplicates. That may be desirable when each row represents a separate alert reason.

4. Union eliminates duplicate projected rows

var highIds = db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => new { w.Id, w.WorkOrderNumber });var unassignedIds = db.WorkOrders    .Where(w => w.AssignedTechnicianId == null)    .Select(w => new { w.Id, w.WorkOrderNumber });var uniqueAttention = highIds.Union(unassignedIds);

SELECT w.work_order_id AS Id, w.work_order_number AS WorkOrderNumberFROM work_orders AS wWHERE w.priority = 3UNIONSELECT w0.work_order_id AS Id, w0.work_order_number AS WorkOrderNumberFROM work_orders AS w0WHERE w0.assigned_technician_id IS NULL;

Union may require duplicate-elimination work (sort/hash/provider strategy). If sources are already disjoint or duplicates are meaningful, Concat can be the correct semantics and cheaper.

5. Intersect and Except answer overlap/difference

var high = db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => w.Id);var assigned = db.WorkOrders    .Where(w => w.AssignedTechnicianId != null)    .Select(w => w.Id);var highAndAssigned = high.Intersect(assigned);var highButUnassigned = high.Except(assigned);

Inspect provider SQL and plans. These set operators can be clearer than deeply nested boolean predicates when you are combining independently meaningful query sets, but they are not automatically faster.

6. Ordering belongs to the final set unless semantics require otherwise

var result = highIds    .Union(unassignedIds)    .OrderBy(x => x.WorkOrderNumber)    .ThenBy(x => x.Id);

Ordering each input does not guarantee ordering of the final set and may be discarded/rewritten by the provider. Apply final ordering after the set operation when consumers require deterministic output.

7. Deliberately wrong: set operation after a client-only projection

static string DisplayCode(string value)    => $"WO::{value.ToUpperInvariant()}";var left = db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => new { Code = DisplayCode(w.WorkOrderNumber) });var right = db.WorkOrders    .Where(w => w.AssignedTechnicianId == null)    .Select(w => new { Code = w.WorkOrderNumber });var broken = left.Union(right);await broken.ToListAsync(ct); // expect translation failure for client projection before set op

The custom method can be allowed only as top-level client projection, but placing a server set operation after that boundary requires EF to combine something it cannot represent in SQL. A common diagnostic is that EF cannot translate a set operation after client projection.

var left = db.WorkOrders    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => w.WorkOrderNumber);var right = db.WorkOrders    .Where(w => w.AssignedTechnicianId == null)    .Select(w => w.WorkOrderNumber);var rows = await left.Union(right).ToListAsync(ct);var display = rows.Select(DisplayCode).ToList();

The database combines compatible strings; formatting is an explicit post-materialization client step.

8. Type/nullability/store compatibility is provider-visible

Even when CLR projections compile, provider store types/collations/conversions can make a set operation non-translatable or semantically surprising. Normalize the server projection before combining—for example, cast numeric types consistently and avoid mixing incompatible collations/provider-specific mapped types without explicit design.

9. Hands-on lab: same inputs, different set semantics

1. Seed at least one work order that is both high priority and unassigned.
2. Project IDs only; compare Concat vs Union row counts and SQL.
3. Run Intersect and Except; verify expected IDs manually.
4. Add final deterministic ordering and confirm it appears outside/after the combined SQL shape.
5. Run the client-method-before-Union failure and save the exception.
6. Repair by moving Union before the client projection.
7. Use EXPLAIN QUERY PLAN to compare UNION vs UNION ALL on the SQLite lab; do not invent timings.

10. Production judgment and bridge

Use set operators when the domain genuinely combines sets, not as a stylistic replacement for predicates. Preserve duplicate semantics deliberately, align projection shapes before the operator, and inspect SQL/plans for large sets. Lesson 5 introduces the intentional escape hatch when LINQ cannot or should not express the needed query: parameterized raw SQL, composability, unmapped results, stored-procedure boundaries, and non-query commands.

Authoritative references

• LINQ set operations - .NET — Union/Concat/Intersect/Except semantics
• Client vs Server Evaluation - EF Core — client projection boundary
• How Queries Work - EF Core — translation/execution model
• SQLite compound SELECT — UNION/UNION ALL/INTERSECT/EXCEPT behavior
• Efficient Querying - EF Core — measure SQL/plan costs