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
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
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!