title: Left Outer Join in Entity Framework (Linq). Member materializing to Null
body: <p><strong>Problem:</strong></p>
  
  <p>Could I perform the left join and verify the <code>null</code>ed element without 
  terminating the DB link? If yes, how do I do it?</p>
  
  <p><strong>Trial Code</strong></p>
  
  <pre><code>var transactions1 = (from tran in ctx.transactions
      group tran.Amount by new { tran.UserID, tran.LeaveID } into leave
      select new { UserID = leave.Key.UserID, LeaveID = leave.Key.LeaveID, Balance = leave.Sum() });
  
  var transactions2 = (from tran in ctx.transactions
      where tran.Type == type &amp;&amp; tran.FiscalYear == fiscal
      group tran.Amount by new { tran.UserID, tran.LeaveID } into leave
      select new { UserID = leave.Key.UserID, LeaveID = leave.Key.LeaveID, Rollout = leave.Sum() });
  
  var computed = (from balance in transactions1
                 join join_rollout in transactions2 on new { balance.UserID, balance.LeaveID } equals new { join_rollout.UserID, join_rollout.LeaveID } into rolls
                 from rollout in rolls.DefaultIfEmpty()
                 select new
                 {
                     UserID = balance.UserID,
                     LeaveID = balance.LeaveID,
                     computed = rollout.Rollout
                 }).ToList();
  </code></pre>
  
  <p><strong>Goal</strong>:</p>
  
  <p>I am trying to left join two tables using linq. As one could guess, some values of the second table could result in <code>null</code>. If I use a ternary operator to verify <code>null</code>ed values the application throws the following exception</p>
  
  <blockquote>
    <p>The argument to <strong>DbIsNullExpression</strong> must refer to a primitive, enumeration or reference type.</p>
  </blockquote>
  
  <p>Find the stack trace at <a href="http://pastebin.com/KwUrnWN1" rel="nofollow noreferrer">PastBin</a></p>
  
  <p>If I remove the ternary verification, the application throws the following exception</p>
  
  <blockquote>
    <p>The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.</p>
  </blockquote>
  
  <p>Find the stack trace at <a href="http://pastebin.com/40kq4r9n" rel="nofollow noreferrer">PasteBin</a></p>
  
  <p><strong>Working solution</strong>:</p>
  
  <p>I am able to avoid these exceptions by terminating the DB link (using <code>.ToList()</code> as suggested by <a href="https://stackoverflow.com/a/21188777/1799502">slawek</a>) before the final join (that is, <code>transactions1.ToList() and transactions2.ToList()</code>. However I wish to avoid using <code>.ToList()</code> because I need to perform another join with a DB table which cannot be done on a List.</p>
  
  <p><strong>My Experiments Since the SO post</strong></p>
  
  <ul>
  <li>I've tried using <code>UseDatabaseNullSemantics</code> which I did not expect to resolve it but atleast aid in a workaround, but the result is <strong><code>Failed</code></strong></li>
  </ul>