Hello Experts,
I have a 1 linq expression. In this, i want some columns from DT1 & some columns to DT2. i tried multiple ways but ended in vain.
Can anyone please help me with this!!
(From DT1 In dt1.AsEnumerable()
From DT2 In dt2.AsEnumerable()
Where DT1(14).ToString().contains(DT2(1).ToString())
Select DT2).ToArray().CopyToDatatable
Please help @ppr @NIVED_NAMBIAR
@kantheshm
is the data from link below represing the sample data and expected output for this topic case?
If yes, Join Columns are Col2, Col3?
Hi @ppr ,
I don’t want to join col3, but I want only the data after join of col2.
Can you please help me with it!!
Thank you for your response…
Hi @ppr ,
To be more precise, Dt1 & Dt2 Col3 are not in match to join. Each has got unique number of records.
I need to do a join for col2 in DT1 & DT2 then i need to get Col1,Col2,col3,col4 from DT1 & Col4 from DT2.
Example as shown below
Dt1
Col1 – Col2 – Col3 – Col4
A – 11 – AA – 31
B – 12 – RT – 32
C – 13 – HL – 33
D – 27 – AA – 34
F – 15 – RT – 35
G – 24 – HL – 36
Dt2
Col1 – Col2 – Col3 – Col4
A – 11 – AA – 20
B – 12 – RT – 21
C – 13 – HL – 22
D – 27 – AA – 23
F – 15 – RT – 24
G – 24 – HL – 25
output
Col1 – Col2 – Col3 – Col4-Col5
A – 11 – AA – 31-20
B – 12 – RT – 32-21
C – 13 – HL – 33-22
D – 27 – AA – 34-23
F – 15 – RT – 35-24
G – 24 – HL – 36-25
Let me know, if you need more clarity on this…
What about this?
// joining dt1 with dt2 into dt3
dt3 = (From a In dt1.AsEnumerable
Join b In dt2.AsEnumerable
On a("col2 ") Equals b(“col2”)
Select dt3.LoadDataRow(New Object() {
a(“col1”),
a(“col2”),
a(“col3”),
a(“col4”),
b(“col4”)},
False)
).CopyToDataTable
Hi @J0ska ,
I am trying to implement same here, but getting some error
AFAIK the array you use to create the new object could only reference to keys and agregated values (like in SQL). So you can not include any “dt1” value unless it is “group by” key.
Cheers
Hi @J0ska ,
If i use that Colum in grp, then it tries to find a match. Since there is no match, i can not use that. And the 2nd option is aggregated values, in that it is string field and some time number, i cannot aggregate tat one.
So, any suggestion to tackle this scenario??
Maybe you could use something of this in the gpr
https://www.sensibledev.com/linq-firstordefault-vs-first/
Or ask @ppr 
who is an expert in LINQ
Cheers
We are in doubt on this statement. Bringing both datatables into a relationship requires a condition/rule for doing the pairing of dt1 and dt2 rows
Currently the case looks like following:
LINQ
(From d1 in dt1.AsEnumerable
Join d2 in dt2AsEnumerable
On d1(“Col3”).toString.Trim Equals d2(“Col3”).toString.Trim
Let ra = d1.ItemArray.Append(d2(“Col4”).toArray
Select dtResult.Rows.Add(ra)),CopyToDataTable
Here you are…
dt2 = (From row In dt1.AsEnumerable
Group row By keys = New With {
key .k1 = row.field(Of String)(“col1”)}
Into gr = Group
Select dt2.LoadDataRow(New Object() {
keys.k1,
gr.FirstOrDefault()(“col2”),
0,
gr.sum(Function(x) CDec(x.Item(“col4”)))},
False)
).CopyToDataTable
Thank for your inputs. Based on @J0ska response, i got some idea and implemented.
Thank you for the support
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.