title: GroupJoin, SelectMany, GroupBy and Sum
body: <p>I am trying to do a 'left outer join' in linq using GroupJoin and SelectMany, but then I also want to aggregate the result using GroupBy and Sum.</p>
  
  <p>But when I execute the code below, I get:</p>
  
  <blockquote>
    <p>System.NotSupportedException: 'The entity or complex type '...tableB' cannot be constructed in a LINQ to Entities query.'</p>
  </blockquote>
  
  <pre><code>Repo&lt;tableA&gt;().All()
      .Where(i =&gt;
          (i.Date &gt;= dateF &amp;&amp; i.Date &lt;= dateT)
          &amp;&amp;
          i.EndOfMonth
      )
      .GroupJoin(
          Repo&lt;tableB&gt;().All().Where(i =&gt; (i.fieldX = ...somevalue... )), 
          dt =&gt; dt.DayIndex, scd =&gt; scd.DayIndex, (dt, scd) =&gt;
              new {
                  dt = dt,
                  scd = scd,
              }
      )
      .SelectMany(
          jn =&gt; jn.scd.DefaultIfEmpty( new tableB { Count1 = 0, Count2 = 0 }), // runtime error here
          (dt,scd) =&gt; new { dt=dt.dt, scd = scd}
          )
      .GroupBy(i =&gt; i.dt)
      .Select(i =&gt; new CountListItem
      {
          Date = i.Key.Date,
          CountField1 = i.Sum(o =&gt; o.scd.Count1),
          CountField2 = i.Sum(p =&gt; p.scd.Count2)
       })
       .OrderBy(i =&gt; i.Date)
       .ToList()
  </code></pre>
  
  <p>When I just do <code>DefaultIfEmpty()</code> I get the error:</p>
  
  <blockquote>
    <p>System.InvalidOperationException: 'The cast to value type 'System.Int32' 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>I am assuming, I must admit, this is because Sum encounters null values. </p>
  
  <p>I tried <code>i.Sum(o =&gt; o.scd.Count1 ?? 0</code>, but then it says:</p>
  
  <blockquote>
    <p>Operator '??' cannot be applied to operands of type 'int' and 'int' [sic]</p>
  </blockquote>
  
  <p>I also tried <code>DefaultIfEmpty(new { Count1  = 0, Count2 =0})</code> but this gives me></p>
  
  <blockquote>
    <p>... type cannot be inferred.</p>
  </blockquote>
  
  <p>How can I get this to work?</p>