Compare DataTables with LINQ: Except, Intersect and Union

To compare two ADO.NET DataTable objects, call AsEnumerable() on each to get a sequence of DataRow, then apply LINQ set operators: Except gives rows in the first table but not the second, Intersect gives rows in both, and Union gives every distinct row across both. The one detail that trips everyone up is that DataRow uses reference equality by default, so a plain Except compares object identity, not column values — you must supply an IEqualityComparer<DataRow> (or DataRowComparer.Default) that compares the columns you care about. This article shows the correct pattern on .NET 9 and turns each result back into a DataTable with CopyToDataTable().

Load DataRows with AsEnumerable()

A DataTable is not itself IEnumerable<DataRow>. The AsEnumerable() extension (from the System.Data.DataSetExtensions assembly, referenced automatically by the .NET 9 SDK) bridges the gap and hands you a queryable sequence of rows:

using System.Data;

DataTable current = BuildEmployees(/* ... */);
DataTable previous = BuildEmployees(/* ... */);

IEnumerable<DataRow> currentRows = current.AsEnumerable();
IEnumerable<DataRow> previousRows = previous.AsEnumerable();

Once you have those two sequences, every LINQ operator is available. Read typed column values with row.Field<T>("Column"), which is DBNull-safe and lets you use nullable types like Field<string?> — a better choice than the raw row["Column"] indexer, which returns object and hands you DBNull.Value rather than null.

Two rules keep AsEnumerable() comparisons predictable. First, both tables should expose the columns you intend to compare, with compatible types — comparing an Id stored as int in one table against a long in the other will never match. Second, the operators are lazy: nothing runs until you enumerate the result (a foreach, ToList(), Any(), or CopyToDataTable()), so you can compose them freely before paying for the work.

Why a plain Except returns the wrong rows

The set operators (Except, Intersect, Union, Distinct) decide whether two elements are "the same" by calling Equals and GetHashCode. For DataRow those come from object — pure reference identity. Two rows holding identical values are still different objects, so this does not do what you expect:

// Wrong: compares object references, not column values.
var wrong = current.AsEnumerable()
                   .Except(previous.AsEnumerable());
// Every row in `current` is returned, because no two
// DataRow instances are ever reference-equal.

Because the rows in current and previous are distinct instances, Except treats them all as unique and returns the entire table. You need to tell LINQ how to compare rows.

Supply a custom IEqualityComparer<DataRow>

The most explicit fix is a comparer that decides equality by your key column (or a composite of columns). Compare on the same fields you would use as a primary key:

sealed class EmployeeKeyComparer : IEqualityComparer<DataRow>
{
    public bool Equals(DataRow? x, DataRow? y) =>
        x is not null && y is not null &&
        x.Field<int>("Id") == y.Field<int>("Id");

    public int GetHashCode(DataRow row) =>
        row.Field<int>("Id").GetHashCode();
}

Pass an instance to any set operator and the comparison now runs on values:

var comparer = new EmployeeKeyComparer();

// Rows in `current` whose Id is not in `previous` (new hires).
var added = current.AsEnumerable()
                   .Except(previous.AsEnumerable(), comparer);

// Rows whose Id appears in both tables (retained).
var retained = current.AsEnumerable()
                      .Intersect(previous.AsEnumerable(), comparer);

// Every distinct Id across both tables.
var everyone = current.AsEnumerable()
                      .Union(previous.AsEnumerable(), comparer);

The rule of thumb: Except = "in A but not B", Intersect = "in both", Union = "all, de-duplicated". Run those three queries against a previous roster of {Asha, Ben, Chris, Dana} and a current roster of {Ben, Chris, Dana, Evan, Fay} and you get exactly what a diff should produce:

Added (Except: current - previous):
  5 - Evan
  6 - Fay

Retained (Intersect):
  2 - Ben
  3 - Chris
  4 - Dana

All distinct (Union):
  1 - Asha
  2 - Ben
  3 - Chris
  4 - Dana
  5 - Evan
  6 - Fay

Reverse the operands of Except — previous.Except(current) — and you get the leavers instead (Asha). That symmetry makes Except the natural tool for change detection: run it both ways to find additions and removals, and Intersect to find what stayed.

Reuse DataRowComparer instead of writing your own

.NET ships a ready-made comparer so you often do not need a custom class. DataRowComparer.Default compares two rows by the ordered values of all their columns — same column count, same values, in order:

using System.Data;

var comparer = DataRowComparer.Default;

var changedOrNew = current.AsEnumerable()
                          .Except(previous.AsEnumerable(), comparer);

Use DataRowComparer.Default when a row counts as "equal" only if every column matches — ideal for detecting any change to a record. Write your own IEqualityComparer<DataRow> when equality should hinge on a subset of columns, such as a business key while ignoring a LastModified timestamp.

Comparing on specific columns without a comparer class

If you would rather not author a comparer, project each row to an anonymous type or a record. LINQ already knows how to compare those by value, because the compiler generates structural Equals/GetHashCode:

var addedIds =
    current.AsEnumerable().Select(r => new
    {
        Id = r.Field<int>("Id"),
        Name = r.Field<string>("Name")
    })
    .Except(previous.AsEnumerable().Select(r => new
    {
        Id = r.Field<int>("Id"),
        Name = r.Field<string>("Name")
    }));

This is concise and readable, but note you get back the projected shape (the anonymous type), not DataRow — so you cannot call CopyToDataTable() on it directly. Choose a comparer when you need the original rows back. A common middle ground is to project only the key for the comparison, then join the surviving keys back to the original table:

var addedKeys = current.AsEnumerable().Select(r => r.Field<int>("Id"))
    .Except(previous.AsEnumerable().Select(r => r.Field<int>("Id")))
    .ToHashSet();

var addedRows = current.AsEnumerable()
    .Where(r => addedKeys.Contains(r.Field<int>("Id")));

This keeps the value comparison simple while still leaving you with real DataRow objects ready for CopyToDataTable().

Turn results back into a DataTable

The set operators return IEnumerable<DataRow>. To materialize a result as a real table (to bind, export, or return from a method), call CopyToDataTable():

DataTable addedTable = added.CopyToDataTable();

One gotcha: CopyToDataTable() throws InvalidOperationException if the sequence is empty, because it clones its schema from the first row. Guard it:

DataTable addedTable = added.Any()
    ? added.CopyToDataTable()
    : current.Clone();   // empty table with the same columns

current.Clone() copies the structure (columns, types) but no data, giving you a correctly-shaped empty table. There is also a CopyToDataTable(destination, options) overload that merges rows into an existing table, which is handy when you are accumulating results from several comparisons into one report table.

A subtle point about CopyToDataTable(): the rows it produces are copies detached from the source table's DataRowState, so mutating them will not touch current or previous. That is usually what you want for a diff report, but if you need to write changes back to the original table, keep a reference to the source rows (for example, the added sequence) rather than the copied ones.

Performance and larger tables

Except, Intersect, and Union build a hash set internally from the second sequence, so they run in roughly linear time — far better than a nested-loop comparison of every row against every other row. The cost that matters is your GetHashCode: keep it cheap and make sure it agrees with Equals (equal rows must return equal hash codes), or the set operators silently miss matches. For very large tables, comparing on a single indexed key column, as EmployeeKeyComparer does, is both the fastest and the clearest option.

Key takeaways

• Call AsEnumerable() to expose a DataTable as IEnumerable<DataRow> for LINQ.
• Except = rows in A not in B, Intersect = rows in both, Union = all distinct rows.
• DataRow uses reference equality by default, so these operators need an explicit comparer.
• Write an IEqualityComparer<DataRow> to compare by key column(s); use DataRowComparer.Default to compare by all columns.
• Alternatively project to an anonymous type or record for value comparison, but you lose the DataRow.
• Use CopyToDataTable() to materialize results, and guard against the empty-sequence exception.

Frequently asked questions

Why does Except return all rows from the first DataTable?

Because DataRow inherits Equals from object, which is reference equality. No two row instances are ever reference-equal, so every row looks unique and Except returns them all. Pass an IEqualityComparer<DataRow> to compare by value instead.

What is DataRowComparer.Default?

It is a built-in IEqualityComparer<DataRow> in System.Data that treats two rows as equal when they have the same number of columns and equal values in the same order. It is the quickest way to compare rows by their full contents without writing a comparer.

How do I compare only certain columns?

Write a small IEqualityComparer<DataRow> whose Equals and GetHashCode read only those columns with Field<T>(), or project each row to an anonymous type or record containing just those columns and let LINQ compare structurally.

Does this work on .NET 9?

Yes. AsEnumerable(), the LINQ set operators, DataRowComparer, and CopyToDataTable() are all part of System.Data in .NET 9, and the SDK references the needed assemblies automatically — no extra NuGet packages required. The same code compiled and ran unchanged on earlier versions too, but .NET 9 is the current target and needs no System.Data.DataSetExtensions package reference.