CODEWITHSUNDEEP
c#linqdatatable
user2095405
user2095405

Reputation: 479

How to get difference between two DataTables

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

Upvotes: 22

Views: 40203

Answers (6)

Madhu
Madhu

Reputation: 497

Try this

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

Upvotes: 3

Kevin.Hardy
Kevin.Hardy

Reputation: 81

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

Upvotes: 8

TGarrett
TGarrett

Reputation: 552

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

Upvotes: 0

Ryszard Dżegan
Ryszard Dżegan

Reputation: 25434

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.

Upvotes: 0

Amol Kolekar
Amol Kolekar

Reputation: 2325

You can try the following code...

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

Upvotes: 8

skjcyber
skjcyber

Reputation: 5957

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

Upvotes: 2

Related Questions