• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • 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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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