Subtract two DataTables that have the same schema, e.g Minus SQL query

Question

I have two DataTables that have both the same schema (same columns names and types) .I want to get the rows that appear in the first and not in the second one. Could anyone help me in this? Thanks.

You could use Linq's Except. You would probably have to provide your own EqualityComparer. — Crowcoder, Jun 16 '16 at 16:55
https://stackoverflow.com/questions/7560742/difference-between-2-datatable — William Xifaras, Jun 16 '16 at 16:55
@WilliamXifaras the answer in the question you refer to is not working: it returns the rows that are in both DataTables. — Imededdine Fargi, Jun 16 '16 at 17:11

D Stanley · Accepted Answer · 2016-06-16T17:51:52.047

1

Thankfully, MS has created extension methods for DataTable that let you use Linq methods (like Except) to query data rows, and written a class that implements IEqualityComparer<DataRow> that compares DataRow instances by their column values:

var rows = dt1.AsEnumerable()
              .Except(dt2.AsEnumerable(),DataRowComparer.Default);

edited Jun 16 '16 at 17:51

answered Jun 16 '16 at 17:42

D Stanley

149,601
11
178
240

score 0 · Answer 2 · answered Jun 16 '16 at 16:51

0

I would write something like this:

var onlyIn1 = dataTable1.Where( row1 => !dataTable2.Rows.Any( row2 => row1["CustomerKey"] == row2["CustomerKey"]) ).ToList();

answered Jun 16 '16 at 16:51

Jonathan Allen

68,373
70
259
447

score 0 · Answer 3 · answered Jun 16 '16 at 17:25

0

You could try this:

var onlyIn1 = db.Table1.Except(db.Table2).ToList();

I never test this, but it should work like T-SQL's EXCEPT

answered Jun 16 '16 at 17:25

Eric

3,165
1
19
25

bluff and bla bla bla... Please be serious in your answers ! – Imededdine Fargi Jun 16 '16 at 17:36

Subtract two DataTables that have the same schema, e.g Minus SQL query

3 Answers3