Query to retrieve the latest status of each action

Question

I'm new about LinQ technologies. My program generates actions (moving files, deleting files, ...) and saves the status of each action in a database. Let's say that the statutes are simply passed (1) or failed (0). While my action isn't a success I try it again. For each trial I write a line in my database :

ID  ProcessedOn             ActionID  Description  Status
--- ----------------------- --------- ------------ ------
20  2011-05-20 10:45:01.440 24871     xxx          0 
21  2011-05-20 10:45:09.080 24873     xxx          0 
22  2011-05-20 11:00:01.993 24871     xxx          0 
23  2011-05-20 11:00:10.477 24873     xxx          1 
24  2011-05-20 11:15:08.127 24871     xxx          1

I would like to write a LINQ query to retrieve the latest status of each action between 2 moment. Is this possible?

I already wrote this :

 MyTable.Where(t => t.ProcessedOn >= start).Where(t => t.ProcessedOn <=
 stop).OrderBy(t => t.ProcessedOn).ToList();

This request requires an additional condition to keep only the last status. I need to keep the ProcessedOn greater time for each ActionID.

Take a look at this question: http://stackoverflow.com/questions/1575316/linq-top-value-form-each-group — twaggs, Nov 15 '11 at 16:00

score 4 · Answer 1 · answered Nov 15 '11 at 16:02

4

You may try to group by ActionID and than fetch only last item

MyTable
   .Where(t => t.ProcessedOn >= start)
   .Where(t => t.ProcessedOn <= stop)
   .GroupBy(t => t.ActionID)
   .Select(t => t.OrderBy(t => t.ProcessedOn).Last())
   .ToList();

answered Nov 15 '11 at 16:02

VMykyt

1,589
12
17

+1: One minor problem - need inner `OrderBy(t)` lambda to have different variable name because `t` is already used in outer `Select(t)`. – mellamokb Nov 15 '11 at 16:05
@mellamokb : uups. This is my fault. I wrote this in answer section without any checking :( Thank You – VMykyt Nov 15 '11 at 16:22

score 3 · Answer 2 · answered Nov 15 '11 at 16:00

You want to make use of the GroupBy operator:

MyTable
    .Where(t => t.ProcessedOn >= start && t.ProcessedOn <= stop)
    .GroupBy(t => t.ActionID)
    .Select(g => new {
        Action = g.Key,
        LastStatus =
            g.Where(a => a.ProcessedOn == g.Max(a2 => a2.ProcessedOn) )
                .Single().Status
    })
    .ToList();

Query to retrieve the latest status of each action

2 Answers2