How can join 2 datatable with linq

Help Activities
1

Dear RPA developer
I have excel 2 files. Kindly help me please.
How can join 2 datatable with linq

Sample

DT01 DT02
AA BB AAA BBB
3BB OW 3BBA XXX
VALUE0 CA VALUE01 ABC

I need value column AA from DT01 and BBB from DT02
with criteria column AA in DT01 constrain value DT02 in column AAA.

result expected.

AA BBB
3BB XXX
VALUE0 ABC

Thank you.

2

prepare a datatable with build datatable, configure 2 cols: AA, BB - dtResult

Assign Activity
LHS: dtResult
RHS:

(From d1 in dt1.AsEnumerable
Join d2 in dt2.AsEnumerable
On d1("AA").toString.Trim Equals d2("AA").toString.Trim
Let ra = new Object(){d1("AA"), d2("AA"})
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

In case of empty results we can defensive handle as well and avoid the empty row exception thrown by the CopyToDataTAble method

Kindly note:

are not matching so it will not be in the output. Maybe it was a typo

3

@ppr Seem like not have output because

AA = 3BB
AAA = 3BBA

I think Equals not working.
On d1(“AA”).toString.Trim Equals d2(“AA”).toString.Trim

Thanks.

4

For a LINQ inner join the dtleft-Statement Equals dtRight.Statement is mandatory
also, we got some case cases run successfully in the past

can you share your xaml / implementation details with us? We will also review your case soon

5

@daviday.guys Kindly go through it, this will help you definitely

1 Like
6

after a crosscheck we can state following from your samples:
3BB will match as it starts with at 2BBA
same for VALUE0 and VALUE01

For AA we can not work with startsWith rule and do need additonal rules e.g. a min length

A LINQ will leave using the Join Operator and could look like this

(From d1 In dt1.AsEnumerable
Let wm = dt2.AsEnumerable.Where(Function (x) x("AA").toString.Trim.StartsWith(d1("AA").ToString.Trim))
From d2 In wm
Where d2("AA").toString.Trim.Length > 2
Let ra = New Object(){d1("AA"), d2("AA")}
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

2 is just a threshold, but we would recommend recheck any match rule more close to your business case