LINQ Get First Rows from Each Query

user_6096175 0
Viewed 47

I have two sets of data and IDs, with multiple entries in the dataset for each ID. I want to get the latest entry for each ID. I can just do data.Where(item => idList.Contains(item.ID)); but that gets me every row, and I only want one per ID. Right now I'm considering just iterating and doing a separate query for each ID like data.Where(item => item.ID == currentID).OrderBy(item => item.time).First();, but that seems clunky. Is there a significantly more elegant way to do this in LINQ?

Given IDs [1, 2, 3, 4] and data

time | ID | result
-------------------
1230 | 1  | Succeed
1215 | 1  | Fail
1215 | 2  | Fail
1200 | 2  | Fail
1220 | 3  | Fail
1215 | 4  | Success
1200 | 5  | Fail

I want to get

time | ID | result
-------------------
1230 | 1  | Succeed
1215 | 2  | Fail
1220 | 3  | Fail
1215 | 4  | Success
linq c#
1 Answers

You can group by the id and get the element with the biggest time:

var ids = new[]{1,2,3,4};
var l = new List<(int time,int id,string result)>(){
    (1230, 1, "Succeed"),
    (1215, 1, "Fail"),
    (1215, 2, "Fail"),
    (1200, 2, "Fail"),
    (1220, 3, "Fail"),
    (1215, 4, "Success"),
    (1200, 5, "Fail")
};

var results = l.Where(x => ids.Contains(x.id))
    .GroupBy(
        x => x.id,
        (k,v) => v.OrderByDescending(x=>x.time).First()
        );

This would result in

1230 1 Succeed 
1215 2 Fail 
1220 3 Fail 
1215 4 Success
user_57623320
Related