title: How can I return an amount sum grouped by date and ID using Linq
body: <p>I want return the data in an object of type <code>IQueryable&lt;Money&gt;</code>, 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: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>I am not sure if this is possible to do from the code or it's better to create a view for it.</p>
  
  <p>What I have tried so far: </p>
  
  <pre><code>public List&lt;Money&gt; GetData(MoneyFilter filter)
  {
      var data = _moneyRepository.GetFiltered(filter);
  
      List&lt;MoneyDto&gt; list = new List&lt;MoneyDto&gt;();
  
      foreach (var item in data.GroupBy(x =&gt; new {x.Date,x.ContactId}))
      {
          list.Add(new MoneyInOutFlowDto
          {
              Sell = item.Sum(x =&gt; x.Amount&lt;0?x.Amount:0),
              Buy = item.Sum(x =&gt; x.Amount&gt;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;}        
  }
  </code></pre>