How to build syntax to LINQ Query in vb.net

[Guy Zommer]

Hi,

I would like to compare between codes per item in master table called "Master Company" and selected company/s in order to compare codes between companies.

For Example one common item:

Master company contains:

Item: A

Description: Letter A

Code : a1

Ref. Company 1:

Item : A

Code: Z1

Ref Company 2:

Item: A

Code: B1

...and so on number of companies depends on user selection

Following this example the result is:



What I did is:

Sub Needed()
Dim dtMaster As New System.Data.DataTable
dtMaster.Columns.Add("Item", GetType(Integer))
dtMaster.Columns.Add("Description", GetType(String))
dtMaster.Columns.Add("Code", GetType(String))

dtMaster.Rows.Add({1, "item 1", "A1"})
dtMaster.Rows.Add({2, "layer", "B1"})
dtMaster.Rows.Add({3, ",sXhmXXuk", "C1"})
dtMaster.Rows.Add({4, ",mXotXXzezet", "D1"})

'Number of reference table is according to how many compaines user is selected
Dim dtRefTables As New DataSet

Dim vDT1 As New System.Data.DataTable
vDT1.TableName = "Ref Table 1"
vDT1.Columns.Add("Item", GetType(Integer))
vDT1.Columns.Add("Code", GetType(String))

vDT1.Rows.Add({1, "b1"})
vDT1.Rows.Add({2, "c1"})
vDT1.Rows.Add({3, "e2"})
vDT1.Rows.Add({4, "x3"})

dtRefTables.Tables.Add(vDT1)

Dim vDT2 As New System.Data.DataTable
vDT2.TableName = "Ref Table 2"
vDT2.Columns.Add("Item", GetType(Integer))
vDT2.Columns.Add("Code", GetType(String))

vDT2.Rows.Add({1, "z1"})
vDT2.Rows.Add({2, "z1"})
vDT2.Rows.Add({3, "z2"})
vDT2.Rows.Add({4, "r3"})

dtRefTables.Tables.Add(vDT2)
'...and so on

Dim query =
From a In dtMaster
_
Join b In vDT1
On b.Field(Of Integer)("Item") Equals a.Field(Of Integer)("Item")
_
Join c In vDT1
On c.Field(Of Integer)("Item") Equals a.Field(Of Integer)("Item")
_
Select masterTableCol1 = a.Field(Of Integer)("Item"),
masterTableCol2 = a.Field(Of String)("Description"),
masterTableCol3 = a.Field(Of String)("Code"),
ref1 = b.Field(Of String)("Code"),
Ref2 = c.Field(Of String)("Code")

C1FlexGrid1.DataSource = query.ToList
End Sub


What I need is to change the LINQ query at run time according to the selection of the user.

I do not know who to do it:

Dim query =
From a In dtMaster
_

for i as integer =1 to dtRefTables.Tables.Count

Join ref In dtRefTables(i) On ref.Field(Of Integer)("Item") Equals a.Field(Of Integer)("Item")

next

_

Select masterTableCol1 = a.Field(Of Integer)("Item"),
masterTableCol2 = a.Field(Of String)("Description"),
masterTableCol3 = a.Field(Of String)("Code"),

for i as integer =1 to dtRefTables.Tables.Count

ref & i = dtRefTables(i).Field(Of String)("Code")


next i

Thanks a lot!



Guy Zommer

Continue reading...