bhishan bhishan - 6 months ago 12x
SQL Question

Converting SQL to LINQ using Group By and Max

Is it even possible to write the following query using LINQ? I've been fighting all day, :(

select * from ProductGroup pg
Inner join (
select max(DateShipped) as ShipDate
, ProductId
from Lot lt
group by ProductId) lte
on pg.ProductId = lte.ProductId



I tested this on LinqPad using LinqToSQL.

var data = from lt in  Lots            
           group lt by lt.ProductId into grp
           join pg in ProductGroups on grp.Key equals pg.ProductId
           select new { pg.ProductId,pg.ProductName,ShipDate =grp.Max (g => g.ShipDate)};


and it produced this SQL (almost like your sql , just order of tables is switched)

SELECT [t2].[ProductId], [t2].[ProductName], [t1].[value] AS [ShipDate]
    SELECT MAX([t0].[ShipDate]) AS [value], [t0].[ProductId]
    FROM [Lot] AS [t0]
    GROUP BY [t0].[ProductId]
    ) AS [t1]
INNER JOIN [ProductGroup] AS [t2] ON [t1].[ProductId] = [t2].[ProductId]


This is one way to force a correlated sub-query which produces same result as you want but without a join/group by

var data = from pg in ProductGroups
           let sd = Lots.Where (l => l.ProductId == pg.ProductId ).Max(l => l.ShipDate )
           select new { pg.ProductId,pg.ProductName,ShipDate =sd};


and it generated this SQL

SELECT [t0].[ProductId], [t0].[ProductName], (
    SELECT MAX([t1].[ShipDate])
    FROM [Lot] AS [t1]
    WHERE [t1].[ProductId] = [t0].[ProductId]
    ) AS [sd]
FROM [ProductGroup] AS [t0]

I hope it will help you