[RESOLVED] LINQ : combine two selects like a SQL CTE?

[geek648]

Quick Question, I hope, is there a way to Select from a Select in LINQ?
Combine two operations in one, rather than one step following another?
Just so I can read data from a DGV, but name the fields with simple aliases so that I can then clearly do the complicated bits with simple field names... i.e.

' Get data from long-winded row.Cells() into nice short names
Dim FirstGo = From row As DataGridViewRow In DataGridView1.Rows _
              Select Field1= CDec(row.Cells("A Column").Value) ' etc
' Now do what we need with nice short names
Dim Refined = From .. FirstGo.. ' and use Where/Select using nice short Field1 name

It's not all that important, but I'd be interested to know if it can be done. Googling hasn't thrown up anything like it, so I suspect I'm asking for too much
Thanks

[Joacim Andersson]

Yes you can, you can have multiple Select in a single LINQ query.

Dim FirstGo = From row As DataGridViewRow In DataGridView1.Rows _
              Select Field1= CDec(row.Cells("A Column").Value), Field2 = row.Cells("B Column").ToString, ... _
              Where Field1 > 5 _
              Select Field2, ...

[geek648]

Ah, brilliant, many thanks!
What a great way to play with data, impressive stuff

The Wrong Way

At first I couldn't get this to work

Dim tst = From row As DataGridViewRow In DataGridView1.Rows

because of an error : Option Strict On disallows implicit conversions from 'Object' to 'System.Windows.Forms.DataGridViewRow'

So to work around that, I toyed with a user-defined variable type (like a Pascal 'record') and a list of those, manually adding to the list from a loop around the DGV.

    Private Structure MyRow
        Property field1 As Double
        Property field2 As Double
    End Structure

        Dim myList As IList(Of MyRow)
        myList = New List(Of MyRow)
        For cnt As Integer = 0 To DataGridView1.Rows.Count - 1
            If Not (DataGridView1.Rows(cnt).IsNewRow) Then
                myList.Add(New myRow() With
                   {.field1 = CDbl(DataGridView1.Rows(cnt).Cells(0).Value),
                    .field2 = CDbl(DataGridView1.Rows(cnt).Cells(1).Value)
                   })
            End If
        Next

        ' LINQ query
        Dim Results = From Rows In myList
                           Group By ... Into ...
                           Select ......
                           Order By .... etc

The Better Way

With a simple Cast, and the help given above, here's the easy way.
Much better now I can do this all in one go!
e.g....

        Dim tst = From row In DataGridView1.Rows.Cast(Of DataGridViewRow)()
                        Where Not row.IsNewRow
                        Select field1 = CDbl(row.Cells(0).Value), field2 = CDbl(row.Cells(1).Value)
                        Group By field1 Into f1_Distinct = Min(field1), f2_Distinct = Max(field2)
                        Select field1 = f1_Distinct, field2 = f2_Distinct
                        Order By field1


        If tst.Count > 0 then MsgBox(tst(0).field1)