Join Data Table through LINQ dynamically

Help Studio
, ,
1

Dear Forum Members,

I need to perform left join on two data tables using LINQ query.

Dt1 : Col1 Col2 Col3 and so on
A xx YY ______
B CF HH _____
C GJ FH ______

Dt2: Col1,Col4 Col5 and so on
A AB _________________
B BH _________________
D TH _________________

There is a possibility that few column names are same in both datatables. I need to validate the data in both tables.

Now, key columns and columns to be validated will be in config file.

I want the output as:

Key columns and columns to be validated with Status and Remarks.

I have tried using :

(
From a In DT1
Group Join b In DT2
On a(“col1”).ToString Equals b(“col1”).ToString And a(“col2”).ToString Equals b(“col2”).ToString Into grp = Group
From g In grp.DefaultIfEmpty
Select DT3.Rows.Add({a(“col1”),a(“col2”), If(IsNothing(g),Nothing,g(“col3”))})
).CopytoDataTable

How can I pass multiple columns in order to take data from 2nd DT. I hope, I could explain the query.

Thanks in advance!

2

HI @dimple.khurana

dt3 = (From a In dt1
       Group Join b In dt2
       On
        {
          a("Col1").ToString,
          a("Col2").ToString
          ' Add other key columns as needed
        }
        Equals
        {
          b("Col1").ToString,
          b("Col2").ToString
          ' Add corresponding key columns from the second DataTable
        }
       Into grp = Group
       From g In grp.DefaultIfEmpty
       Select dt3.Rows.Add(
         {
           a("Col1"),
           a("Col2"),
           ' Add other columns from the first DataTable as needed
           If(g Is Nothing, Nothing, g("Col3").ToString())
           ' Add corresponding columns from the second DataTable
         })
       ).CopyToDataTable

Or


dt1 = (From a In dt1.AsEnumerable()
       Group Join b In dt2.AsEnumerable()
       On a.Field(Of String)("Col1") Equals b.Field(Of String)("Col1") And
          a.Field(Of String)("Col2") Equals b.Field(Of String)("Col2")
       Into grp = Group
       From g In grp.DefaultIfEmpty()
       Select dtResult.Rows.Add({
           a.Field(Of String)("Col1"),
           a.Field(Of String)("Col2"),
           If(g Is Nothing, Nothing, g.Field(Of String)("Col3")),
           If(g Is Nothing, "Not Found in DT2", "Found in DT2")
       })).CopyToDataTable()
3

Hi @dimple.khurana

Try this:

dt3 = (
    From a In dt1.AsEnumerable()
    Group Join b In dt2.AsEnumerable()
    On keyColumns.Select(Function(col) a.Field(Of String)(col)).SequenceEqual(keyColumns.Select(Function(col) b.Field(Of String)(col))) Into grp = Group
    From g In grp.DefaultIfEmpty()
    Select dt3.Rows.Add(
        keyColumns.Select(Function(col) a.Field(Of String)(col)).ToArray() _
        .Concat(validateColumns.Select(Function(col) If(g Is Nothing, Nothing, g.Field(Of String)(col)))).ToArray()
    )
).CopyToDataTable()

Hope it helps!!

4

Just to be sure, you know there is a Join Data Table activity, right? Makes it simple. If columns in both tables have the same name it’ll just rename the ones from the right table with a _1 on the end.