Pick line with the highest value of birth in every shop-country section

Question

have been trying for several hours to resolve the problem but i cant sos
here is the tables im working with on pastebin

var result = E
                .Join(A, a => a.ID, aa => aa.ID,
                    (EE, AA) => new {AA.ID, AA.Birth, AA.Street, EE.Code, EE.Shop})
                .Join(D, d => d.Code, dd => dd.Code,
                    (AE, DD) => new {AE.ID, AE.Birth, AE.Street, AE.Shop, DD.Code, DD.Price})
                .Join(B, b => b.Code, bb => bb.Code,
                    (AED, BB) => new {AED.ID, AED.Birth, AED.Shop, AED.Price, BB.Country})
                .GroupBy(g => new
                {
                    g.ID, g.Birth, g.Shop, g.Country
                })
                .Select(s => new
                {
                    s.Key.ID, s.Key.Birth, 
                    ShopCont = s.Key.Shop + "-" + s.Key.Country, 
                    Total = s.Sum(ss => ss.Price)
                });

and this is what result looks like

{ ID = 1, Birth = 1992, ShopCont = Gucci-Nigeria, Total = 64 }
{ ID = 2, Birth = 2001, ShopCont = Gucci-Russia, Total = 41 }
{ ID = 3, Birth = 1998, ShopCont = Gucci-Russia, Total = 123 } // this should be removed
{ ID = 3, Birth = 1998, ShopCont = Dior-Russia, Total = 32 } 
{ ID = 4, Birth = 2003, ShopCont = Dior-USA, Total = 23 }
{ ID = 1, Birth = 1992, ShopCont = Adidas-USA, Total = 1290 }
{ ID = 1, Birth = 1992, ShopCont = Adidas-Germany, Total = 321 }
{ ID = 5, Birth = 2005, ShopCont = Dixi-Germany, Total = 4 }
{ ID = 5, Birth = 2005, ShopCont = Dixi-France, Total = 1890 }
{ ID = 4, Birth = 2003, ShopCont = Dixi-France, Total = 1695 } // this should be removed

and i want to see this

{ ID = 1, Birth = 1992, ShopCont = Gucci-Nigeria, Total = 64 }
{ ID = 2, Birth = 2001, ShopCont = Gucci-Russia, Total = 41 }
{ ID = 3, Birth = 1998, ShopCont = Dior-Russia, Total = 32 }
{ ID = 4, Birth = 2003, ShopCont = Dior-USA, Total = 23 }
{ ID = 1, Birth = 1992, ShopCont = Adidas-USA, Total = 1290 }
{ ID = 1, Birth = 1992, ShopCont = Adidas-Germany, Total = 321 }
{ ID = 5, Birth = 2005, ShopCont = Dixi-Germany, Total = 4 }
{ ID = 5, Birth = 2005, ShopCont = Dixi-France, Total = 1890 }

Why do you think those lines should be removed? They are not duplicates. They are different years. — jdweng
yes you're right but the purpose is to pick line with the highest value of birth in every shop-country section. it is the exercise from the book — invis
Do a OrderByDescending(x => x.Total) then a GroupBy(x => x.ShopCont) and finally select first from each group .Select(x => x.First()) — jdweng

Dmitry Bychenko Dmitry Bychenko · Accepted Answer · 2021-04-26T15:50:45

You can try adding GroupBy + Select (SelectMany) at the very end of the query:

var result = E
   ...
  .Select(s => new {
     s.Key.ID, 
     s.Key.Birth, 
     ShopCont = s.Key.Shop + "-" + s.Key.Country, 
     Total    = s.Sum(ss => ss.Price)
   })
  .GroupBy(item => item.ShopCont) // we group by shop
  .Select(g => g                  // in each shop
     .OrderByDescending(item => item.Birth) // we take the latest
     .First());                             // item only

here we group by Birth, take the only latest one.

Edit: If we can have duplicate Birth we can group by them (I'd rather implement my own extensions method, but when solving problems from a book it's usually not allowed):

  ... 
  .Select(s => new {
          s.Key.ID,
          s.Key.Birth, // Let's declare it explicitly
          ShopCont = s.Key.Shop + "-" + s.Key.Country,
          Total = s.Sum(ss => ss.Price)
        })
  .GroupBy(item => item.ShopCont) // we group by shop
  .SelectMany(outer => outer
    .GroupBy(item => item.Birth)
    .OrderByDescending(inner => inner.Key)
    .First());

Now we group by Birth (we want a group, since it can be more than just one single record to return); with OrderByDescending followed by First take the right group which we finally flatten with a help of SelectMany

Pick line with the highest value of birth in every shop-country section

1 Answers