Serg Serg - 2 months ago 11
C# Question

How to display each month, even if no data for this month

The query works fine, except it only pulls the value where a month has more then zero incident, for example if January had no value, then it doesn't show January. I would like to show all months.

var result = tIncidentReportings
.AsEnumerable()
.GroupBy(c => c.Date.ToString("MMM"))
.Select(g => new { Month = g.Key, Count = g.Count() })
.OrderBy(x => DateTime.ParseExact((x.Month).ToString(), "MMM", CultureInfo.InvariantCulture));

Answer

The issue is you are going to have missing months for whatever months don't have any reports. You'll have to check to see which months don't have any and add them manually.

var result = tIncidentReportings
    .AsEnumerable()
    .GroupBy(c => c.Date.ToString("MMM"))
    .Select(g => new { Month = g.Key, Count = g.Count() })
    .OrderBy(x => DateTime.ParseExact((x.Month).ToString(), "MMM", CultureInfo.InvariantCulture));
var months = 
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthGenitiveNames.Select(s => s.Substring(0,3)).ToList();
months.foreach(m => {
    if(!results.Select(r => r.Month).Contains(m)){
        results.Add(new {Month = m, Count = 0};
});

Something like that

Or a rewrite of:

var months = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.MonthGenitiveNames.Select(s => s.Substring(0,3)).ToList();
var reports = months.Select(m => 
    new { 
        Month = m, 
        Count = tIncidentReportings.AsEnumerable().Where(i => i.Date.ToString("MMM") == m).Count()
    }
).OrderBy(x => DateTime.ParseExact((x.Month).ToString(), "MMM", CultureInfo.InvariantCulture)).ToList();
Comments