Compare DataTables with LINQ: Except, Intersect and Union

"What changed since yesterday?" is the eternal data question — which rows appeared, which vanished, which were modified. LINQ's set operators answer it in a few lines: Except (in A but not B), Intersect (in both), Union (in either). This post runs a real snapshot comparison over two DataTables on .NET 10, including the by-key variants (ExceptBy, IntersectBy, UnionBy) that make the classic version obsolete, with verified output.

The scenario

Yesterday's product snapshot vs today's:

var yesterday = MakeTable(("A-100", 89.00m), ("B-200", 329.99m), ("C-300", 149.50m));
var today     = MakeTable(("B-200", 329.99m), ("C-300", 129.50m), ("D-400", 59.00m));

A-100 was removed, D-400 added, B-200 unchanged, and C-300's price dropped. The code should tell us exactly that.

Why DataRow equality betrays you

The tempting first attempt — yesterday.AsEnumerable().Except(today.AsEnumerable()) — returns everything, because DataRow uses reference equality: two rows with identical values in different tables are "different". Two fixes:

1. DataRowComparer.Default — a value comparer for whole rows: Except(today.AsEnumerable(), DataRowComparer.Default). Works, but compares all columns, so a one-cent price change makes a row "removed and added" rather than "changed".
2. Project to a record — the approach that scales, because records give you value equality for free and you choose the shape:

record ProductRow(string Sku, decimal Price);

static ProductRow Shape(DataRow r) => new(r.Field<string>("Sku")!, r.Field<decimal>("Price"));

var yRows = yesterday.AsEnumerable().Select(Shape).ToList();
var tRows = today.AsEnumerable().Select(Shape).ToList();

The comparison, in five expressions

var removed   = yRows.ExceptBy(tRows.Select(r => r.Sku), r => r.Sku).ToList();
var added     = tRows.ExceptBy(yRows.Select(r => r.Sku), r => r.Sku).ToList();
var unchanged = tRows.Intersect(yRows).ToList();               // record equality: Sku AND Price
var changed   = tRows.IntersectBy(yRows.Select(r => r.Sku), r => r.Sku)
                     .Except(unchanged).ToList();
var allSkus   = yRows.UnionBy(tRows, r => r.Sku).ToList();

Verified output:

removed:   A-100
added:     D-400
unchanged: B-200
changed:   C-300 (129.50)
union:     4 distinct SKUs
identical rows via DataRowComparer: 1

The interplay is the insight:

• ExceptBy keyed on SKU answers presence questions — a price change must not make a product look "removed".
• Intersect on the full record answers identity questions — same SKU and same price means truly unchanged.
• Changed = present in both by key, minus unchanged. Set algebra composes; you rarely need loops.

The *By variants are the modern default

Before .NET 6 you wrote an IEqualityComparer<T> class every time you wanted "compare by this key" — a dozen lines of Equals/GetHashCode ceremony per comparison. ExceptBy, IntersectBy, UnionBy, and DistinctBy take a key selector inline and delete all of it. If you maintain code with custom comparer classes for simple keys, each one is now three words.

Semantics worth remembering: all set operators deduplicate their output (they're set operators — Union of lists with internal duplicates returns distinct results), and ExceptBy's second sequence is the keys to exclude, not the items — hence tRows.Select(r => r.Sku).

Performance and practicalities

• Set operators build a hash set of one side and stream the other — O(n + m), fine for tens of thousands of rows. The nested-loop Where(... .Any(...)) equivalent is O(n × m); at 10k × 10k that's 100 million comparisons versus 20 thousand hash operations.
• Case-insensitive keys: the non-By operators accept StringComparer.OrdinalIgnoreCase; for *By operators normalize the key in the selector (r => r.Sku.ToUpperInvariant()).
• Decimal precision matters for "changed" detection — 129.50m and 129.5000m are equal as decimals, but if one side arrives as double, normalize before comparing or everything looks changed.
• If both tables came from the same SQL Server, do the diff in SQL (EXCEPT/INTERSECT exist there too) and skip hauling both snapshots into memory. These LINQ operators shine when sources differ — a file vs a table, an API vs a cache.

Beyond DataTables

Nothing here is DataTable-specific — the projection step is. The same five expressions diff two List<Order>, an API response against a database state, or config snapshots. Learn the pattern once — project to a value-equatable shape, then apply set algebra — and every "what changed" requirement becomes a five-liner.

The runnable program is in the companion repository, alongside the DataTable conversion sample that produces the typed rows this one compares.