CODEWITHSUNDEEP
c#linqgroup-by
BG100
BG100

Reputation: 4531

How do I order the elements in a group by linq query, and pick the first?

I have a set of data that contains a type, a date, and a value.

I want to group by the type, and for each set of values in each group I want to pick the one with the newest date.

Here is some code that works and gives the correct result, but I want to do it all in one linq query rather than in the iteration. Any ideas how I can achieve the same result as this with purely a linq query...?

var mydata = new List<Item> {
    new Item { Type = "A", Date = DateTime.Parse("2016/08/11"), Value = 1 },
    new Item { Type = "A", Date = DateTime.Parse("2016/08/12"), Value = 2 },
    new Item { Type = "B", Date = DateTime.Parse("2016/08/20"), Value = 3 },
    new Item { Type = "A", Date = DateTime.Parse("2016/08/09"), Value = 4 },
    new Item { Type = "A", Date = DateTime.Parse("2016/08/08"), Value = 5 },
    new Item { Type = "C", Date = DateTime.Parse("2016/08/17"), Value = 6 },
    new Item { Type = "B", Date = DateTime.Parse("2016/08/30"), Value = 7 },
    new Item { Type = "B", Date = DateTime.Parse("2016/08/18"), Value = 8 },
};

var data = mydata.GroupBy(_ => _.Type);

foreach (var thing in data) {

    #region 
            
    // How can I remove this section and make it part of the group by query above... ?          
    var subset = thing.OrderByDescending(_ => _.Date);
    var top = subset.First();
    
    #endregion
    
    Console.WriteLine($"{thing.Key} {top.Date.ToString("yyyy-MM-dd")} {top.Value}");
}

Where Item is defined as:

public class Item {     
    public string Type {get;set;}
    public DateTime Date {get;set;}
    public int Value {get;set;}     
}

Expected output:

A 2016-08-12 2
B 2016-08-30 7
C 2016-08-17 6

Upvotes: 6

Views: 1743

Answers (4)

Gilad Green
Gilad Green

Reputation: 37299

Use select to get the FirstOrDefault (or First - because of the grouping you won't get a null) ordered descending:

var data = mydata.GroupBy(item => item.Type)
                 .Select(group => group.OrderByDescending(x => x.Date)
                 .FirstOrDefault())
                 .ToList();

Or SelectMany together with Take(1)

var data = mydata.GroupBy(item => item.Type)
                 .SelectMany(group => group.OrderByDescending(x => x.Date)
                 .Take(1))
                 .ToList();

Upvotes: 10

Patrick Huizinga
Patrick Huizinga

Reputation: 1340

var data = mydata.GroupBy(
    item => item.Type,
    (type, items) => items.OrderByDescending(item => item.Date).First());

foreach (var item in data)
{
    Console.WriteLine($"{item.Type} {item.Date.ToString("yyyy-MM-dd")} {item.Value}");
}

Upvotes: 2

Marco Scabbiolo
Marco Scabbiolo

Reputation: 7449

Group by type, for each group, order it by date descending and pick the first (newest).

mydata.GroupBy(i => i.Type).Select(g => g.OrderByDescending(i => i.Date).First());

Upvotes: 1

Ren&#233; Vogt
Ren&#233; Vogt

Reputation: 43876

You can Select the first element of the ordered groups:

var topItems = mydata.GroupBy(item => item.Type)
                     .Select(group => group.OrderByDescending(item => item.Date).First())
                     .ToList();

topItems is now a List<Item> containing only the top items per Type.

You may also retrieve it as Dictionary<string,Item> mapping the Type strings to the top Item for that Type:

var topItems = mydata.GroupBy(item => item.Type)
                     .ToDictionary(group => group.Key,
                              group => group.OrderByDescending(item => item.Date).First());

Upvotes: 2

Related Questions