locked
Get the Newest Record in Datatable for Each ID Using LINQ VB.Net RRS feed

  • Question

  • User-2100942636 posted

    I have the following datatable.

    ID Name Date
    440 C 2018-03-14 11:08:15.703
    439 B 2018-03-14 11:05:19.260
    438 A 2018-03-14 11:05:40.573
    440 C 2018-03-07 08:34:00.000
    439 B 2018-03-14 11:38:00.000
    438 A 2018-03-14 11:40:00.000
    441 D 2018-03-12 13:54:41.497
    441 D 2018-03-14 08:36:00.000
    442 E 2018-03-11 07:26:00.000

    I'm looking for a LINQ query in VB.Net that will return the most recent record for each ID. There may be duplicate IDs but there may not.

    I'm able to get a single record for each ID with the following...

    Dim dups = myDataTable.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("ID")).Where(Function(g) g.Count() > 1).Select(Function(g) g.Key)

    ...but I'm having a heck of a time getting the newest record for each ID. I could really use some guidance.

    Thanks

    Wednesday, March 14, 2018 7:56 PM

All replies

  • User2103319870 posted

    LINQ query in VB.Net that will return the most recent record for each ID.

    You can try with below code

    Dim result = From row In table.AsEnumerable()
                         Group row By VarDate = row.Field(Of String)("ID") Into GroupResults = Group
                         Select GroupResults.OrderByDescending(Function(r) r.Field(Of DateTime)("Date")).FirstOrDefault()
    
            For Each item As DataRow In result.AsEnumerable()
                Console.WriteLine(item("ID") + " " + item("Name") + " " + item("Date"))
            Next
    Wednesday, March 14, 2018 9:09 PM
  • User-2100942636 posted

    Thanks A2H! That did the trick. Awesome!

    Thursday, March 15, 2018 1:34 PM