Linq query to compare 2 datatables

Help Activities
, , ,
1

Hi ,

I need help on below scenario using linq.
Having 2 datatables Master datatable, address datatable

I want to check the id present in Master datatable is available in address dt.
if id found, i want to add id, email address from address dt to respective id in Master dt.

data may have more than 50000 rows.

Thanks in Advance

2

Hi @meghana1929 ,

Please follow the below steps and LINQ code to achieve your requirement.

Use Build datatable activity and add column names you need in final output.
Lets say Output of BuildDataTable activity is Output_DT.
In the left side of assign activity add Output_DT and on the right side write below code.

(
From a In Master_DT
Join b In Address_DT
On a("ID").toString Equals b("ID").tostring
Select Output_DT.Rows.Add ({a("ID"), b("email address")})
).copytodatatable

Let me know if you need any clarification on the same.

Regards,
Ashutosh Gupta

3

Hi @Ashutosh.Gupta ,
Thank you for the code

From this code, i am getting only matching id rows,

i also want that to update “email id” column in output dt with NA for the rows if i id are not matching.

can you please help on this.

4

Hi @meghana1929 ,

Please take reference from below code.

From row1 In table1.AsEnumerable()
               Group Join row2 In table2.AsEnumerable()
               On row1.Field(Of Integer)("Id") Equals row2.Field(Of Integer)("Id")
               Into Group = Group
               From row2 In Group.DefaultIfEmpty()
               Select New With {.Id = If(row1 IsNot Nothing, row1.Field(Of Integer)("Id"), row2.Field(Of Integer)("Id")),
                                .Property1 = If(row1 IsNot Nothing, row1.Field(Of String)("Property1"), Nothing),
                                .Property2 = If(row2 IsNot Nothing, row2.Field(Of String)("Property2"), Nothing)}

fullJoin = fullJoin.Union(From row2 In table2.AsEnumerable()
                           Where Not table1.AsEnumerable().Any(Function(row1) row1.Field(Of Integer)("Id") = row2.Field(Of Integer)("Id"))
                           Select New With {.Id = row2.Field(Of Integer)("Id"),
                                            .Property1 = Nothing,
                                            .Property2 = row2.Field(Of String)("Property2")})

Assume we have two DataTables called table1 and table2

Here we first perform a left outer join using a group join and the DefaultIfEmpty method to include all rows from table1 and matching rows from table2. Then, we select a new anonymous type with the properties we want to include in the join.

Next, we use the Union method to append to the join all rows from table2 that did not match any rows from table1. We achieve this by checking if there are any rows in table1 with a matching Id using the Any method.

The resulting fullJoin object will contain all rows from both tables, with Nothing values for columns that were not found in the original table.

Regards,
Ashutosh Gupta