Databases
StackOverflow2013
Postsdbo
POHow do I use LINQ to query for items, but also include missing items?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I use LINQ to query for items, but also include missing items?
    text
    copied!<p>I'm trying to chart the number of registrations per day in our registration system. I have an Attendee table in sql server that has a smalldatetime field A_DT, which is the date and time the person registered.</p> <p>I started with this:</p> <pre><code>var dailyCountList = (from a in showDC.Attendee let justDate = new DateTime(a.A_DT.Year, a.A_DT.Month, a.A_DT.Day) group a by justDate into DateGroup orderby DateGroup.Key select new RegistrationCount { EventDateTime = DateGroup.Key, Count = DateGroup.Count() }).ToList(); </code></pre> <p>That works great, but it won't include the dates where there were no registrations, because there are no attendee records for those dates. I want every date to be included, and when there is no data for a given date, the count should just be zero.</p> <p>So this is my current working solution, but I KNOW THAT IT IS TERRIBLE. I added the following to the code above:</p> <pre><code>// Create a new list of data ranging from the beginning to the end of the first list, specifying 0 counts for missing data points (days with no registrations) var allDates = new List&lt;RegistrationCount&gt;(); for (DateTime date = (from dcl in dailyCountList select dcl).First().EventDateTime; date &lt;= (from dcl in dailyCountList select dcl).Last().EventDateTime; date = date.AddDays(1)) { DateTime thisDate = date; // lexical closure issue - see: http://www.managed-world.com/2008/06/13/LambdasKnowYourClosures.aspx allDates.Add(new RegistrationCount { EventDateTime = date, Count = (from dclInner in dailyCountList where dclInner.EventDateTime == thisDate select dclInner).DefaultIfEmpty(new RegistrationCount { EventDateTime = date, Count = 0 }).Single().Count }); } </code></pre> <p>So I created ANOTHER list, and loop through a sequence of dates I generate based on the first and last registrations in the query, and for each item in the sequence of dates, I QUERY the results of my first QUERY for the information regarding the given date, and supply a default if nothing comes back. So I end up doing a subquery here and I want to avoid this.</p> <p>Can anyone thing of an elegant solution? Or at least one that is less embarrassing?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload