Help with LINQ to SQL group by query
The LINQ group by syntax is confusing me. In TSQL I can select multiple columns and only group by one of them. With LINQ it's making me group by all of the columns that I want to work with.
How can I convert this TSQL to LINQ?
SELECT MAX(Item.itemID) AS Expr1, MAX(Item.title) AS Expr2,
SUM(OrderDetail.quantity) AS Qty, MAX([Order].dateCreated) AS Expr3
FROM Payment INNER JOIN
[Order] ON Payment.ID = [Order].orderID INNER JOIN
OrderDetail ON [Order].orderID = OrderDetail.orderID INNER JOIN
Item ON OrderDetail.itemID = Item.itemID
WHERE ([Order].dateCreated >= '4 / 15 / 2011 12:00:00 AM')
AND ([Order].dateCreated <= '4/15/2011 11:59:00 PM')
GROUP BY Item.itemID
ORDER BY Expr2
var q = from p in db.Payments
join o in db.Orders on p.ID equals o.paymentID
join od in db.OrderDetails on o.orderID equals od.orderID
join i in db.Items on od.itemID equals i.itemID into j1
from j2 in j1
where o.dateCreated >= new DateTime(2011, 4, 15)
group j2 by j2.itemID into g
开发者_开发知识库 select new
{
g.Key
};
var query =
from p in db.Payments
join o in db.Orders
on p.ID equals o.orderID
join od in db.OrderDetails
on o.orderID equals od.orderID
join i in db.Items
on od.itemID equals i.itemID
where o.OrderDate.Date == new DateTime(2011, 4, 15)
group new { i.itemID, i.title, od.quantity, o.dateCreated }
by i.itemID into g
let Expr1 = g.Max(x => x.itemID)
let Expr2 = g.Max(x => x.title)
let Qty = g.Sum(x => x.quantity)
let Expr3 = g.Max(x => x.dateCreated)
orderby Expr2
select new { Expr1, Expr2, Qty, Expr3 };
精彩评论