I have a Query regarding Linq. My problem statement is I have do a left join for two tables.
My dt1 - student No , Student Name , Student Average score, … extra columns
My Dt2 - Student roll no , Student Score , Student City , Student state … Extra columns
My output dt - Student No(dt1) , Student Name(dt1) ,Student Avg score(dt1) , Avg score (dt2), student city , student state
Its kind of like a vlookup where student no. are matching in both the sheets then add relevant dt2 columns else write as NA.
I tried this linq but its showing as ‘)’ expected compilation error
(From row1 In dt1.AsEnumerable()
Join row2 In dt2.AsEnumerable()
On row1.Field(Of String)(“Student no”) Equals row2.Field(Of String)(“student no”) Into joinedRows = Group
From row2 In joinedRows.DefaultIfEmpty()
Select outputdt.Rows.Add(row1(“student no”), row1(“student name”), row1(“Student score”), row1(“student age”),
row1(“student branch”),
If(row2 Is Nothing OrElse row2.IsNull(“student score”), “NA”, row2.Field(Of String)(“student score”)),
If(row2 Is Nothing OrElse row2.IsNull(“student state”), “NA”, row2.Field(Of String)(“student state”)),
If(row2 Is Nothing OrElse row2.IsNull(“student city”), “NA”, row2.Field(Of String)(“student city”)),“OK”
)).CopyToDataTable()
Pls check if there is any syntax error or any simpler version , I tried to first copy all the required columns from dt1 but I’m not able to just update remaining columns based on condition.
we keep in mind that Data Table reconstruction strategy for a Left join is tried
As a starter sample have a look here:
(From d1 In dt1.AsEnumerable()
Group Join d2 In dt2.AsEnumerable()
On d1("Student no").toString.Trim Equals d2("student no").toString.Trim
Into joinedRows = Group
From jrow In joinedRows.DefaultIfEmpty()
Let arrSet1 = New String() {"studen no","student name","Student Core", "student age", "student branch"}
Let arrSet2 = New String() {"student score","student state","student city"}
Let ra1 = arrSet1.Select(Function (e1) d1(e1))
Let raEmpty = arrSet2.Select(Function (ee) "NA").Cast(Of Object)
Let ra2 = If(isNothing(jrow), raEmpty, arrSet2.Select(Function (e2) jrow(e2)))
Let ra = ra1.Concat(ra2).Cast(Of Object).toArray
Select r = outputdt.Rows.Add(ra)).CopyToDataTable
Hey , Thank you so much !!! This worked. But still I didnt get what was the issue with the Linq syntax I was using.
But Im trying another linq for another scenario to update the column in the same sheet based on some conditions:
Example : I need to update the status column based on some Input column being not zero & need to match months of two columns . Pls check this Linq Query & let me know where is the mistake:
Im getting error as ‘Range variable name can be inferred only from a simple or qualified name with no arguments’
Thank you , Solution flag because the Query worked for the requirement on Group join between those two tables & combining the selected columns to a new array ra
Yes I used it in Assign & its accepted the Group Keyword. I didnt get any compilation issues - I just changed the datatype parsing as
on if(Double.TryParse(row.Field(of String)(“Student No”),Nothing) , convert.ToDouble(row1.Field(of String)(“Student No”)),Double NAN) _ equals Convert.ToDouble(row2(“ID”).ToString)
Also as suggested I have created another topic for my other Linq Query
Join … Into joinedRows = Group
(here without prepending group)
Requested more times for Feedback, (Screenshot of compiling Join without Group and used into construct)
Not expexcing that it will compile,
not found in Docu with this definition
not compiling in out cross tests
Mistake from my end , I liked the solution provided by you with ‘Group’ & thought the same was marked as solution. My Apologies …!! I made the other one as solution by mistake.
Corrected it now.!! Thank you so much for all the detailed explanations
