asked on 10/26/2018

Is it possible to use a variable in the SELECT clause of a LINQ query?

Hello Experts.

I'm hoping to find a better method for defining a LINQ query that can programatically determine which column/field to return.

I was actually able to do this fairly easily using SQL:

cmd.CommandText = "SELECT DISTINCT ? FROM Scoring WHERE LS = ?"
cmd.Parameters.Add(New OleDbParameter("@Param1", SL_Clmn))
cmd.Parameters.Add(New OleDbParameter("@Param2", LoserTP))

Open in new window

As you can see in the above SQL command, I want to retrieve the value of a specific column which needs to be initially defined as a string (in this case, the SL_Clmn variable).

Since LINQ only allows variables to be used in the WHERE clause and not the SELECT clause, I'm not able to specify which column I want from the selected row.

So right now, my code looks like this:

' LsrMP: Loser Match Points, LsrNo: Player 1 or 2
Dim LsrMP As Int32 = 0, LsrNo As Int32 = IIf(TN = 1, 2, 1)
' WnrMP: Winner Match Points, WnrNo: Player 1 or 2
Dim WnrMP As Int32 = 0, WnrNo As Int32 = IIf(TN = 1, 1, 2)
' LoserSL: Loser's Skill Level
Dim LoserSL As Int32 = CInt(PT(SK, LsrNo).Text)
' SL_Clmn: Skill Level Column (L1 through L9)
Dim SL_Clmn As String = "SL" & CStr(LoserSL)
' LoserTP: Loser's Total Points (out of 20)
Dim LoserTP As Int32 = CInt(PT(MP, LsrNo).Text)
'
Dim ScoreData =
(
   From getPoints In Me.sData.Scoring
   Where getPoints.LS = LoserTP
   Select getPoints
).ToList
'
For Each row In ScoreData
   Select Case SL_Clmn
      Case 1 : LsrMP = row.SL1
      Case 2 : LsrMP = row.SL2
      Case 3 : LsrMP = row.SL3
      Case 4 : LsrMP = row.SL4
      Case 5 : LsrMP = row.SL5
      Case 6 : LsrMP = row.SL6
      Case 7 : LsrMP = row.SL7
      Case 8 : LsrMP = row.SL8
      Case 9 : LsrMP = row.SL9
      Case Else : LsrMP = 0
   End Select
Next
WnrMP = 20 - LsrMP

Open in new window

Obviously, I really don't want to have to resort to a lengthy Select Case statement if I don't have to.

Any ideas or insights would be greatly appreciated!

Visual Basic.NET LINQ Query SQL

Last Comment

Tony Gardner

10/27/2018

Norie

10/26/2018

Tony

Have you looked at System.Linq.Dynamic which you can get via NuGet?

Tony Gardner

10/27/2018

ASKER

Sounds intriguing Norie. I did do some Google searches and found quite a bit of documentation, but didn't see anything that would allow me to use a variable to choose which column to return.

I've also been toying with the idea of returning a simple array. The nature of the Scoring table is such that the columns are perfectly oriented to accomplish my objective. Say for example, it returns a row with the numbers I need for all nine possibilities. Instead of trying to do a "Select row.L2" I could simply return ScoreData(2) since the first column is used for the query (Where getPoints.LS = LoserTP) and in a zero based array, that would make the desired return value for SL2 at the ScoreData(2) location.

I did try dong this just by changing ToList to ToArray, and in the debugger I can see my returned record with values in positions 0 - 9. I just don't know how to PUT that value into my LsrMP variable. In other words, attempting "ScoreData(2)" generates an Intellisense error (see attached image of a test sub with the error).
Test-Sub-with-Error-Message.png

ASKER CERTIFIED SOLUTION

Fernando Soto

10/27/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Tony Gardner

10/27/2018

ASKER

Thanks so much, Fernando. That's pretty much exactly what I needed to knock this one off!

I made a small revision to your idea and was able to accomplish it in a very tidy manner:

Dim ScoreData =
(
From getPoints In Me.sData.Scoring
Where getPoints.LS = LoserTP
Select getPoints.Item(LoserSL)
).ToArray()
'
IIf((ScoreData.Count = 0), LsrMP = 0, LsrMP = ScoreData(0))