LINQ/Lambda query help for SQL Group By + MAX?

Question

I was wondering if anybody could help me with a lambda or LINQ query (preferrably lambda) based off the following SQL query. I have tried and got a little way through but no luck :(

SELECT TOP 1 MAX(cv.ProductID) as MaxProductID, MAX(ap.RegionAsLocationID) RegionID,
   COUNT(cv.ProductID) as ProductCount 
FROM CustomerVouchers cv
INNER JOIN Products p on p.id = cv.ProductID and p.status = 3
INNER JOIN APs ap on ap.id = p.apid
WHERE cv.Status = 1
GROUP BY cv.ProductID
ORDER BY ProductCount DESC

The SQL returns a result like this:

MaxProductID | RegionID | ProductCount
123 | 16862 | 3

I am after the "max result" columns where MAX() would be the customer vouchers record and it's relationships which has the most productID's found.

Thanks gang.

OakStreek OakStreek · Accepted Answer · 2011-10-21T14:34:34

Assuming ctx is your entity model, use the following.

 var result = from cv in ctx.CustomerVoucher
                         join p in ctx.Products on p.id equals cv.ProductID && p.status == 3
                         join ap in ctx.APs on ap.id equals prop.apid
                         where cv.status == 1
                         group cv by cv.ProductID into g
                         select new { MaxProductID = g.Max(cv => cv.ProductID), RegionID = g.max(ap => ap.RegionAsLocationID), ProductCount = g.Count(cv => cv.ProductID) };

LINQ/Lambda query help for SQL Group By + MAX?

1 Answers