CODEWITHSUNDEEP
linq
atreeon
atreeon

Reputation: 24087

Group by with maximum

I want to group by category, show it's name, then show the highest id that is related to it. Here's some data and the result that I want further down. Any ideas? I've been playing around with GroupJoin but can't seem to get it to work.

My Data

var stuff = new[] {
    new {id = 5, catId = 2},
    new {id = 56, catId = 2},
    new {id = 56, catId = 2},
    new {id = 8, catId = 1},
    new {id = 9, catId = 3}};

var categories = new[] {
    new {catId = 1, Name = "Water"},
    new {catId = 4, Name = "Wind"},
    new {catId = 2, Name = "Fire"}};

What I want my results to look like

Water - 8
Wind - null
Fire - 56

Upvotes: 2

Views: 52

Answers (2)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

categories
    .GroupJoin
    (
        stuff,
        c=>c.catId,
        s=>s.catId,
        (c,s)=>new 
        {
            c.Name,
            Max = s.Any() ? (int?)s.Max (m => m.id) : null
        } 
    );

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460048

It seems that you want a "LEFT OUTER JOIN" with LINQ:

var query = from cat in categories
            join s in stuff
            on cat.catId equals s.catId into gj
            from stuffJoin in gj.DefaultIfEmpty()
            group stuffJoin by new { cat.catId, cat.Name } into catGroup
            select new { 
                Category = catGroup.Key.Name,
                MaxID = catGroup.Max(s => s == null ? 0 : s.id) // stuff is null for Wind
            };

foreach (var x in query)
    Console.WriteLine("Category: {0} Max-ID: {1}", x.Category, x.MaxID);

Outputs:

Category: Water Max-ID: 8
Category: Wind  Max-ID: 0
Category: Fire  Max-ID: 56

Upvotes: 1

Related Questions