Linq to compare two datatables using two keys

Hello, I have a linq that I need to use to compare dt1 and dt2 and produce a result of dt3. dt3 should be a data table that shows all the rows in dt1 which do not match the rows in dt2. Below is my linq, but i am getting a compiler error which says that the Operator ‘AND’ is not defined for IEnumerables. Your help would be greatly appreciated

From a In dt1.AsEnumerable() Join b In dt2.AsEnumerable
On a(“PAN”).ToString.Trim Equals b(“PAN”).ToString.Trim And a(“Amt”).ToString.Trim Equals b(“Amt”).ToString.Trim
Where a(“PAN”).ToString.Trim.Except(b(“PAN”).ToString.Trim)And (a(“Amt”).ToString.Trim.Except(b(“Amt”).ToString.Trim))
Select a

1 Like

@Charmaine_Matsaudza I don’t think you need to do a join Operation. Have you tried using the Except Method?

DT3 = DT1.AsEnumerable.Except(DT2.AsEnumerable,DataRowComparer.Default).CopyToDataTable

2 Likes

Hi @supermanPunch, thank you for your response. I need to use two columns, the “Amt” and “PAN” from each table to determine the duplicates.

@Charmaine_Matsaudza Can you give us an Example Input Data’s and the Expected Output that you need. We can help a lot faster if we know that.

@supermanPunch

Please find below dt1 and dt2, the sample input datatables. dt3 which shows the rows in dt1 which do not matc rows in dt2 based on the PAN and Amt columns and dt4 which shows the rows in dt2 which do not match the rows in dt1 based on the PAN and Amt columns.
dt1 sample
dt2 sample
dt3 sample
dt4 sample

1 Like

@Charmaine_Matsaudza Yes. I am trying to reproduce the data in an Excel and work on it. Meanwhile you can test this linq query and check the results it provides. The solution might be related to this Linq Query but with some modifications.

DT1.AsEnumerable().Where(Function(row) Not DT2.AsEnumerable().Select(function(r) r(“Amt”).ToString).Any(Function(x) x = row(“Amt”).ToString)).CopyToDataTable

1 Like

Thank you @supermanPunch, let me try to work with this one for now. Thank you so much:smiley:

@Charmaine_Matsaudza Can you try with this Query:

DT1.AsEnumerable().Where(Function(row) Not DT2.AsEnumerable().Where(function( r ) r(“PAN”).ToString.Equals(row(“PAN”).ToString) and r(“Amt”).ToString.Equals(row(“Amt”).ToString)).Any).CopyToDataTable

It gives me a bit different output that what you needed but can you check it and determine if it is actually the Output you need taking into consideration that you need PAN and AMT to be not Matching.

1 Like

Hi @supermanPunch
Is this the right query? I am getting a compiler error. Not sure what I’m doing wrong

DT1.AsEnumerable().Where(Function(row) Not DT2.AsEnumerable().Where(function(r) r(“PAN”).ToString.Equals(row(“PAN”).ToString) and r(“Amt”).ToString.Equals(row(“Amt”).ToString)).Any).CopyToDataTable

@Charmaine_Matsaudza What’s the error?

Hi @supermanPunch, I managed to resolve the error, but the result produced by the linq is showing the matched rows instead of showing the unmatched rows

@Charmaine_Matsaudza How did you resolve the Error? Did you make any changes in the Query?

@supermanPunch I added a bracket that was missing. after the

Where(function( r ) r

@Charmaine_Matsaudza Check this Workflow :
Get_NonMatchingRows.xaml (11.4 KB)

1 Like

Hey @supermanPunch , sorry to keep bothering but the resulting data table has some matched rows included.

@Charmaine_Matsaudza Yes. If the row which you think should not be present is 5/10/2020 row, can you tell me why it shouldn’t be in the Output. Because even it satisfies the criteria for the data that you have provided

@supermanPunch It should not satisfy the criteria because it has a matching row in dt2 which has the same PAN and amount, which is the row with ID 4 in dt2

@Charmaine_Matsaudza The Amount does not match right ? :sweat_smile: It’s 20.3 and 20.03

1 Like

@supermanPunch, oh yes , you are right. :sweat_smile: :sweat_smile: , sorry about that.
one last question, how do i then get dt4, which shows the rows in dt2 that do not match dt1. Do i swap the positions of the input data tables in the linq query?

@Charmaine_Matsaudza If you can understand the query, the result it provides now is based on DT1 which is actually called at the beginning, and since the columns names are the same in both datatables that you want to compare, you would just need to Switch DT1 with DT2 and DT2 with DT1.

Check if it gets the Output you need.

1 Like