[RESOLVED] LINQ Select most recent data row using OrderByDescending on DateTime column
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.
Code:
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()
Re: LINQ Select most recent data row using OrderByDescending on DateTime column
Quote:
Originally Posted by
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.
Code:
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.
Re: LINQ Select most recent data row using OrderByDescending on DateTime column
Quote:
Originally Posted by
ident
array of list(of string)? No such thing.
That's not strictly true. You can make an array of anything, e.g.
vb.net Code:
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:
vb.net Code:
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.
Re: LINQ Select most recent data row using OrderByDescending on DateTime column
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):
Code:
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
Re: LINQ Select most recent data row using OrderByDescending on DateTime column
I think this is what I was looking for:
Code:
storageRecord = (From d_row In diskpoolparsed.result
Order By Convert.ToDateTime(d_row.dateStr)
Select d_row
Where d_row.type = "2").First()
Re: LINQ Select most recent data row using OrderByDescending on DateTime column
Quote:
Originally Posted by
jmcilhinney
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.
Re: LINQ Select most recent data row using OrderByDescending on DateTime column
Quote:
Originally Posted by
bigteks
I think this is what I was looking for:
Code:
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:
vb.net Code:
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.
