How can I return an amount sum grouped by date and ID using Linq

Question

I want return the data in an object of type IQueryable<Money>, and the money table in the database keeps a record of money transactions (buy/sell) for several customers each time a buy or sell occurs it saves the date and the amount and I get this data using a repository as follows:

ID                 Value        Action         Date 
1                  12             Buy          1/2/2018
1                  -5             Sell         1/2/2018 
1                  7              Buy          1/2/2018 
1                  3              Buy          1/2/2018

2                  35             Buy          1/2/2018
2                  -27            Sell         1/2/2018 
2                  20             Buy          1/2/2018 

4                  20              Buy         1/2/2018
4                  30              Buy         1/2/2018
4                  -50             Sell        1/2/2018 

1                  15              Buy         12/10/2017
1                  -23             Sell        12/10/2017 
1                  20              Buy         30/10/2017 
1                  3               Buy         30/10/2017

In my C# code I want to display the total buy and sell amounts per day for each customer (sum(buy) sum(sell) group by date and customer ID) using LINQ and EF, and display the value in a grid, as follows:

ID                Buy Value       Sell Value         Date 
1                  22                 -5              1/2/2018
1                  15                 -23             12/10/2017
1                  23                  -              30/10/2017
2                  55                 -27             1/2/2018 
4                  50                 -50             1/2/2018 

I am not sure if this is possible to do from the code or it's better to create a view for it.

What I have tried so far:

public List<Money> GetData(MoneyFilter filter)
{
    var data = _moneyRepository.GetFiltered(filter);

    List<MoneyDto> list = new List<MoneyDto>();

    foreach (var item in data.GroupBy(x => new {x.Date,x.ContactId}))
    {
        list.Add(new MoneyInOutFlowDto
        {
            Sell = item.Sum(x => x.Amount<0?x.Amount:0),
            Buy = item.Sum(x => x.Amount>0?x.Amount:0),
            Id = item.ContactId,
            Date = item.Date
        });
    }
    return list;
}

public class MoneyDto 
{
    public int ContactId {get;set;}
    public decimal Sell {get;set;}
    public decimal Buy {get;set;}
    public DateTime Date {get;set;}        
}

Answer 1

You started off well by grouping but the selection was not implemented correctly

var data = _moneyRepository.GetFiltered(filter);

List<MoneyDto> list = data.GroupBy(_ => new { _.Date, _.ContactId })
    .Select(g => new MoneyDto {
        ContactId = g.Key.ContactId,
        Date = g.Key.Date,
        Sell = g.Where(_ => _.Action == "Sell").Sum(_ => _.Amount),
        Buy = g.Where(_ => _.Action == "Buy").Sum(_ => _.Amount),
    }).ToList();

Answer 2

var data = _moneyRepository.GetFiltered(filter);

data.GroupBy(x => new { x.Action, x.Id }).Sum(x => x.Value).Select(x => new MoneyDto{
//Here assign the values
Id = .xId //and the other values
}).ToList();