Tony Gardner
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:
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:
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!
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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))
Have you looked at System.Linq.Dynamic which you can get via NuGet?