CODEWITHSUNDEEP
c#linqlambda
pheobas
pheobas

Reputation: 314

Linq query to group items and query from the top item in each group

So, I have a list that looks something like this. Its basically a state history for a bunch of items, with the most recent state representing the current state.

Record   Id      State       Date
=====    ===     =========  =======
1        A       Waiting     Jan 01
2        A       InProgress  Jan 02
3        A       Finished    Jan 03
4        B       Waiting     Jan 02
5        C       Waiting     Jan 01
6        C       InProgress  Jan 02
7        D       Waiting     Jan 01
8        D       InProgress  Jan 02

What I'm looking for is to be able to query the 'current' state for each item. For example, I'd like to say: "Give me all Ids are are 'InProgress'" and get Id D and Id C, but not Id A (because its latest state is 'Finished').

I know I've got to do some grouping and some ordering or Maxing, but I can't quite put it all together.

Upvotes: 3

Views: 3865

Answers (3)

Doctor Jones
Doctor Jones

Reputation: 21674

Here's some code to do what you want. It gets the latest state for each id and ignores finished records. I've provided a complete working example that you can run (and hopefully adapt to work for your real data).

//the example data provided by the OP
var data = new []
{
    new { Record = 1, Id = "A", State = "Waiting", Date = new DateTime(2013, 1, 1) },
    new { Record = 2, Id = "A", State = "InProgress", Date = new DateTime(2013, 1, 2) },
    new { Record = 3, Id = "A", State = "Finished", Date = new DateTime(2013, 1, 3) },        
    new { Record = 4, Id = "B", State = "Waiting", Date = new DateTime(2013, 1, 1) },        
    new { Record = 5, Id = "C", State = "Waiting", Date = new DateTime(2013, 1, 1) },
    new { Record = 6, Id = "C", State = "InProgress", Date = new DateTime(2013, 1, 2) },        
    new { Record = 7, Id = "D", State = "Waiting", Date = new DateTime(2013, 1, 1) },
    new { Record = 8, Id = "D", State = "InProgress", Date = new DateTime(2013, 1, 2) },
};

var query = from d in data
            //put the newest record first
            orderby d.Date descending
            //group by the id
            group d by d.Id into groupedById
            //get the latest record for each id
            let latest = groupedById.First()
            //filter out finished records
            where latest.State != "Finished"
            select latest;

And here's the output from LinqPad.

enter image description here

You'll notice that we have the latest state for each item, apart from "A" which has been ignored because it is finished.

Upvotes: 2

Ani
Ani

Reputation: 113462

If this is LINQ to Objects (and assuming records are arranged in the right order), you can do:

var latestById = records.GroupBy(record => record.Id)
                        .ToDictionary(group => group.Key, group => group.Last());

This is because GroupBy guarantees that "Elements in a grouping are yielded in the order they appear in source."

If you can't make guarantees about the record order, I would do:

var latestById = records.GroupBy(record => record.Id)
                        .Select(group => group.MaxBy(r => r.Date))
                        .ToDictionary(record => record.Id);

where MaxBy comes from moreLinq.

By the way, if this were LINQ to SQL, I would do:

var latestById = records.GroupBy(record => record.Id)
                        .Select(group => group.OrderByDescending(r => r.Date).First())
                        .AsEnumerable()
                        .ToDictionary(record => record.Id);

Upvotes: 1

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

myList.GroupBy(m => m.Id)
.Select(g => g.OrderByDescending(x => x.Date).First())
.Where(<your filter>);

Upvotes: 9

Related Questions