locked
Filling in the missing dates with a Linq query RRS feed

  • Question

  • User854162288 posted

    Hi All,

    I am running a Linq query to populate a bar chart (chart.js) where i group by month and year and count the entries for that particular period. However I have hit a snag. where there are zero entries for particular months and this throws my data out of sync. Example below where at index 5 the month skips over a month as there were no entries that particular period 

    1. 0: {year: 2019, month: 3}
    2. 1: {year: 2019, month: 4}
    3. 2: {year: 2019, month: 5}
    4. 3: {year: 2019, month: 6}
    5. 4: {year: 2019, month: 7}
    6. 5: {year: 2019, month: 8}
    7. 6: {year: 2019, month: 10}

    My Linq query is below, which isnt accounting for this currently 

    var veterans = _db.Records
                 .Where(j => j.Requestor == "Veterans" && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) >= 0 && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) <= 24)
                 .GroupBy(g => new { g.Request_Date.Value.Year, g.Request_Date.Value.Month }).OrderBy(d => d.Key.Year).ThenBy(d => d.Key.Month)
                 .Select(group => new
                     {
                     Dates = group.Key,
                     Count = group.Count()
                     });
    
                var veteransCount = veterans.Select(n => n.Count).ToArray();

    I guess my question is, how can I show the missing dates even when there were no entries 

    Friday, March 26, 2021 1:34 PM

Answers

  • User1686398519 posted

    Hi Jrow20, 

    how can I show the missing dates even when there were no entries 

    According to your needs, I modified your linq query statement, you can refer to the following code:

    var filterdata = _db.Records
           .Where(j => j.Requestor == "Veterans" && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) >= 0 && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) <= 24)
           .ToList();
    var yearAndMonthRange = filterdata.Select(m => m.Request_Date.Year).Distinct().OrderBy(t => t)
           .Select(m=>Enumerable.Range(1, 12).Select(t=>new { Year = m, Month =t }))
           .ToList();
    var result = yearAndMonthRange.Select(m=>m
           .GroupJoin(filterdata, key => key, f => new { Year = f.Request_Date.Year, Month = f.Request_Date.Month }, (key, f) => new { Dates = key, Count = f.Count() })
           .OrderBy(d => d.Dates.Year).ThenBy(d => d.Dates.Month).ToList())
           .ToList();

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 29, 2021 6:49 AM

All replies

  • Friday, March 26, 2021 2:31 PM
  • User1686398519 posted

    Hi Jrow20, 

    how can I show the missing dates even when there were no entries 

    According to your needs, I modified your linq query statement, you can refer to the following code:

    var filterdata = _db.Records
           .Where(j => j.Requestor == "Veterans" && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) >= 0 && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) <= 24)
           .ToList();
    var yearAndMonthRange = filterdata.Select(m => m.Request_Date.Year).Distinct().OrderBy(t => t)
           .Select(m=>Enumerable.Range(1, 12).Select(t=>new { Year = m, Month =t }))
           .ToList();
    var result = yearAndMonthRange.Select(m=>m
           .GroupJoin(filterdata, key => key, f => new { Year = f.Request_Date.Year, Month = f.Request_Date.Month }, (key, f) => new { Dates = key, Count = f.Count() })
           .OrderBy(d => d.Dates.Year).ThenBy(d => d.Dates.Month).ToList())
           .ToList();

    Here is the result. 

    Best Regards,

    YihuiSun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 29, 2021 6:49 AM
  • User854162288 posted

    Hey YihuiSun,

    Thankyou so much, this worked a charm you have been a great help :)

    Monday, March 29, 2021 4:18 PM