title: Left Outer Calendar Table Join with LINQ-to-SQL
body: <p>How can I write the following <code>LEFT OUTER JOIN</code> SQL query against my <code>Calendar</code> and <code>Sales</code> tables for the purpose of grouping summed sales by day, week or month in LINQ so that it can be materialised by LINQ-to-SQL?</p>
  
  <pre><code>SELECT c.CalendarDate, c.FirstDayOfWeek, c.FirstDayOfMonth,
         ISNULL(s.Total, 0) as Total
  FROM Calendar as c
  LEFT OUTER JOIN Sales as s
   on s.SaleDate &gt;= c.CalendarDateTime and
      s.SaleDate &lt; c.NextDayDateTime
  WHERE s.SaleDate BETWEEN @since and @until
  </code></pre>
  
  <p>I managed to get an <em>inner join</em> working in LINQ, but I need an outer join to retrieve days with zero sales. Here is the code I use for an inner join:</p>
  
  <pre><code>var sales = from s in db.Sales
              from c in db.Calendars
              where
                  s.SaleDate &gt;= c.CalendarDate &amp;&amp; s.SaleDate &lt; c.NextDayDateTime
                  &amp;&amp; s.SaleDate &gt;= sinceDate &amp;&amp; s.SaleDate &lt; dateEnd
              select new
              {
                  c.CalendarDate,
                  c.FirstDateOfWeek,
                  c.FirstDateOfMonth,
                  s.Total
              };
  </code></pre>
  
  <p>I can then switch on a date interval and group sales as follows:</p>
  
  <p>Daily:</p>
  
  <pre><code>var groupedSales = sales.GroupBy(x =&gt; x.CalendarDate);
  </code></pre>
  
  <p>Weekly:</p>
  
  <pre><code>var groupedSales = sales.GroupBy(x =&gt; x.FirstDateOfWeek);
  </code></pre>
  
  <p>Monthly:</p>
  
  <pre><code>var groupedSales = sales.GroupBy(x =&gt; x.FirstDateOfMonth);
  </code></pre>
  
  <p>Finally:</p>
  
  <pre><code>var salesReport = from g in groupedSales
                    orderby g.Key
                    select new {
                        Date = g.Key,
                        Total = g.Sum(x =&gt; x.Total)
                    };
  </code></pre>
  
  <p>Alternatively, it could also work to inject zero sale records into my report after retrieving sales for non-zero days only.</p>