LINQ: Group by month and year within a datetime field

Question

I have a table with a datetime field. I want to retrieve a result set grouped by the month/year combination and the number of records that appear within that month/year. How can this be done in LINQ?

The closest I've been able to figure out is in TSQL:

select substring(mo,charindex(mo,'/'),50) from (
select mo=convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created)) 
 ,qty=count(convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created)))
from posts 
group by convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created))
) a
order by substring(mo,charindex(mo,'/')+1,50)

But I wouldn't say that works...

Jeremy · Accepted Answer · 2010-10-12T16:42:10.643

86

var grouped = from p in posts
     group p by new { month = p.Create.Month,year= p.Create.Year } into d
     select new { dt = string.Format("{0}/{1}",d.Key.month,d.Key.year), count = d.Count() };

Here's the list of DateTime functions available in LINQ. For this to work you'll also need to understand multi-column grouping

ordered descending

var grouped = (from p in posts 
  group p by new { month = p.Create.Month,year= p.Create.Year } into d 
  select new { dt = string.Format("{0}/{1}",d.Key.month,d.Key.year), count = d.Count()}).OrderByDescending (g => g.dt);

edited Oct 12 '10 at 16:42

answered Apr 18 '09 at 04:58

Jeremy

6,580
2
25
33

i want to sort this by months like first current month at top and previous month next a nd so on?? please help – Praveen Prasad Oct 11 '10 at 16:52
you can do that by adding an orderbydescending – Jeremy Oct 12 '10 at 16:39

score 29 · Answer 2 · answered Apr 03 '15 at 01:50

This is for those who are trying to accomplish the same but using lambda expressions.

Assuming that you already have a collection of entities and each entity has OrderDate as one of its properties.

yourCollection
// This will return the list with the most recent date first.
.OrderByDescending(x => x.OrderDate)
.GroupBy(x => new {x.OrderDate.Year, x.OrderDate.Month})
// Bonus: You can use this on a drop down
.Select(x => new SelectListItem
        {
           Value = string.Format("{0}|{1}", x.Key.Year, x.Key.Month),
           Text = string.Format("{0}/{1} (Count: {2})", x.Key.Year, x.Key.Month, x.Count())
        })
.ToList();

If you do not need the collection of SelectListItem then just replace the select with this one:

.Select(x => string.Format("{0}/{1} (Count: {2})", x.Key.Year, x.Key.Month, x.Count()))

score 7 · Answer 3 · answered Nov 18 '14 at 15:07

7

you could also do it this way

from o in yg
group o by o.OrderDate.ToString("MMM yyyy") into mg
select new { Month = mg.Key, Orders = mg }

Your result will be

{Jan 2014, 25} {Feb 2015, 15} etc...

answered Nov 18 '14 at 15:07

Rolwin Crasta

4,219
3
35
45

Works great for objects, but not for `IQueryable<>` that will get sent to a database. There you get `Method 'System.String ToString(System.String)' has no supported translation to SQL.` – Thymine Jun 10 '19 at 18:18

score 1 · Answer 4 · answered Apr 18 '09 at 04:41

1

This Site has an example that should fill your need.

This is the basic syntax:

from o in yg
group o by o.OrderDate.Month into mg
select new { Month = mg.Key, Orders = mg }

answered Apr 18 '09 at 04:41

Jimmie R. Houts

7,728
2
31
38

14

With a list containing orders from mulitple years, this will group orders from january 2010, 2011 and 2012 into the same group. – Moulde Mar 05 '12 at 13:40

score 0 · Answer 5 · edited Jul 11 '17 at 14:32

0

Here is a simple solution for grouping in DateTime.

List<leaveallview> lav = new List<leaveallview>();
lav = dbEntity.leaveallviews.Where(m =>m.created==alldate).ToList();
dynamic lav1 = lav.GroupBy(m=>m.created.Value.GetDateTimeFormats()).FirstOrDefault().ToList();

edited Jul 11 '17 at 14:32

SilverNak

3,283
4
28
44

answered Jul 11 '17 at 13:28

belal ahmad

59
6

LINQ: Group by month and year within a datetime field

5 Answers5

Linked

Related