Avatar of Tony Gardner
Tony Gardner
Flag for United States of America asked on

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.NETLINQ QuerySQL

Avatar of undefined
Last Comment
Tony Gardner

8/22/2022 - Mon
Norie

Tony

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

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Tony Gardner

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))
Your help has saved me hundreds of hours of internet surfing.
fblack61