Need help for a more beautiful LINQ to SQL query

Question

Following is a T_SQL query for AdventureWorks database:

SELECT Name
FROM Production.Product
WHERE ListPrice >= ANY
(SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)

I try writing a LINQ query for this:

        var groupMaxPricesquery2 = from product in dc.Products
                                   group product by product.ProductSubcategoryID into productGroup
                                   select productGroup.Max(eachProductInGroup => eachProductInGroup.ListPrice);

        var query = from product in dc.Products
                    where groupMaxPricesquery2.Any(listPrice => listPrice <= product.ListPrice)
                    select product.Name;

How can I make it more beautiful (i.e. combining those queries together, or a more efficient approach)?

Thank you very much

Answer 1

Maybe I'm missing something with the grouping, but I don't see why it is necessary.

var maxListPrice = dc.Products.Max(p => p.ListPrice);
var query = dc.Products.Where(p => p.ListPrice >= maxListPrice).Select(n => n.Name);

Answer 2

Give either of these a try:

var query = from product in dc.Products
            let groupMaxPricesQuery = dc.Products.GroupBy(p => p.ProductSubcategoryID)
                                                 .Select(g => g.Max(item => item.ListPrice))
            where groupMaxPricesQuery.Any(listPrice => listPrice <= product.ListPrice)
            select product.Name;

// or
var query = dc.Products
              .Select(product => new {
                  Product = product,
                  GroupedMaxPrices = dc.Products.GroupBy(p => p.ProductSubcategoryID)
                                                .Select(g => g.Max(item => item.ListPrice))
            })
            .Where(item => item.GroupedMaxPrices.Any(listPrice => listPrice <= item.Product.ListPrice))
            .Select(item => item.Product.Name);