Join Datatable Using Linq

[Manii_K]

Hi Team,

I have 2 DT as shown in below image, I want to join both as DT3 Using Linq, Anyone Kindly Suggest

[lrtetala (Lakshman Reddy)]

Hi @Manii_K

Can you try the below

Code:

For Each row1 As DataRow In dt1.Rows
    Dim matchingRow As DataRow = dt2.AsEnumerable().Where(Function(r) r("Name").ToString() = row1("Name").ToString() AndAlso r("Product").ToString() = row1("Product").ToString()).FirstOrDefault()
    If matchingRow IsNot Nothing AndAlso Not String.IsNullOrWhiteSpace(matchingRow("Price").ToString()) Then
        row1("Price") = matchingRow("Price").ToString()
    End If
Next

Output:

Regards,

[Anil_G (Anil Gorthi)]

@Manii_K

Use invoke code by sending dt1 as in/put and dt2 as in

dt1.AsEnumerable.Where(function(x) String.IsNullOrEmpty(x("Price").ToString.Trim)).ToList.ForEach(sub(r) r("Price") = dt2.AsEnumerable.Where(function(x) x("Name").ToString.Equals(r("Name").ToString AndAlso x("Product").ToString.Equals(r("Product").ToString)).First(function(x) x("Price").ToString))

Cheers

[Manii_K]

Thanks for the Reply, Its Working

[ppr (Peter Preuss)]

the sample values do look constructed. Instead of Filter processing we can use the technique of merging rows with a group by

dt3 =

(From d In dt1.AsEnumerable.Concat(dt2.AsEnumerable)
Group d By k1=d("Name").toString.Trim, k2=d("Product").toString.Trim Into grp=Group
Let rpr = grp.OrderBy(Function (g) If(isNothing(g("Price")), 0, g("Price").toString.Trim.Length)).Last()
Let pr = rpr("Price")
Let pos = {dt1.Rows.IndexOf(rpr),dt2.Rows.IndexOf(rpr)}.Max()
Order By pos
Let ra = New Object(){k1,k2,pr}
Select r = dt3.Rows.Add(ra)).CopyToDataTable