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. :)

Except checks wether or not they are the same instance. Not wether their respective properties are identical. You can use the overload that accepts an EqualityComparer or you can investigate the extension method ExceptBy() that is implemented in various LINQ+ libraries, also in Jon Skeets MoreLinq (http://code.google.com/p/morelinq/) — Tormod, Feb 21 '13 at 12:52
@Tormod, but then what is the use of the `DataRowComparer` in his first solution? It overrides `public bool Equals(TRow leftRow, TRow rightRow)` to compare actual column values. — hometoast, Feb 21 '13 at 13:05
Can you show how you are retrieving your datatables? Is it possible the data is different than you expect it to be? I did a quick sample in LINQPad and your first solution seems to work fine. — goric, Feb 21 '13 at 13:13
@Tormod, the msdn library says otherwise: The DataRowComparer class is used to compare the values of the DataRow objects and does not compare the object references. (http://msdn.microsoft.com/en-us/library/system.data.datarowcomparer.aspx) — bouvierr, Feb 21 '13 at 13:22

Amol Kolekar · Answer 1 · 2013-02-21T13:05:01.563

7

You can try the following code...

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

edited Feb 21 '13 at 13:05

answered Feb 21 '13 at 12:58

Amol Kolekar

2,257
4
27
43

Kevin.Hardy · Answer 2 · 2014-09-03T04:13:05.830

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

When I try this all I get out of GetChanges is the union of the sets, not the difference. — Paul Kienitz, May 27 '16 at 16:47

score 1 · Answer 3 · answered Feb 21 '13 at 13:01

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...

score 1 · Answer 4 · answered Aug 27 '15 at 10:33

1

Try this

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

answered Aug 27 '15 at 10:33

Madhu

459
4
10

1

"Try this" isn't really an answer. – Gert Arnold Dec 21 '18 at 09:22

score 1 · Answer 5 · answered Sep 13 '16 at 11:08

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();

Ryszard Dżegan · Answer 6 · 2013-02-21T13:31:01.143

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.

score 0 · Answer 7 · answered Aug 13 '14 at 12:25

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();

score -1 · Answer 8 · answered Dec 21 '18 at 09:06

Try This ...

    public DataTable getDiffRecords(DataTable dtDataOne, DataTable dtDataTwo)
    {
        DataTable returnTable = new DataTable("returnTable");

        using (DataSet ds = new DataSet())
        {
            ds.Tables.AddRange(new DataTable[] { dtDataOne.Copy(), dtDataTwo.Copy() });

            DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
            for (int i = 0; i < firstColumns.Length; i++)
            {
                firstColumns[i] = ds.Tables[0].Columns[i];
            }

            DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
            for (int i = 0; i < secondColumns.Length; i++)
            {
                secondColumns[i] = ds.Tables[1].Columns[i];
            }

            DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
            ds.Relations.Add(r1);

            DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
            ds.Relations.Add(r2);

            for (int i = 0; i < dtDataOne.Columns.Count; i++)
            {
                returnTable.Columns.Add(dtDataOne.Columns[i].ColumnName, dtDataOne.Columns[i].DataType);
            }

            returnTable.BeginLoadData();
            foreach (DataRow parentrow in ds.Tables[0].Rows)
            {
                DataRow[] childrows = parentrow.GetChildRows(r1);
                if (childrows == null || childrows.Length == 0)
                    returnTable.LoadDataRow(parentrow.ItemArray, true);
            }

            foreach (DataRow parentrow in ds.Tables[1].Rows)
            {
                DataRow[] childrows = parentrow.GetChildRows(r2);
                if (childrows == null || childrows.Length == 0)
                    returnTable.LoadDataRow(parentrow.ItemArray, true);
            }
            returnTable.EndLoadData();
        }
        return returnTable;
    }

"Try this" isn't really an answer. – Gert Arnold Dec 21 '18 at 09:19 — Gert Arnold, Dec 21 '18 at 09:19

How to get difference between two DataTables

8 Answers8

Linked