LINQ group by date - include empty days WITHOUT using join

Question

Using C#, NHibernate, NHibernate to LINQ. Using NHibernate to LINQ, I do not have the JOIN functionality. I can not use QueryOver either.

I have a LINQ query that counts the amount of Leads and Sales. This table only creates when a new lead or sale is done, so some days there is no row inserted. Doing the following query works

var query3 = query2.AsEnumerable()
           .Where(x => x.Created <= toDate.Value && x.Created >= fromDate.Value)
           .GroupBy(x => x.Created.Date)
           .Select(g => new ReferrerChart {
               Date = g.Key.Date,
               LeadsCount = g.Count(x => !x.IsSale),
               SalesCount = g.Count(x => x.IsSale)
           });

but some dates are not in there (the dates there were 0 leads and sales).

How can I include these dates and let them set LeadsCount = 0 and SalesCount = 0 WITHOUT using join?

EDIT:

Final result that works:

var selectedFromDate = fromDate.Value;
var selectedToDate = toDate.Value;

var selectedDates = Enumerable.Range(0, 1 + selectedToDate.Subtract(selectedFromDate).Days)
          .Select(offset => new ReferrerChart { 
                            Date = selectedFromDate.AddDays(offset),
                            LeadsCount = 0,
                            SalesCount = 0
                            })
          .ToList();

var query3 = Find(x => x.Source == "UserRef").AsEnumerable()
             .Where(x => x.Created <= toDate.Value && x.Created >= fromDate.Value)
             .GroupBy(x => x.Created.Date)
             .Select(g => new ReferrerChart {
                 Date = g.Key.Date,
                 LeadsCount = g.Count(x => !x.IsSale),
                 SalesCount = g.Count(x => x.IsSale)
             }).ToList();

var result = query3.Union(
                        selectedDates
                        .Where(e => !query3.Select(x => x.Date).Contains(e.Date)))
                        .OrderBy(x => x.Date);

score 6 · Accepted Answer · answered Jun 13 '13 at 13:47

You can Union() the data from your query with dummy items that you create in memory. For example:

var query4 = query3.ToList(); // Prevent multiple execution.

var startDate = DateTime.Today.AddDays(-99);

var emptyData = Enumerable.Range(1,100).Select (i => 
    new ReferrerChart
        {
           Date = startDate.AddDays(i),
           LeadsCount = 0,
           SalesCount = 0
        });

var result = query4 .Union(
             emptyData
                .Where(e => !query4.Select(x => x.Date).Contains(e.Date)))
             .OrderBy(x => x.Date);

Brilliant answer! Thanks Gert. – Thabiso Mofokeng Jul 22 '20 at 13:08 — Thabiso Mofokeng, Jul 22 '20 at 13:08

score 1 · Answer 2 · answered Jun 13 '13 at 12:48

There are two things I would change in your LINQ Query, I'd include the null dates

.Where(x => x.Created <= toDate.Value && x.Created >= fromDate.Value)

becomes something like

.Where(x => x.Created.Date == null || (x.Created <= toDate.Value && x.Created >= fromDate.Value))

My other pointer would be that your .GroupBy requires a x.Created.Date, use of IsNull or similar function to set the value here to a value regardless of whether the entry is Null

E.g. (x.Created == null ? "" : x.Created.ToString("yyyyMMdd"))

(I apologize but I'm not on my development PC at this moment in time so can't definitively state the correct code)

score 0 · Answer 3 · answered May 09 '17 at 17:52

There's a great way to do it by implementing an extension method for IEnumerable<IGrouping<TKey, TElement>> (the type that is returned from GroupBy). In my humble opinion it's the best approach for a number of reasons:

Doesn't recalculate the result set being filled (.ToList() also accomplishes that, but at the price of eager evaluation),
Keeps lazy evaluation (deferred execution) of GroupBy,
One-liners friendly (can be chained inside a fluent LINQ query),
Generic, reusable,
Elegant, easy to read.

Implementation

public static IEnumerable<IGrouping<TKey, TElement>> Fill<TKey, TElement>(this IEnumerable<IGrouping<TKey, TElement>> groups, IEnumerable<TKey> filling)
{
    List<TKey> keys = filling.ToList();

    foreach (var g in groups)
    {
        if(keys.Contains(g.Key))
            keys.Remove(g.Key);

        yield return g;
    }

    foreach(var k in keys)
        yield return new EmptyGrouping<TKey, TElement>(k);
}



class EmptyGrouping<TKey, TElement> : List<TElement>, IGrouping<TKey, TElement>
{
    public TKey Key { get; set; }

    public EmptyGrouping(TKey key)
    {
        this.Key = key;
    }
}

Sample usage

Random rand = new Random();

var results = Enumerable.Repeat(0, 5)                    // Give us five
    .Select(i => rand.Next(100))                         // Random numbers 0 - 99
    .GroupBy(r => r.Dump("Calculating group for:") / 10) // Group by tens (0, 10, 20, 30, 40...)
    .Fill(Enumerable.Range(0, 10))                       // Fill aby missing tens
    .OrderBy(g => g.Key);                                // Sort

@"Anything above = eager evaluations.
Anything below = lazy evaluations.".Dump();

results.Dump();

Sample output

(Five integers on top are printed from inside the query when it's being evaluated. As you can see, there was only one calculation pass).

LINQ group by date - include empty days WITHOUT using join

3 Answers3

Implementation

Sample usage

Sample output

Linked