Adding missing dates to list between maximum and minimum date in LIST using LINQ

Question

I'm trying to populate my list with missing dates where they have no sales...

I have defined a class like this:

   public class GroupedItem
    {
        public DateTime _Date { get; set; }
        public int Sales { get; set; }
    }

And now the list is populated like this:

var LineGraph = _groupedItems
                      .GroupBy(l => l._Date.Date)
                      .Select(cl => new GroupedItem
                      {
                           _Date = cl.Key,
                           Sales = cl.Sum(c=>c.Sales)
                      })
                      .OrderBy(x => x._Date)
                      .Where(t => t._Date <= DateTime.Now &&
                      t._Date >= DateTime.Now.Subtract(TimeSpan.FromDays(date_range)))
                      .ToList();

And the output that I get with this is like following:

11th December 6 sales
13th December 8 sales
18th December 12 sales
19th December 25 sales

This is rather okay, but I'd like to add the dates that are missing in between the first and last date so that I can have an output like this:

11th December 6 sales
12th December 0 sales
13th December 8 sales
14th December 0 sales 
15th December 0 sales
16th December 0 sales
17th December 0 sales
18th December 12 sales
21st December 25 sales

How can I achieve this with LINQ ?

My brain is having a hard time not wanting to just have a list of the dates in the date range that you left join on. — EJC, Dec 22 '16 at 15:29
Really just left join? How can I do that in LINQ, I've never did it before.. — User987, Dec 22 '16 at 15:31
I'm working on a solution using LinqPad currently, hold tight. It'll either be a left join or a union. — EJC, Dec 22 '16 at 15:38

score 5 · Accepted Answer · edited May 23 '17 at 10:30

5

This Post has a way to generate a list of dates based on a range. I think we're going to have to union a list of dates to your groupedquery since it looks like to me your query results don't already contain rows for dates with 0 sales.

var LineGraph = _groupedItems.Union(Enumerable.Range(1, date_range)
          .Select(offset => new GroupedItem { _Date = DateTime.Now.AddDays(-(date_range)).AddDays(offset), Sales = 0} ))
                      .GroupBy(l => l._Date.Date)
                      .Select(cl => new GroupedItem
                      {
                           _Date = cl.Key,
                           Sales = cl.Sum(c=>c.Sales)
                      })
                      .Where(t => t._Date <= DateTime.Now &&
                      t._Date >= DateTime.Now.Subtract(TimeSpan.FromDays(date_range)))
                      .OrderBy(x => x._Date)
                      .ToList();

Corrected to include today and order by after the select.

edited May 23 '17 at 10:30

Community

1
1

answered Dec 22 '16 at 15:45

EJC

2,062
5
21
33

It works in terms of adding missing day perfectly... Yeah this is the issue I haven't figured out yet, maybe it doesn't includes the first or last date given by the range? – User987 Dec 22 '16 at 15:49
1

Did you want to include today? – EJC Dec 22 '16 at 15:52
Yes I'd like to – User987 Dec 22 '16 at 15:53
P.S. I just tested the whole method on 30 days range, it shuffles the dates quite weirdly xD – User987 Dec 22 '16 at 15:56
It went from 1.12 to 22.12 , then it suddenly started from 22.11 for some reason lol – User987 Dec 22 '16 at 15:57
1

I moved the order by after the where which should help with the ordering. I've also corrected the query to include today. – EJC Dec 22 '16 at 16:00
Yeah thx a lot, btw. the dates are still in order like: 22.12, then it suddenly shifts to 23.11, 24.11 ,25.11,26.11,27.11,28.11,29.11,30.11 and thats where it ends – User987 Dec 22 '16 at 16:03
1

Data List Issue: It doesn't contain all of the dates like you want? Ordering: Hmm, is there another order by somwhere after this? When I `.Dump();` it in LinqPad it's in the right order. – EJC Dec 22 '16 at 16:08
Is your date range entering November month ? Mine date range is 22th of December to 23rd of November, they are aligned from 1-22 December correctly, and then after the 22.12 comes 23.11 (maybe we aren't checking of the month is bigger or smaller than the current one ? ) P.S. This is the only order by – User987 Dec 22 '16 at 16:12
1

I've figured out that the problem isn't in the upgrade u made, but actually on the view itself where i convert the date format – User987 Dec 22 '16 at 16:24
That's what I was getting at when I was asking about sorting after the initial query. Glad you found it. – EJC Dec 22 '16 at 16:44
1

Yeah ur upgrade works excellent and I've just accepted ur answer. Happy coding and happy new year mate :) – User987 Dec 22 '16 at 16:47
Thanks! Same to you! – EJC Dec 22 '16 at 16:47

Arturo Menchaca · Answer 2 · 2016-12-22T16:13:42.337

You can generate the list of dates and make a left join with lineGraph to generate the entire list:

var minDate = lineGraph.Min(g => g.Date);
var maxDate = lineGraph.Max(g => g.Date);
var range = GetDateRange(minDate, maxDate);

var result = from date in range
             from item in lineGraph.Where(g => g.Date.Date == date)
                                   .DefaultIfEmpty()
             select new GroupedItem
             {
                 Date = date,
                 Sales = item?.Sales ?? 0
             };

Use this method to generate the date range:

public static IEnumerable<DateTime> GetDateRange(DateTime startDate, DateTime endDate)
{
    var date = startDate.Date;

    while (date <= endDate.Date)
    {
        yield return date;
        date = date.AddDays(1);
    }
}

Alternatively, you can do something like this:

var min = _groupedItems.Min(g => g.Date);
var max = _groupedItems.Max(g => g.Date);
var range = GetDateRange(min, max).Select(d => new GroupedItem { Date = d, Sales = 0 });

And make your query against range.Concat(_groupedItems) instead of _groupedItems.

would this work if the date range goes 1 month into past ? Like for example: 22th December to 23th November ? — User987, Dec 22 '16 at 16:16
@User987: Using either the `left join` or `contact` solution, in both cases the range of dates goes from the earliest date to the latest date that appears in `_groupedItems` regardless of `range_date` value. — Arturo Menchaca, Dec 22 '16 at 16:23

Adding missing dates to list between maximum and minimum date in LIST using LINQ

2 Answers2