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?

Interesting, take it ` DefaultIfEmpty()` won't get you anywhere? — Stokedout, Jul 01 '13 at 13:03

score 2 · Accepted Answer · edited May 23 '17 at 12:28

2

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.

edited May 23 '17 at 12:28

Community

1
1

answered Jul 01 '13 at 13:12

Timothy Shields

75,459
18
120
173

The problem with this is A) `events[e.Date.Day]` needs to use a full date, which is easily solvable, and B) casting the anonymous type when passing this to the repeater is an effort. Will look to tweak, thanks. – Grant Thomas Jul 01 '13 at 14:43
@GrantThomas What anonymous type are you referring to? (You don't mention this in the question.) – Timothy Shields Jul 01 '13 at 14:45
`.ToDictionary( day => new DateTime(date.Year, date.Month, day), day => new List());` creates anonymous types that can't be cast to `DictionaryEntry`. This again should be quickly solved (one of the most important things here), so I'll alter it. – Grant Thomas Jul 01 '13 at 14:47
@GrantThomas Huh? That call returns a `Dictionary>` - not an anonymous type. Or is it that the `Event` type I'm referring to is actually an anonymous type? – Timothy Shields Jul 01 '13 at 14:48
It's that the entries are `KeyValuePair` types and not `DictionaryEntry` types. I'll use a strong type for the inner value either way, and can cast to `KeyValuePair` for the entries. – Grant Thomas Jul 01 '13 at 14:55
@GrantThomas Oh. Seems like a minor detail. Just do `events.Select(pair => ...)`. – Timothy Shields Jul 01 '13 at 14:58
Okay, still not part of the query but that's not the be all and end all, mostly that it works effectively for now, which I managed at least minimise a little bit. So, thanks. – Grant Thomas Jul 01 '13 at 16:33

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

1 Answers1