convert query result to dictionary in vb.net

Question

What is the proper syntax for .ToDictionary to return the following as a Dictionary(Of String, String) where ShortDesc is the key and CurrentFrontSymbol is the value

 Dim dicQuery As Dictionary(Of String, String) = (From d In toolkitEntities.currentfrontcommoditysymbols
                                                     Select d.ShortDesc, d.CurrentFrontSymbol)

Update

And can the following functions query and following For Each loop become one LINQ query?

Public Shared Function GetRangeProjectionPerformance(Optional daysToRetrieve As Integer = 100) As Dictionary(Of Integer, List(Of ProjectionPerformance))

    Dim todaysDate As Date = DateTime.Now.Date
    Dim lookbackDate As Date = todaysDate.AddDays(daysToRetrieve * -1)
    Dim temp As New Dictionary(Of Integer, List(Of ProjectionPerformance))


    Using ctx As New ProjectionsEntities()
        Dim query = (From d In ctx.projections
                     Where d.SymbolId <= 42 AndAlso d.Date >= lookbackDate
                     Join t In ctx.symbols On d.SymbolId Equals t.Id
                     Let actualRange = d.HighProjection - d.LowProjection
                     Select New With {
                        d.Date,
                        d.SymbolId,
                        t.Name,
                        actualRange}).GroupBy(Function(o) o.SymbolId).ToDictionary(Function(p) p.Key)

        For Each itm In query
            Dim rpp As New ProjectionPerformance
            Dim rppList As New List(Of ProjectionPerformance)
            If itm.Value.Count > 0 Then
                For x As Integer = 0 To itm.Value.Count - 1
                    Dim bb As Integer = Convert.ToInt32(itm.Value(x).SymbolId)
                    With rpp
                        .SymbolId = bb
                        .ProjectionDate = itm.Value(x).Date.ToString()
                        .Name = itm.Value(x).Name
                        .ProjectedRange = itm.Value(x).actualRange
                    End With
                    rppList.Add(rpp)
                Next
            End If

            temp.Add(itm.Key, rppList)
        Next
    End Using
    Return temp
End Function

Answer 1

As you do not have a special projection in the select, you can just call ToDictionary on the collection. The first lambda expression retrieves the key, the second one the value.

Dim dicQuery = toolkitEntities.currentfrontcommoditysymbols.ToDictionary( _
                   Function(x) x.ShortDesc, _
                   Function(y) y.CurrentFrontSymbol)

As for your update: the following query should get you the desired result:

Dim query = (From d In ctx.projections
             Where d.SymbolId <= 42 AndAlso d.Date >= lookbackDate
             Join t In ctx.symbols On d.SymbolId Equals t.Id
             Let actualRange = d.HighProjection - d.LowProjection
             Select New With {
                d.Date,
                d.SymbolId,
                t.Name,
                actualRange}).GroupBy(Function(o) o.SymbolId)
            .ToDictionary(Function(p) p.Key, 
                          Function(x) x.Select(Function(y) New ProjectionPerformance() With {
                              .SymbolId = Convert.ToInt32(y.SymbolId), 
                              .ProjectionDate = y.Date.ToString(), 
                              .Name = y.Name, 
                              .ProjectedRange = y.actualRange
                          }).ToList())