[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Need a LINQ Qry to get data from a multi-dimensional list - VB.net

Friends,

I have a multi-dimensional list and now I want to count the number of instances (using LINQ) that occur for the following conditions::

For Each Driver in ROPDrivers_AL

Dim DriverID As String = Driver
            'Phase 1
            Dim LapCount_Phase1 As Integer = (From ???
            Let key = CType(???) _
            Let value = CType(???, clsLapDetail) _
            Where value.LapSpeed >= 200 AndAlso value.LapSpeed < 205).Count()

Next

So in essence,  I want to go to the list that has the key of the driverID, and then i want to look through that driverID's  list of the lap details, for that driverid for a count of data elements that match the criteria of lapspeed.

Looking for VB.net syntax help, please.

Thanks in advance!

Eric
'Declaration of Multi-dimensional list:

    Private DriverList As New Dictionary(Of String, Dictionary(Of String, clsLapDetail))

'Declaration of clsLapDetail:

Public Class clsLapDetail
    Public Lap As Integer
    Public LapTime As Integer
    Public PassingTime As Integer
    Public ElapsedTime As Integer
    Public LapSpeed As Decimal
End Class

'Initial Population of DriverList:

    Private Sub PopulateInitialList()

        For Each Driver In ROPDrivers_AL

            DriverList.Add(Driver, New Dictionary(Of String, clsLapDetail))

        Next

    End Sub

'Populating the details for each driver:

Private Sub AddLapDetailsToDriverList()
        Dim dr As SqlClient.SqlDataReader = Nothing

        Dim SQLSelectStatement As String = "SELECT ..."

        Dim c As New SqlClient.SqlCommand(SQLSelectStatement, conUI3)


        Try
            Dim data As New DataSet
            data = New DataSet

            If conUI3.State = ConnectionState.Closed Then
                conUI3.Open()
            End If

            dr = c.ExecuteReader(CommandBehavior.SingleResult)

            Dim RowCount As Integer = 0
            If dr.HasRows Then
                'loop through the records
                While dr.Read()

                    Dim DriverID As String = dr("DriverID")
                    Dim RunID As String = dr("RunID")
                    Dim ResultID As String = dr("ResultID")
                    Dim ElapsedTime As Integer = dr("ElapsedTime")
                    Dim PassingTime As Integer = dr("PassingTime")
                    Dim LapCount As Integer = dr("LapCount")
                    'Dim LeaderLap As Integer = dr("LeaderLap")
                    'LastSFPassingID = dr("PassingID")
                    Dim Laptime As Integer = dr("LapTime")
                    Dim LapSpeed As Decimal = CalcLapSpeed(Laptime)

                    Dim LapDetail As New clsLapDetail
                    LapDetail.ElapsedTime = ElapsedTime
                    LapDetail.Lap = LapCount
                    LapDetail.PassingTime = PassingTime
                    LapDetail.LapTime = Laptime
                    LapDetail.LapSpeed = LapSpeed


                    Dim LapKey As String = RunID & "-" & LapCount

                    SyncLock DriverList

                        If DriverList.ContainsKey(DriverID) Then
                            DriverList(DriverID).Add(LapKey, LapDetail)

                        End If
                    End SyncLock
                End While
            End If
            dr.Close()
            ' con.Close()

           Catch ex As Exception

        Finally
            If c IsNot Nothing Then
                c.Dispose()
            End If

            If dr IsNot Nothing Then
                dr.Close()
            End If

        End Try

    End Sub

Open in new window

0
indy500fan
Asked:
indy500fan
  • 5
  • 4
1 Solution
 
indy500fanAuthor Commented:
Private Sub PopulateROPListview()


        For Each Driver In ROPDrivers_AL

            Dim DriverID As String = Driver
            'Phase 1
            Dim LapCount_Phase1 As Integer = (From Driver_ID In DriverList _
            Let key = DriverID _
            Let value = CType(Driver_ID.Value, clsLapDetail) _
            Where value.LapSpeed >= 200 AndAlso value.LapSpeed < 205).Count()


        Next


Here is a guess of mine, but I get the error:

Error      1      Value of type 'System.Collections.Generic.Dictionary(Of String, ROPTracker.clsLapDetail)' cannot be converted to 'ROPTracker.clsLapDetail'.      C:\EJK\My Projects\Base\VS2008\ROPTracker\ROPTracker\frmROPMain.vb      352      31      ROPTracker
0
 
Jeff CertainCommented:
New approach. :)

Private DriverList As New Dictionary(Of String, List(Of clsLapDetail))

Then, getting the laps you want is like so:
Dim lapCount As Integer = (From lap In DriverList(DriverID) Where lap.LapSpeed >= 200 AndAlso lap.LapSpeed < 205).Count
0
 
Jeff CertainCommented:
More completely, something like this:
'Declaration of Multi-dimensional list:
    Private DriverList As New Dictionary(Of String, List(Of clsLapDetail))

    'Declaration of clsLapDetail:
    Public Class clsLapDetail
        Public Lap As Integer
        Public LapTime As Integer
        Public PassingTime As Integer
        Public ElapsedTime As Integer
        Public LapSpeed As Decimal
    End Class

    'Initial Population of DriverList:
    Private Sub PopulateInitialList()
        For Each Driver In ROPDrivers_AL
            DriverList.Add(Driver, New List(Of clsLapDetail))
        Next
    End Sub

    'Populating the details for each driver:

    Private Sub AddLapDetailsToDriverList()
        Dim dr As SqlClient.SqlDataReader = Nothing

        Dim SQLSelectStatement As String = "SELECT ..."

        Dim c As New SqlClient.SqlCommand(SQLSelectStatement, conUI3)


        Try
            Dim data As New DataSet
            data = New DataSet

            If conUI3.State = ConnectionState.Closed Then
                conUI3.Open()
            End If

            dr = c.ExecuteReader(CommandBehavior.SingleResult)

            Dim RowCount As Integer = 0
            If dr.HasRows Then
                'loop through the records
                While dr.Read()
                    Dim DriverID As String = dr("DriverID")
                    Dim RunID As String = dr("RunID")
                    Dim ResultID As String = dr("ResultID")
                    Dim ElapsedTime As Integer = dr("ElapsedTime")
                    Dim PassingTime As Integer = dr("PassingTime")
                    Dim LapCount As Integer = dr("LapCount")
                    'Dim LeaderLap As Integer = dr("LeaderLap")
                    'LastSFPassingID = dr("PassingID")
                    Dim Laptime As Integer = dr("LapTime")
                    Dim LapSpeed As Decimal = CalcLapSpeed(Laptime)

                    Dim LapDetail As New clsLapDetail
                    LapDetail.ElapsedTime = ElapsedTime
                    LapDetail.Lap = LapCount
                    LapDetail.PassingTime = PassingTime
                    LapDetail.LapTime = Laptime
                    LapDetail.LapSpeed = LapSpeed

                    SyncLock DriverList
                        If DriverList.ContainsKey(DriverID) Then
                            DriverList(DriverID).Add(LapDetail)
                        End If
                    End SyncLock
                End While
            End If
            dr.Close()
        Catch ex As Exception

        Finally
            If c IsNot Nothing Then
                c.Dispose()
            End If

            If dr IsNot Nothing Then
                dr.Close()
            End If
        End Try
    End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
indy500fanAuthor Commented:
Chaosian,

Wow...

I added RunID to my clsLapDetail, as I might need it for later, but this is bloody brilliant.

Thank you!
0
 
Jeff CertainCommented:
Very glad to help. How's the performance on this? I might be able to offer you a bit of a cheat on creating the clsLapDetail.
0
 
indy500fanAuthor Commented:
Much better!  It barely blips the processor!

What sort of cheat?
0
 
Jeff CertainCommented:
It's called projection. It's a cool LINQ trick.

            Using dr As SqlClient.SqlDataReader = c.ExecuteReader(CommandBehavior.SingleResult)
                If dr.HasRows Then
                    'loop through the records
                    While dr.Read()
                        Dim DriverID As String = dr("DriverID")

                        Dim lapDetail As clsLapDetail = From item In dr
                                                        Select New clsLapDetail With {.RunId = dr("RunID"),
                                                                                      .ElapsedTime = dr("ElapsedTime"),
                                                                                      .PassingTime = dr("PassingTime"),
                                                                                      .LapTime = dr("LapTime"),
                                                                                      .LapSpeed = CalcLapSpeed(dr("LapTime")),
                                                                                      .Lap = dr("LapCount")}
                        SyncLock DriverList
                            If DriverList.ContainsKey(DriverID) Then
                                DriverList(DriverID).Add(lapDetail)
                            End If
                        End SyncLock
                    End While
                End If
            End Using
0
 
indy500fanAuthor Commented:
I will try it.
0
 
indy500fanAuthor Commented:
Processing is now MUCH more efficient!  Chaosian rocks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now