[RESOLVED] LINQ Select most recent data row using OrderByDescending on DateTime column

[bigteks]

diskpoolParsed.result is an array of list(of string) that comes back from JSON deserialization, which includes columns like result().id, result().type & result().datetime among other things.

So I am trying to assign to storageRow() the most recent row in time from result() using the result().timestamp column and also where result().type = 2

I can't seem to figure out the comparison function and I would appreciate some pointers.

Dim storageRow As List(Of String) = From d_row In diskpoolParsed.result.AsEnumerable()
                                          Where d_row.type = 2
                                          Group d_row By rowid = d_row.id Into grp = Group
                                          Select grp.OrderByDescending(Of DateTime)(g => g.datetime).First()

[ident]

diskpoolParsed.result is an array of list(of string) that comes back from JSON deserialization, which includes columns like result().id, result().type & result().datetime among other things.

So I am trying to assign to storageRow() the most recent row in time from result() using the result().timestamp column and also where result().type = 2

I can't seem to figure out the comparison function and I would appreciate some pointers.

Dim storageRow As List(Of String) = From d_row In diskpoolParsed.result.AsEnumerable()
                                          Where d_row.type = 2
                                          Group d_row By rowid = d_row.id Into grp = Group
                                          Select grp.OrderByDescending(Of DateTime)(g => g.datetime).First()

array of list(of string)? No such thing.

[jmcilhinney]

array of list(of string)? No such thing.

That's not strictly true. You can make an array of anything, e.g.

Dim list1 As New List(Of String)
Dim list2 As New List(Of String)
Dim arr = {list1, list2}

That is now an array of List(Of String).

That said, the code in post #1 doesn't really make sense because neither an array, a List(Of T) nor a String have the properties indicated. What EXACTLY is 'diskpoolParsed.result'? Is it actually a typed DataTable? The AsEnumerable call seems to suggest that it is. I think that you need to try again to provide a FULL and CLEAR explanation.

If 'diskpoolParsed.result' is indeed a List(Of String) array then 'd_row' is a List(Of String) so you simply can't do things like 'd_row.type = 2'. You can only access the items in a List by index, so if the 'type' value is the second item in the List then you'd have to use 'd_row(1) = "2"'. Notice that, because a List(Of String) contains Strings, you need to compare to a String, not an Integer. That also means that sorting the 'datetime' column will be useless unless the format of the values is such that chronological order and alphabetical order coincide. Do they? If not then you would need to convert the String to a DateTime before doing the sorting.

Assuming that you really do have a List(Of String) array, you can get the element with the latest 'datetime' value like so:

Dim storageRow As List(Of String) = From d_row In diskpoolParsed.result.Last(Function(list) Convert.ToDateTime(list(2)))

That's it. Methods like First and Last let you specify sorting criteria so there's no need for a separate OrderBy or OrderByDescending.

[bigteks]

OK sorry for the bad example, hopefully this makes more sense. This is not how the real data gets filled, it is filled by the JSON deserializer. But the data structure is the same.

Still not working, still trying to figure out how to set up the compare statement (the Function() part):

Module Module1

    Sub Main()

        Dim diskpoolparsed As storage.JSONmapHeader = New storage.JSONmapHeader
        Dim storageRecord As storage.Result

        storageRecord = New storage.Result("One", "2", "0", "304785", "2018-02-24 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)

        storageRecord = New storage.Result("Two", "1", "1", "304780", "2018-02-24 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)

        storageRecord = New storage.Result("Three", "0", "2", "304700", "2018-02-24 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)

        storageRecord = New storage.Result("One", "2", "3", "304600", "2018-02-23 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)
 
        storageRecord = New storage.Result("Two", "1", "4", "304500", "2018-02-23 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)
 
        storageRecord = New storage.Result("Three", "0", "5", "304400", "2018-02-23 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)
 
        storageRecord = New storage.Result("One", "2", "6", "304300", "2018-02-22 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)
 
        storageRecord = New storage.Result("Two", "1", "7", "304200", "2018-02-22 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)
 
        storageRecord = New storage.Result("Three", "0", "8", "304100", "2018-02-22 03:00:00.052")
        diskpoolparsed.result.Add(storageRecord)
 
        'find most recent storageRecord of type = "2":
 
        storageRecord = From d_row In diskpoolparsed.result.Last(Function(d_row) 
                        Convert.ToDateTime(d_row.dateStr))
                        Where d_row.type = "2"
 
    End Sub
 
End Module
 
Public Class storage
 
    Public Class Result
        Public Property name As String
        Public Property type As String
        Public Property id As String
        Public Property used As String
        Public Property dateStr As String
        Public Sub New(N As String, T As String, I As String, U As String, D As String)
            name = N
            type = T
            id = I
            used = U
            dateStr = D
        End Sub
    End Class
    Public Class JSONmapHeader
        Public Property result As List(Of Result)
        Public Property status As Integer
    End Class
 
End Class

[bigteks]

I think this is what I was looking for:

        storageRecord = (From d_row In diskpoolparsed.result
                         Order By Convert.ToDateTime(d_row.dateStr)
                         Select d_row
                         Where d_row.type = "2").First()

[ident]

That's not strictly true. You can make an array of anything, e.g.

I worded that very badly. My head knew what I was saying but jibberish come out.

[jmcilhinney]

I think this is what I was looking for:

        storageRecord = (From d_row In diskpoolparsed.result
                         Order By Convert.ToDateTime(d_row.dateStr)
                         Select d_row
                         Where d_row.type = "2").First()

For the record, using pure function syntax, that would look like this:

storageRecord = diskpoolparsed.result.
                               Where(Function(d_row) d_row.type = "2").
                               First(Function(d_row) Convert.ToDateTime(d_row.dateStr))

By the way, the Select should come last using query syntax. Logically, the Where should precede the Order By and that should precede the Select. Filter first and then sort, so there are fewer comparisons, i.e. no point sorting data that will be filtered out. It probably doesn't matter as most LINQ providers would probably do that implicitly anyway but it's a good habit to adopt.