CODEWITHSUNDEEP
c#linqjoingroup-bymin
Tom K
Tom K

Reputation: 145

How to get Min in C# Linq with join and group by?

I want to build a Linq query for below scenario:

Table 1

PriceId
ProductId
ProductPrice 
ProductHeight
ProductWeight
ProductType

Table 2

ProductId
ProductName

Task: Select the cheapest prices and names for products where:

The end expected result is a list of objects like this:

ProductId
ProductName
TheCheapestPrice(the cheapest ProductPrice)

I created a SQL query to get this info, however I have a problem with writing it in LINQ.

SQL query:

SELECT
    t1.ProductId,
    t2.ProductName,
    MIN(t1.ProductPrice) AS TheCheapestPrice
FROM
    Table1 t1
    INNER JOIN Table2 t2 ON t1.ProductId = t2.ProductId
WHERE
    t1.ProductHeight = 5
    AND
    t1.ProductWeight = 10
    AND
    t1.ProductType IN ('abc', 'xyz')
GROUP BY
    t1.ProductId,
    t2.ProductName

Can you please, help me to achieve the same result in LINQ?

So far I created such a LINQ, however I have a problem with getting minimum price:

from t1 in Table1
where
    productTypes.Contains(t1.ProductType)
    &&
    t1.ProductHeight == 5
    &&
    t1.ProductWeight == 10 
join t2 in Table2 on t1.ProductId equals t2.ProductId
group new { t1.ProductId, t2.ProductName }
by new { t1.ProductId, t2.ProductName } into topPrices
select new 
{
    ProductId = topPrices.Key.ProductId,
    ProductName = topPrices.Key.ProductName,
    TheCheapestPrice = ???
}

Upvotes: 1

Views: 655

Answers (1)

Canica
Canica

Reputation: 2738

You will need to adjust your group to return the t1 record since you want to access its ProductPrice property:

group t1 by new { t1.ProductId, t2.ProductName } into topPrices

Then all you need is to perform the Min on the topPrices group, like this:

TheCheapestPrice = topPrices.Min(t => t.ProductPrice)

Link to working fiddle.

HTH

Upvotes: 1

Related Questions