Should I use OrderByDescending twice in LINQ?

Question

I have a products table like this:-

ProductID     ProductName     Price
   1            Milk           10
   2            Banana         20
   3            Apple          15
   1            Grapes         12
   2            Banana         25
   1             Milk          8

I want to find the products who have maximum price for each productId.

Sample Output:-

    ProductID     ProductName     Price
       1            Grapes          12
       2            Banana         25
       3            Apple          15

I have tried this query:-

List<Product> groups = products
    .GroupBy(p => p.ProductId)
    .Select(p => new Product
        {
            ProductId = p.Key,
            Name = p.OrderByDescending(o => o.Price).First().Name,
            Price = p.OrderByDescending(o => o.Price).First().Price,
        })
    .ToList();

This query is working fine, but my question is should i use OrderByDescending twice? I mean since i just want single item based on 1 property and suppose there are multiple other properties, so do i need to use same logic again and again?

Edit: Pardon me forgot to mention, Please Assume ProductName can be different, Please check updated tables.

Answer 1

No, you don't have to, you can just select First():

new Product
{
    ProductId = p.Key,
    Name = p.First().Name,
    Bar = p.First().Bar,
    Price = p.OrderByDescending(o => o.Price).First().Price,
}

This of course assumes all products with a given ProductId have the same Name and Bar.

If this isn't the case and you want to map all properties from the selected entity, create a code block in your Select():

.Select(p => 
{   
    var havingHighestPrice = p.OrderByDescending(o => o.Price).First()

    return new Product
    {
      ProductId = p.Key,
      Name = havingHighestPrice.Name,
      Bar = havingHighestPrice.Bar,
      Price = havingHighestPrice.Price,
    }
})

Answer 2

You can use Linq query syntax to store local variables:

var groups = from p in products.GroupBy(x => x.ProductId)
             let first = p.OrderByDescending(o => o.Price).First()
             select new Product
             {
                 ProductId = p.Key,
                 Name = first.Name,
                 Price = first.Price,
             };

What's important is that it's safe to use in Entity Framework queries.