CODEWITHSUNDEEP
c#entity-frameworklinq
Taian
Taian

Reputation: 319

Group, sum and subtract with conditions

Having the list of objects bellow:

public class Example
{
    public string Local { get; set; }
    public string Type { get; set; }
    public int Amount { get; set; }
}

I would like to group the list by Local and, in the same field (Total), sum the Amount where the Type is "Input" and subtract the Amount where the Type is "Output".

Example:

enter image description here

Based on the example above, I want the following results:

enter image description here

I've tried the following code, but it's not working. It's returning NULL on Total.

var Balances = Examples
.GroupBy(y => y.Local)
.Select(y => new BalanceDTO
{
    Local = y.FirstOrDefault().Local
    Total = y.Where(z => z.Type == "Input").Sum(z => z.Amount) - y.Where(z => z.Type == "Output").Sum(z => z.Amount)
}).ToList()

What should I do?

Upvotes: 1

Views: 843

Answers (2)

simon-pearson
simon-pearson

Reputation: 1970

I've added a computed property to your Example class:

public class Example
{
    public string Local { get; set; }
    public string Type { get; set; }
    public int Amount { get; set; }
    public int NetAmount
    {
        get
        {
            return Type == "Input" ? Amount : -1 * Amount;
        }
    }
}

Then in your LINQ group them on the Local field as you did, and then I've used the LINQ Aggregate method to sum all of these 'net totals' in each group:

var Balances = Examples
    .GroupBy(y => y.Local)
    .Select(y => new
    {
        Local = y.Key,
        Total = y.Sum(y => y.NetAmount)
    }).ToList();

Upvotes: 3

lzydrmr
lzydrmr

Reputation: 909

Here is an alternative solution:

 var Balances = Examples.GroupBy(
                y => y.Local, 
                y => (y.Type, y.Amount), 
                (key,elements) => new { 
                    Local = key, 
                    Total = elements.Sum(el => el.Type == "Input" ? el.Amount : -el.Amount) 
                });

Upvotes: 3

Related Questions