How to get difference between two DataTables

Question

I have these two datatables and I want to get the difference between them. Here is an example:

Table1
-------------------------
ID  |   Name 
--------------------------
 1  |  A
 2  |  B
 3  |  C
--------------------------

Table2
-------------------------
ID  |   Name 
--------------------------
 1  |  A
 2  |  B
--------------------------

I just want the result as data which is in table1 and not in table2 (table1-table2)

ResultTable
-------------------------
ID  |   Name 
--------------------------
 3  |  C
--------------------------

I tried to use these two similar solutions via Linq, but it always return table1 and not table1-table2. Here is first solution:

DataTable table1= ds.Tables["table1"];
DataTable table2= ds.Tables["table2"];
var diff= table1.AsEnumerable().Except(table2.AsEnumerable(),DataRowComparer.Default);

Second solution:

var dtOne = table1.AsEnumerable();
var dtTwo = table2.AsEnumerable();
var difference = dtOne.Except(dtTwo);

So, where is the mistake? Thank you a lot for all your answers. :)

Answer 1

You can try the following code...

table1.AsEnumerable().Where(
    r =>!table2.AsEnumerable().Select(x=>x["ID"]).ToList().Contains(r["ID"])).ToList();

Answer 2

I just went through this and wanted to share my findings. For my application it is a data sync mechanism, but i think you will see how this applies to the original question.

In my case, I had a DataTable that represented my last data upload and sometime in the future, I need to get the current state of the data and only upload the differences.

//  get the Current state of the data
DataTable dtCurrent = GetCurrentData();

//  get the Last uploaded data
DataTable dtLast = GetLastUploadData();
dtLast.AcceptChanges();

//  the table meant to hold only the differences
DataTable dtChanges = null;

//  merge the Current DataTable into the Last DataTable, 
//  with preserve changes set to TRUE
dtLast.Merge(dtCurrent, true);

//  invoke GetChanges() with DataRowState.Unchanged
//    !! this is the key !!
//    the rows with RowState == DataRowState.Unchanged 
//    are the differences between the 2 tables
dtChanges = dtLast.GetChanges(DataRowState.Unchanged);

I hope this helps. I fought with this for a few hours, and found lots of false-leads on the interwebz, and ended up comparing RowStates after merging a few different ways

Answer 3

I will try to do it on a column level rather than a DataTable.

IEnumerable<int> id_table1 = table1.AsEnumerable().Select(val=> (int)val["ID"]);
IEnumerable<int> id_table2  = table2.AsEnumerable().Select(val=> (int)val["ID"]);
IEnumerable<int> id_notinTable1= id_table2.Except(id_table1);

Just adding a .Select() to your answer...

Answer 4

Try this

DataTable dtmismatch = Table1.AsEnumerable().Except(Table2.AsEnumerable(), DataRowComparer.Default).CopyToDataTable<DataRow>();

Answer 5

You are using LINQ's Except method seems correct, but the issue might be related to the default equality comparer used for DataRow objects. The default equality comparer for DataRow objects might not work as expected for your case because it compares all columns of the DataRow, not just the "ID" and "Name" columns that you want to use for comparison. You can use the LINQ Except method with a custom equality comparer. Here's how you can do it:

DataTable table1 = ds.Tables["table1"];
DataTable table2 = ds.Tables["table2"];

var comparer = new CustomDataRowComparer(); // Custom equality comparer
var diff = table1.AsEnumerable().Except(table2.AsEnumerable(), comparer);
DataTable resultTable = diff.CopyToDataTable();

class CustomDataRowComparer : IEqualityComparer<DataRow>
{
    public bool Equals(DataRow x, DataRow y)
    {
        // Compare based on specific columns (ID and Name in this case)
        return x["ID"].Equals(y["ID"]) && x["Name"].Equals(y["Name"]);
    }

    public int GetHashCode(DataRow obj)
    {
        // Generate a hash code based on specific columns (ID and Name in this case)
        int hash = obj["ID"].GetHashCode() ^ obj["Name"].GetHashCode();
        return hash;
    }
}

Answer 6

Try the below approach:

Initialization:

var columnId = new DataColumn("ID", typeof (int));
var columnName = new DataColumn("Name", typeof (string));
var table1 = new DataTable();
table1.Columns.AddRange(new[] {columnId, columnName});
table1.PrimaryKey = new[] {columnId};
table1.Rows.Add(1, "A");
table1.Rows.Add(2, "B");
table1.Rows.Add(3, "C");

var table2 = table1.Clone();
table2.Rows.Add(1, "A");
table2.Rows.Add(2, "B");
table2.Rows.Add(4, "D");

Solution:

var table3 = table1.Copy();
table3.AcceptChanges();
table3.Merge(table2);

var distinctRows = from row in table3.AsEnumerable()
                   where row.RowState != DataRowState.Modified
                   select row;

var distintTable = distinctRows.CopyToDataTable();

Above solution also works when there are new rows in table2 that were not present in table1.

distintTable constains C and D.

Answer 7

Try below, this is pretty basic. Merge two sets together, and get the difference. If the sets dont align up properly, then this will not work.

DataSet firstDsData = new DataSet();
DataSet secondDsData = new DataSet();
DataSet finalDsData = new DataSet();
DataSet DifferenceDataSet = new DataSet();
finalDsData.Merge(firstDsData);
finalDsData.AcceptChanges();
finalDsData.Merge(secondDsData);
DifferenceDataSet = finalDsData.GetChanges();

Answer 8

Try below, this is pretty basic. Merge two sets together, and get the difference. If the sets dont align up properly, then this will not work. Trying to Test the same

DataSet firstDsData = new DataSet();
DataSet secondDsData = new DataSet();
DataSet finalDsData = new DataSet();
DataSet DifferenceDataSet = new DataSet();
finalDsData.Merge(firstDsData);
finalDsData.AcceptChanges();
finalDsData.Merge(secondDsData);
DifferenceDataSet = finalDsData.GetChanges();