CODEWITHSUNDEEP
c#asp.net-mvc
Mohammed Imadh
Mohammed Imadh

Reputation: 9

Get Sum and Id of record through Linq-to-SQL and display on view

I need to convert this SQL statement to linq to sql in MVC code first.

SELECT SUM(OrderUnits), ProductID
FROM OrderProducts AS op
jOIN Orders AS o ON o.ID = op.OrderID
WHERE o.OrderStatus = 1
GROUP BY op.ProductID;

I have tried to no avail. I have method in my controller call MostSoldItems and I need to get this result and display it on my view.

This is what I tried: 

var query =
    (from p in dbcontext.OrderProducts
    let totalQuantity = 
        (from op in dbcontext.Products
         join o in dbcontext.Orders on p.OrderID equals o.ID
         where o.OrderStatus == true
         select p.OrderUnits).Sum()
     orderby totalQuantity descending
     select p);

I'm not getting anything. and I do not know how I would go about displaying this on a view.

Upvotes: 0

Views: 354

Answers (2)

Mohammed Imadh
Mohammed Imadh

Reputation: 9

So I seem to have figured a work around of perhaps even a straight answer. Thanks for @candide 's answer I started working on it for a while and have figured it out! The below code works fine for my problem. Thanks @Moho also for his contribution.

var query = from p in dbcontext.OrderProducts
                    join pd in dbcontext.Orders on p.OrderID equals pd.ID
                    group p by p.ProductID into pdg
                    select new MostSoldModel
                    {
                        ID = pdg.Key,
                        Total = pdg.Sum(x => x.OrderUnits)
                    };

Upvotes: 1

Moho
Moho

Reputation: 16498

If you're looking for the sum total of OrderProduct.OrderUnits filtered by the related Order.OrderStatus == true and grouped by product:

dbContext.OrderProducts
    .Where( op => op.Order.OrderStatus )
    .GroupBy( op => op.Product )
    .Select( g => new
    {
        Product = g.Key,
        TotalQuantity = g.Sum( op => op.OrderUnits ),
    } );

Upvotes: 0

Related Questions