Convert SQL to Linq in ASP Net Core

Question

I learning ASP NET CORE and i have to use LINQ and a have to try very much it more easy with simple query
but with complex query it's so hard

for example i have a sql query

select Products.ProductId,Products.ProductName,OrderDetails.UnitPrice, sum(OrderDetails.Quantity) as TotalQuantity from Products, OrderDetails, Orders where Products.ProductId = OrderDetails.ProductId and Orders.OrderId=OrderDetails.OrderId and Orders.OrderDate between '1/2/2019' and '12/12/2019' group by Products.ProductId,Products.ProductName,OrderDetails.UnitPrice

how can i query like this in linq (I tried 2 days but.....!) thank for your help!

Brad · Accepted Answer · 2019-05-08 12:56:50Z

2

I don't have a database to test against, but try this:

from o in Orders
join d in OrderDetails on o.OrderId equals d.OrderId
join p in Products on d.ProductId equals p.ProductId
where o.OrderDate > Convert.ToDateTime("1/2/2019")
   && o.OrderDate < Convert.ToDateTime("12/12/2019")
group d.Quantity by new {p.ProductId, p.ProductName, d.UnitPrice} into g
select new 
{
    ProductId = g.Key.ProductId,
    ProductName = g.Key.ProductName,
    UnitPrice = g.Key.UnitPrice,
    TotalQuantity = g.Sum()
}

To sum multiple columns adjust the group to just d, adjust the existing sum to add d => d.Quantity in the parenthesis and add the new sum line like this:

from o in Orders
join d in OrderDetails on o.OrderId equals d.OrderId
join p in Products on d.ProductId equals p.ProductId
where o.OrderDate > Convert.ToDateTime("1/2/2019")
   && o.OrderDate < Convert.ToDateTime("12/12/2019")
group d by new {p.ProductId, p.ProductName, d.UnitPrice} into g
select new 
{
    ProductId = g.Key.ProductId,
    ProductName = g.Key.ProductName,
    UnitPrice = g.Key.UnitPrice,
    TotalQuantity = g.Sum(d => d.Quantity),
    TotalUnitPrice = g.Sum(d => d.UnitPrice)
}

edited May 8, 2019 at 12:56

answered May 6, 2019 at 12:18

Brad

624 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hùngkk Over a year ago

Thank bro it very useful. you lock like a god, thank you very much

Hùngkk Over a year ago

what should i do if i want to sum UnitPrice it's solution: group { d.Quantity,d.UnitPrice } by ... ,isn't it?

Collectives™ on Stack Overflow

Convert SQL to Linq in ASP Net Core

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related