Aggregate and break down data using linq

Question

I have a database table which looks like the following:

| ID | Label | Type | Amount | Category | OriginDate |
------------------------------------------------------
| 1  | Foo   | 1    | 100    | 8        | 2017-01-23 |
| 2  | Bar   | 2    | 250    | 1        | 2017-01-30 |`
| 3  | Foo   | 1    | 400    | 12       | 2017-02-15 |`

Basically what I am trying to achieve is this. I am trying to write a linq query where I can group each record within a month. Then sum up all the values of the column Amount, and store it along it's corresponding month group.

Sums will look like the following, given the example table above:

January = 350
February = 400

These associated sums will then be displayed in a bar chart.

I imagine that I will need to do some grouping, and further expand using some foreach-magic. But I am quite blank on how to achieve it at this point.

Any suggestion that could point me in the right direction will be much appreciated.

EDIT:

jdweng's linq query put me on the right track.

Though it's far from optimal, the below code is how I solved it. Any pointers in terms of improvement are most welcome.

public ExpensesByMonth ChartMonthlyExpenses()
{
       // Retreives all transactions from repository

       List<Transaction> allTransactions = _repository.SelectAllTransactions();

       // Filter transactions by transaction type (2 = expenses)

       var filteredTransactions = (from transactions in allTransactions
                                   where transactions.Type == 2
                                   select transactions);

      // Made some modifications to jdweng's query in order to adapt it to existing codebase

      var results = filteredTransactions.GroupBy(x => new { month = x.OriginDate.Month, year = x.OriginDate.Year }).Select(x => new {
                    month = x.Key.month,
                    year = x.Key.year,
                    amount = x.Select(y => y.Amount).Sum()
                }).ToList();

                // Instantiating a new object containing all month's expenses as properties

                ExpensesByMonth expenseObj = new ExpensesByMonth();

                // Looping through each result and assigning the amounts to a certain property, using a switch statement
                foreach(var result in results)
                {
                    switch (result.month)
                    {
                        case 1:
                            expenseObj.JanuaryExpenses = result.amount;
                            break;
                        case 2:
                            expenseObj.FebruaryExpenses = result.amount;
                            break;
                        case 3:
                            expenseObj.MarchExpenses = result.amount;
                            break;
                        case 4:
                            expenseObj.AprilExpenses = result.amount;
                            break;
                        case 5:
                            expenseObj.MayExpenses = result.amount;
                            break;
                        case 6:
                            expenseObj.JuneExpenses = result.amount;
                            break;
                        case 7:
                            expenseObj.JulyExpenses = result.amount;
                            break;
                        case 8:
                            expenseObj.AugustExpenses = result.amount;
                            break;
                        case 9:
                            expenseObj.SeptemberExpenses = result.amount;
                            break;
                        case 10:
                            expenseObj.OctoberExpenses = result.amount;
                            break;
                        case 11:
                            expenseObj.NovemberExpenses = result.amount;
                            break;
                        case 12:
                            expenseObj.DecemberExpenses = result.amount;
                            break;

                    }
                }
                return expenseObj;            
            }

`

Please add *problem description* with details and minimal code required to reproduce your problem — Sergey Berezovskiy, Jun 25 '17 at 18:42
You could start looking at this solution to group dates in SQL: https://stackoverflow.com/questions/8345282/sql-server-group-by-month — Alexandru-Dan Pop, Jun 25 '17 at 18:49
Something like this should do the trick: `Entity.GroupBy(e => e.OriginDate.Month).Select(g => g.Sum(e => e.Amount));` — Fruchtzwerg, Jun 25 '17 at 19:02

score 0 · Accepted Answer · answered Jun 25 '17 at 18:58

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("Label", typeof(string));
            dt.Columns.Add("Type", typeof(int));
            dt.Columns.Add("Amount", typeof(int));
            dt.Columns.Add("Category", typeof(int));
            dt.Columns.Add("OriginDate", typeof(DateTime));


            dt.Rows.Add(new object[] { 1,"Foo",1,100,8, DateTime.Parse("2017-01-23")});
            dt.Rows.Add(new object[] { 2,"Bar",2,250,1, DateTime.Parse("2017-01-30")});
            dt.Rows.Add(new object[] { 3,"Foo",1,400,12, DateTime.Parse("2017-02-15")});

            var results = dt.AsEnumerable().GroupBy(x => new { month = x.Field<DateTime>("OriginDate").Month, year = x.Field<DateTime>("OriginDate").Year }).Select(x => new {
                month = x.Key.month,
                year = x.Key.year,
                amount = x.Select(y => y.Field<int>("Amount")).Sum()
            }).ToList();

        }
    }
}

Now, OP just have to crtl+c and crtl+v ... awesome isn't it? — Rahul, Jun 25 '17 at 19:03
Thank you! Your linq query helped me on the right track here. I will edit my original post and show you the solution I went with. — Michael S., Jun 25 '17 at 20:30

score 0 · Answer 2 · answered Jun 25 '17 at 19:14

You will likely hold your data in some form of IEnumerable<T>. Then you can easily use GroupBy (or even ToDictionary) and group your data by OriginDate.Month. Finally you'll need Sum() at some point, or you can just write a loop over the records stored against each month.

However you need to be careful about other situations, for example: what if you have records from January 2016 and January 2017? Would you like to mix them together? Maybe you will need to change some conditions when you group your data.

Aggregate and break down data using linq

2 Answers2