Join Table Group By Summary On Linq-CodePudding

Hi I try to convert the SQL query to LINQ.

SELECT C.ID, SUM(S.AMOUNT*CS.PRICE) AS TOTALCATEGORYSUMMARY 
FROM CATEGORY C 
INNER JOIN PRODUCT P ON P.CATEGORYID=C.ID 
INNER JOIN PSTOCK S ON S.PRODUCTID=P.ID 
INNER JOIN PCOST CS ON CS.PRODUCTID=P.ID 
GROUP BY C.ID

I try as below:

var qry = from c in categories
          join p in products on c.Id equals p.CategoryId
          join s in stoks on p.Id equals s.ProductId
          join t in costs on p.Id equals t.ProductId
          group new {c} by new { c.Name,s.Stock,t.Amount } into ct
          select (new { ct.Key.Name, AllCost=ct.Key.Amount * ct.Key.Stock });

How can I do this?

CodePudding user response：

From your SQL query, your LINQ statement should be:

var qry = from c in categories
          join p in products on c.Id equals p.CategoryId
          join s in stoks on p.Id equals s.ProductId
          join t in costs on p.Id equals t.ProductId
          group new { c.Id, s.Amount, t.Price } by c.Id into ct
          select new { Id = ct.Key.Id, AllCost = ct.Sum(x => x.Amount * x.Price) };

References

Group by single property

Enumerable.Sum method