Compare two DataTables with several keys and select the rows that are not present in second table

Question

I have two DataTables and I want to select the rows from the first one which are not present in second one, both tables have 3 Keys custnum, shiptonum, connum

For example:

Table Contacts

custnum  shiptonum  connum  column
   1        1         1     data1    
   2        2         2     data2
   3        3         3     data3
   4        4         4     data4

Table Invitations

custnum  shiptonum  connum  column
   1        1         1     data11
   3        3         3     data33

I'd like the result to be:

Table Result

custnum  shiptonum  connum  column
   2        2         2     data2
   4        4         4     data4

I already tried using

var differences = table1.AsEnumerable().Except(table2.AsEnumerable(),DataRowComparer.Default);

but it didn't work. For example in my testing in Contacts table I have 14,389 records, in Invitations table I have two records that exist in Contacts table the count after using the abovesolution was 14,389 instead of 14,387 (removing the two records from Invitations table).

Possibly you need something like this: `var differences = table1.AsEnumerable().Except(table2.AsEnumerable(),DataRowComparer.Default);`. — Tetsuya Yamamoto, Aug 20 '18 at 04:44
Possible duplicate of [Difference of two DataTables in c#](https://stackoverflow.com/questions/9289475/difference-of-two-datatables-in-c-sharp) — Tetsuya Yamamoto, Aug 20 '18 at 04:45
It didn't work @TetsuyaYamamoto, for example in my testing in Contacts table I have 14,389 records, in table Invitations I have two records that exist in Contacts table the count after using the suggested solution was 14,389 instead of 14,387 (removing the two records from Invitations table). — Pedro Rodriguez, Aug 20 '18 at 04:58

score 0 · Answer 1 · answered Aug 20 '18 at 06:42

You wrote:

I want to select the rows from the first one which are not present in second one

From your example, I see that you don't want to select rows from the first table that are not rows in the second table, but that you only want to take the values of the keys into account:

I want to select all rows from tableA which have keys with values that are not keys from tableB

You didn't define your tables. They might be IQueryable, or IEnumerable, for your LINQ statements there is not a big difference. Try to avoid AsEnumerable, especially if your data source is in a different process, like a database management system. The other process is much more efficient in executing your query than your process. AsEnumerable transports all data from your other process to your process, which is a relatively slow process. Therefore as a rule: Only use AsEnumerable this if you really need to,

The second definition defines clearer what you want: apparently from tableB you only need the keys:

var keysTableB = tableB.Select(row => new
    {
         CustNum = row.custNum,
         ShipToNum = row.shiptonum,
         ConNum = row.connum,
    });

In words: from every row in tableB make one new object of anonymous type with three properties: CustNum, ShipToNum and ConNum

Select uses lazy execution. No query is executed, only property Expression is changed.

Now you want to keep only the rows from tableA that have a key that is a member of sequence keysTableB: if you want to keep a subset of a sequence, use Where

var result = tableA.Where(row => keysTableB.Contains(new
    {
         CustNum = row.custNum,
         ShipToNum = row.shiptonum,
         Connum = row.connum,
    }));

In words: from every row in tableB keep only those rows that have a key that is also in keysTableB, using value equality.

TODO: consider concatenating these two LINQ statements into one.
I doubt whether this would improve performance. It surely will deteriorate readability of your code, and thus decreases changeability / maintenance / testability.

score 0 · Answer 2 · answered Aug 20 '18 at 07:43

for (int i=0;i<table1.rows.count;i++)
{
   var rowExists = from dr in table2.AsEnumerable()
                   where dr.Field<typeofcolumn>("colum_name")==table1.Rows[i]["column_name"]
                   select dr;
  if(rowExists.ToList().Count==0)
  {
      //here u import row table1.rows[i] to new table
  }
}

Compare two DataTables with several keys and select the rows that are not present in second table

2 Answers2