troubleshooting Question

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

Avatar of Tony Gardner
Tony GardnerFlag for United States of America asked on
Visual Basic.NETLINQ QuerySQL
4 Comments1 Solution145 ViewsLast Modified:
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))
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

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!
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros