Databases
StackOverflow2013
Postsdbo
POOuter Join based on DateTime field in LINQ, missing months
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOuter Join based on DateTime field in LINQ, missing months
    text
    copied!<p>My question is how to perform a left outer join, but to include missing values based on a datetime field, in linqToEF/SQL. I'm trying to cut down a huge problem to ask my specific question.</p> <p>I have 3 tables. Picture them below.</p> <ul> <li>timelogs | Id, StartDateTime, AccountNumber, ContractNumber</li> <li>contracts | Id, ContractNumber, AccountNumber, Value, StartDateTime, EndDateTime</li> <li>accounts | Id, AccountNumber, AccountName, Email, FirstName, LastName</li> </ul> <p>I'm performing a group by in linq like this:</p> <pre><code>group new {contracts, timelogs} by new { accounts.AccountNumber, accounts.AccountName, Year = timelogs.StartDateTime.Year, Month = timelogs.StartDateTime.Month } into g </code></pre> <p>The timelogs table only includes timelogs that have been submitted for a month with a particular account. If no timelogs have been submitted, it won't output any entry, as I would expect.</p> <p>See this example:</p> <p>Account "Blah" doesn't have any timelogs submitted for month 5. Hence, missing entry for for Blah in the sample output below.</p> <pre><code>AccountName | Account Number | Month | Year Bling 654321 5 2013 Bling 654321 6 2013 Blah 123456 6 2013 Bling 654321 7 2013 Blah 123456 7 2013 Bling 654321 8 2013 Blah 123456 8 2013 </code></pre> <p>I've tried to do this, but alas, this doesn't work due to the array being local.</p> <pre><code>join months in new int[] { 1,2,3,4,5,6,7,8,9,10,11,12 } on timelogs.StartDateTime.Month equals months join years in new int[] { 2008,2009,2010,2011,2012,2013,2014,2015,2016 } on timelogs.StartDateTime.Year equals months </code></pre> <p>How do I perform a join in linq to SQL / entity framework, based missing months/years from a DateTime field? I want to include Blah, for month 5, even if it does not have any timelogs.</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