CODEWITHSUNDEEP
sqllinq
Hùngkk
Hùngkk

Reputation: 79

Convert SQL to Linq in ASP Net Core

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!

Upvotes: 0

Views: 179

Answers (1)

Brad
Brad

Reputation: 62

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)
}

Upvotes: 2

Related Questions