LINQ - Entity Framework Core - SELECT most recent record, only if it matches another condition

entity-framework-core linq-to-entities

Question

I have a dataset that looks like this. I want to select all rows where the most recent record for a node has a status of 'Pending'

Nodeid  Status     Type          Utctimestamp
------  -----      ------        --------   

1       Pending    Transaction   2020-03-13 03:31:00.00000+00
1       Overridden Transaction   2020-03-13 03:32:00.00000+00
2       Unknown    Other         2020-03-13 03:34:00.00000+00
2       Pending    Other         2020-03-13 03:35:00.00000+00

A record for each node should be returned only if the status of the most recent record is Pending (otherwise don't select anything for that node). So for the dataset above, the query will only return the following:

2       Pending    Other         2020-03-13 03:35:00.00000+00

I want to use a LINQ query with Entity Framework, using fluent syntax ('Method syntax'). How do I do that?

1
0
3/16/2020 4:43:24 AM

Popular Answer

I achieved it like this:

var pending = myContext.Records
                         .GroupBy(t => t.Nodeid)
                         .Select(grp => grp.OrderByDescending(t => t.Utctimestamp).First())
                         .Where(t => t.Status == "Pending");
0
3/16/2020 5:59:00 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow