coco coco - 5 months ago 19
SQL Question

Changing a SQL query to a linq query - syntax

I'm having problem coding my linq query.

This is my SQL query:

select
price, (cast(sum(Quantity) as decimal(7,2)))
from
OrderDetails
where
ItemID = 1000
group by
price
order by
price


This is my linq query:

var result = from od in db.OrderDetails
where od.ItemID == 1000
orderby od.Price
group by price
select od.price, (cast(sum(od.Quantity) as decimal(7, 2)));


This linq query seems to be incorrect. What is the right syntax?

Answer

This should work: (You need to move the order part to be after the grouping)

var q = (from o in context.OrderDetails
            where o.ItemID == 1000
            group o by o.price into grp
            select new
            {
                Price = grp.Key,
                Quantity = grp.Sum(x => x.Quantity)
            }).OrderBy(a => a.Price);