CODEWITHSUNDEEP
c#linqgroup-bysum
Wojtek P
Wojtek P

Reputation: 3

Linq with sum and group from two tables

Hi I have some two tables,

Product:

ProductID | IssueDate | Amount

1           2017-06-01  1000
2           2017-06-01  1000
3           2017-06-02  500

and Credit:

ProductID | Amount

1           500
1           500
2           1000
3           500

if I use query like this from SQL Server:

SELECT p.IssueDate, SUM(p.Amount), SUM(p.Total) 
FROM (SELECT p.IssueDate, SUM(p.Amount) AS Amount, 
(SELECT TOP 1 SUM(c.Amount) FROM Credit c WHERE p.Id = c.ProductId) AS Total from Product p
GROUP BY p.IssueDate, p.Id) p
GROUP BY p.IssueDate

I get this result:

IssueDate | Amount | Total

2017-06-01  2000     2000
2017-06-02  500      500

In C# Linq I can get this data by two queries like this: 

var data = from p in Products.Collection
           select new
           {
              Product = p,
              Credits = Credit.Collection.Where(c => c.ProductID == p.ID).Sum(c => c.Amount)
           };

var result = from d in data
           group d by new
           {
               IssueDate = d.Product.IssueDate
           } into gr
           select new
           {
               IssueDate = gr.Key.IssueDate,
               Credits = gr.Sum(s => s.Credits),
               Total = gr.Sum(s => s.Product.Amount)
           };

var test = result.ToList();

Does anyone know a better (simpler) solution to get this result? Maybe in one query?

Upvotes: 0

Views: 819

Answers (1)

Pablo notPicasso
Pablo notPicasso

Reputation: 3161

How about:

Products.Collection.GroupBy(x => x.IssueDate)
            .Select(x => new
            {
                IssueDate = x.Key,
                Amount = x.Sum(p => p.Amount),
                Total = Credit.Collection.Where(c => x.Any(p => p.ProductID == c.ProductID)).Sum(c => c.Amount)
            }).ToList();

Upvotes: 1

Related Questions