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

Tony Gardner
Tony Gardner used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

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
Distinguished Expert 2017
Commented:
Hi Tony;

The reason you are having issues with this query is that Me.sData.Scoring represents a DataTable object. The getPoints is a DataRow object of the DataTable and NOT a single value. Therefore when you do this,  Select getPoints, it returns a DataRow object with all it properties and collections.
Dim ScoreData =
(
   From getPoints In Me.sData.Scoring
   Where getPoints.LS = LoserTP
   Select getPoints
).ToArray

Open in new window

To get a collection of individual columns from the row you could do this.
Dim ScoreData =
(
   From getPoints In Me.sData.Scoring
   Where getPoints.LS = LoserTP
   Select getPoints.ItemArray
).ToArray

Open in new window

Which will return and array of arrays. Each row of the array will contain a an array of columns.
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))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial