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