.NET Compare 2 Datarows by Value Using LINQ

There are too many top Google search results giving the impression that it is impossible to compare 2 DataRows for its value contents and the programmer needs to iterate through everything himself.

Such is no longer true in .NET 3.5 but the blogosphere doesn’t seem to have caught on yet.

It is possible, given 2 DataTables or any other enumerable object types, to compare its contents using LINQ in just one line of code.

I will give an example in VB where I’d want to compare all column properties (description, data type, field length, etc) of all tables in 2 supposedly identical SQL databases.

  1. Read into DataTable the SQL command
  2. SELECT * FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    ORDER BY TABLE_NAME, COLUMN_NAME

    this returns a table of with all the column properties. Do this for your second table you want to compare.

  3. To get the difference, you can use the Except LINQ operator on the 2 tables and use a DataRowComparer class to check for value equality instead of reference equality
  4. Dim differenceTable = masterTable.AsEnumerable.Except(subjectTable.AsEnumerable, DataRowComparer.Default)

    this will return you a list of DataRows present in masterTable but not in subjectTable. You can also use other LINQ operators such as Distinct, Union and Intersect.

This solution is ideal when there is no key column. Otherwise, merge the second table into the first and call .GetDifference() for a easier way to find the different rows.