Need Help: In Linq Query

Help Studio
, ,
1

This is my GL Master Data

GL Master
Id Name
1601901 ICT
1601902 Science
1601903 Maths

This is my Customer Data

Customer Data
Id Teacher
1900 reshma
1901 katrina
1902 rekha
1903 suresh
1904 ramesh

I need expected output as below

Expected Output
Id Teacher Name
1900 reshma NA
1901 katrina ICT
1902 rekha Science
1903 suresh Maths
1904 ramesh NA

I am currently using this LINQ query, and it is working correctly, but I am getting “NA” as the output because it uses “Equals”. I want to use “Contains” instead. Can someone update this LINQ query for me?

(From row2 In Customer_Dt.AsEnumerable()
                                Group Join row1 In GL_Dt.AsEnumerable()
                                On row2.Field(Of Object)(0) Equals row1.Field(Of Object)(0)
                                Into Group
                                From subrow In Group.DefaultIfEmpty()
                                Select Abb_Dt.LoadDataRow(New Object() {
                                If(subrow Is Nothing,"NA", subrow.Field(Of String)(1))
                                }, False)).CopyToDataTable()
2

@Vinit_Mhatre

From row2 In Customer_Dt.AsEnumerable()
Group Join row1 In GL_Dt.AsEnumerable()
On row2.Field(Of Object)(0) Equals row1.Field(Of Object)(0)
Into Group
From subrow In Group.DefaultIfEmpty()
Select Abb_Dt.LoadDataRow(New Object() {
    row2.Field(Of Object)(0),
    row2.Field(Of String)(1),
    If(subrow Is Nothing OrElse subrow.IsNull(1), "NA", subrow.Field(Of String)(1))
}, False)
3

Hi @Vinit_Mhatre

 (From row2 In Customer_Dt.AsEnumerable()
            Group Join row1 In GL_Dt.AsEnumerable()
            On GL_Dt.AsEnumerable().Any(Function(row1) row1.Field(Of Object)(0).ToString().Contains(row2.Field(Of Object)(0).ToString()))
            Into Group
            From subrow In Group.DefaultIfEmpty()
            Select Abb_Dt.LoadDataRow(New Object() {
                If(subrow Is Nothing, "NA", subrow.Field(Of String)(1))
            }, False)).CopyToDataTable()
4

still i am getting output as “NA”

5

Argument ‘Value’: BC36619: ‘Equals’ expected.
Argument ‘Value’: BC36641: Lambda parameter ‘row1’ hides a variable in an enclosing block, a previously defined range variable, or an implicitly declared variable in a query expression.

Can you provide me with the corrected query? The current one uses “Equals” and returns “NA”, but I need it to use “Contains” instead.

6

@Vinit_Mhatre

From row2 In Customer_Dt.AsEnumerable()
Group Join row1 In GL_Dt.AsEnumerable()
On row1.Field(Of Object)(0).ToString().Contains(row2.Field(Of Object)(0).ToString())
Into Group
From subrow In Group.DefaultIfEmpty()
Select Abb_Dt.LoadDataRow(New Object() {
    row2.Field(Of Object)(0),
    row2.Field(Of String)(1),
    If(subrow Is Nothing OrElse subrow.IsNull(1), "NA", subrow.Field(Of String)(1))
}, False)
7

Hi @Vinit_Mhatre

Can you try this LINQ expression:

(From row2 In Customer_Dt.AsEnumerable()
 Let matchingRows = (From row1 In GL_Dt.AsEnumerable()
                     Where row1.Field(Of Object)(0).ToString().Contains(row2.Field(Of Object)(0).ToString())
                     Select row1).ToList()
 From subrow In matchingRows.DefaultIfEmpty()
 Select Abb_Dt.LoadDataRow(New Object() {
 If(subrow Is Nothing, "NA", subrow.Field(Of String)(1))
 }, False)).CopyToDataTable()

Hope it helps!!

8

@Vinit_Mhatre
keep in mind, that the Query Syntax for join/group join has some constraints
e.g. the construct of leftside-statement Equals rightside-statement is mandatory

Your use case can also quickly modelled as a lookup case

Assign Activty:
dictLK | Dictionary(Of String, String) =

dtMaster.AsEnumerable.ToDictionary(Function (x) new String(x(0).ToString.Trim.TakeLast(4)),Function (x) x(1).toString.Trim)

Build DataTable - configure the expected outputdatatable with the three cols - dtResult

Assign Activity
dtResult =

(From d in dtCustomer.AsEnumerable
Let c = d("Id").toString.Trim
Let n = If(dictLK.ContainsKey(c), dictLK(c),"NA")
Let ra = d.ItemArray.Append(n).Cast(Of Object).ToArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable
1 Like
10

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