Get difference between two data-tables according to one column

Question

I have the following scenario:

Table A that has 50 records and Table B that has 2 records.

I need to define a new table, say TableDiff which should contain 48 records from Table A that doesn't exist in Table B

My problem is that Table A and Table B are not identical but I have the field rowId which exists in both tables that I need to compare using it.

Use LINQ and JOIN the tables on id, you can use LINQ with DataTables by casting then as Enumerable — Derek, Jul 16 '13 at 07:40
You can filter out the values/id present in table B while creating the new table by using NOT IN operator.. — Vidhya Sagar Reddy, Jul 16 '13 at 07:41

Tim Schmelter · Accepted Answer · 2013-07-16T08:07:13.537

One way using Enumerable.Except and Enumerable.Join:

var aIDs = TableA.AsEnumerable().Select(r => r.Field<int>("RowID"));
var bIDs = TableB.AsEnumerable().Select(r => r.Field<int>("RowID"));
var diff = aIDs.Except(bIDs);
DataTable tblDiff = (from r in TableA.AsEnumerable()
                    join dId in diff on r.Field<int>("RowID") equals dId
                    select r).CopyToDataTable();

Here's the linq-to-objects "left-join"-approach:

DataTable tblDiff = (from rA in TableA.AsEnumerable()
                     join rB in TableB.AsEnumerable()
                     on rA.Field<int>("RowID") equals rB.Field<int>("RowID") into joinedRows
                     from ab in joinedRows.DefaultIfEmpty()
                     where ab == null
                     select rA).CopyToDataTable();

Ehsan · Answer 2 · 2013-07-16T07:54:16.143

    using System.Data.DataSetExtensions

    var tableAIds = tableA.AsEnumerable().Select(row => (int)row["rowId"]);
    var tableBIds = tableB.AsEnumerable().Select(row => (int)row["rowId"]);
    var resultantIds = tableAIds.Except(tableBIds);

now for creating datatable again

    DataTable diff =  from myRow in tableA.AsEnumerable()
                      join rIDS resultantIds in myRow.Field<int>("rowId") equals rIDS
                      select myRow).CopyToDataTable()

Get difference between two data-tables according to one column

2 Answers2

Linked