We help IT Professionals succeed at work.

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

132 Views
Last Modified: 2018-10-26
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!
Comment
Watch Question

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Tony

Have you looked at System.Linq.Dynamic which you can get via NuGet?
Tony GardnerSr. Programmer/Analyst

Author

Commented:
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
Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Tony GardnerSr. Programmer/Analyst

Author

Commented:
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))
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.