CODEWITHSUNDEEP
c#linqcharts
Serg
Serg

Reputation: 23

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));

Upvotes: 1

Views: 268

Answers (1)

Blue Eyed Behemoth
Blue Eyed Behemoth

Reputation: 3872

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();

Upvotes: 2

Related Questions