How to Sum data by every month in LINQ?

Question

I can select the sum by year, which is easy.

Just like this.

var query = from t in ctx.SomeDataEntity
                            group t by t.DateAdded.Year into g
                            select new
                            {
                                Year = g.Year,
                                Total = g.Sum(t => t.SomeColumn1) +
                                g.Sum(t => t.SomeColumn2) +
                                g.Sum(t => t.SomeColumn3) +
                                g.Sum(t => t.SomeColumn4)
                            };

But, how to filter the data by every month? It is not easy as simply replacing t.DateAdded.Year to t.DateAdded.Month, cause t.DateAdded.Month is 1,2,3,...,12. I need the one is in format of 2014-01,2014-02,...,2014-12.

Answer 1

You can group by both Year and Month like this:

var query = from t in ctx.SomeDataEntity
                    group t by new 
                    { 
                        Year = t.DateAdded.Year, 
                        Month = t.DateAdded.Month 
                    } into g
                    select new
                    {
                        MonthAndYear = g.Key.Year + "-" + g.Key.Month,
                        Total = g.Sum(t => t.SomeColumn1) +
                        g.Sum(t => t.SomeColumn2) +
                        g.Sum(t => t.SomeColumn3) +
                        g.Sum(t => t.SomeColumn4)
                    };