How to add empty groups to a Linq query result set?

Question

I have a very simple query which selects items from a table based on matching the month and then grouping by day. These groups are then used as a data source for a repeater which outputs the group elements as entries "per day".

The problem is that days that don't exist (i.e. there's no group for) will naturally not be displayed, so things for the 6th and 8th, when there's nothing for the 7th, will be seen directly next to each other (think of a calendar view). The question is, given my query below, how could I insert groups with no elements, even when there's no entry for that day?

IQueryable events = 
  Events
    .Where(i => i.Date.Month == date.Month)
    .GroupBy(i => i.Date.Day);

I can do this figuring out after the fact, but can I account for it to get the result set at once? Or can a previous tried & tested approach be recommended?

Answer 1

Create your result set like this:

var date = ...;
var events = Enumerable.Range(1, DateTime.DaysInMonth(date.Year, date.Month))
    .ToDictionary(
        day => new DateTime(date.Year, date.Month, day),
        day => new List<Event>());

Then insert into it like this:

var query = Events
    .Where(e => e.Date.Year == date.Year)
    .Where(e => e.Date.Month == date.Month);

foreach (var e in query)
    events[e.Date].Add(e);

---

If you really want to do this server-side as part of the query, you'll either need to (A) send the list of dates you're interested in as part of the query, or (B) use DBMS-specific functions to construct the set of days you're interested in server-side.

Specific to (B) and SQL, there are things like this: Get the first and last date of next month in MySQL

I personally would just do this client-side. The database query gets whatever data your implementation needs to extract from the database; the user gets whatever information your view needs. To query the database for the data you need to create your view, you don't actually need to know the list of dates in your month, so it really shouldn't be part of the query.