Left join using Linq query for 2 datatable

Help Activities
, , ,
1

I have dtA with 707912 rows and dtB with 1048575 rows.

Ive tried using Linq to left join dtA with dtB.
However, number of rows for output is 726173 rows. Which is quite weird. Isn’t it supposed to follow number of rows for dtA?

Below is my steps:

  1. Build data table with column names (dtLatest)
  2. Assign activity for my linq query

(From d1 In dtA.AsEnumerable
Group Join d2 In dtB.AsEnumerable On d1(“Leg ID”) Equals d2(“service_id”) Into gj = Group
From g In gj
Select ra = {d1(“Leg ID”), g(“activation_date”),g(“sub_status”),g(“status_reason_desc”),“”}
Select dtLatest.Rows.Add(ra)).CopyToDataTable

2

@aqiffm

One possible reason can be there might be repeated rows in dtB which would have duplicated the join

Check the same

Cheers

3

Hi @aqiffm

Try this:

(From d1 In dtA.AsEnumerable()
                Group Join d2 In dtB.AsEnumerable()
                On d1("Leg ID") Equals d2("service_id")
                Into gj = Group
                From g In gj.DefaultIfEmpty()
                Select dtLatest.Rows.Add({d1("Leg ID"),
                                          If(g IsNot Nothing, g("activation_date"), ""),
                                          If(g IsNot Nothing, g("sub_status"), ""),
                                          If(g IsNot Nothing, g("status_reason_desc"), ""),
                                          ""})).CopyToDataTable()

Hope it helps!!

4

@aqiffm

dtLatest = (From d1 In dtA.AsEnumerable
            Group Join d2 In dtB.AsEnumerable On d1("Leg ID") Equals d2("service_id") Into gj = Group
            From g In gj.DefaultIfEmpty()
            Select dtLatest.Rows.Add({d1("Leg ID"),
                                      If(g IsNot Nothing, g("activation_date"), Nothing),
                                      If(g IsNot Nothing, g("sub_status"), Nothing),
                                      If(g IsNot Nothing, g("status_reason_desc"), Nothing),
                                      ""})).CopyToDataTable
5

this solves the issue. It shows that the dtB has duplicate value of “Leg ID” column. by removing duplicate value in “Leg ID”

1 Like
6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.