title: Getting counts of LEFT JOINED entities via group by
body: <p>Consider the following query that must return a director along with counts of movies and actors for each movie.  How would one write this in LINQ using EF CORE 2.2?  I need the LINQ to generate SQL that actually uses the GROUP BY with the SQL aggregates as supported in EF CORE 2.1 and forward.</p>
  
  <pre><code>SELECT DirectorName, COUNT(m.MovieID), COUNT(a.ActorID)
  FROM Directors d
  LEFT OUTER JOIN Movies AS m ON m.DirectorID = d.DirectorID
  LEFT OUTER JOIN Actors AS a ON a.MovieID = m.MovieID
  WHERE d.DirectorID = 1
  GROUP BY DirectorName
  </code></pre>
  
  <p>The sample LINQ query was built based on an answer and does give me results, but the query only has one of the LEFT joins and no group by.  Output indicates that DefaultIfEmpty(), GroupBy, and Count could not be translated and will be evaluated locally.</p>
  
  <pre><code>var results =
  (
      from d in _moviesContext.Directors
      join m in _moviesContext.Movies on d.DirectorId equals m.DirectorId 
          into grpM from movies in grpM.DefaultIfEmpty()
      join a in _moviesContext.Actors on movies.MovieId equals a.MovieId
          into grpA from actors in grpA.DefaultIfEmpty()
      where d.DirectorId == 1
      group new { d, grpM, grpA } by new
      {
          d.DirectorName
      } into grp
      select new
      {
          DirectoryName = grp.Key.DirectorName,
          MovieCount = grp.Sum(g =&gt; g.grpM.Count()),
          ActorAcount = grp.Sum(g =&gt; g.grpA.Count())
      }
  ).ToList();
  </code></pre>
  
  <blockquote>
    <p>The LINQ expression
    'DefaultIfEmpty()' could not be translated and will be evaluated
    locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ
    expression 'DefaultIfEmpty()' could not be translated and will be
    evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The
    LINQ expression 'from Movies movies in {[grpM] => DefaultIfEmpty()}'
    could not be translated and will be evaluated locally.
    Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression
    'GroupBy(new &lt;>f__AnonymousType8<code>1(DirectorName = [d].DirectorName),
    new &lt;&gt;f__AnonymousType6</code>2(d = [d], grpM = [grpM]))' could not be
    translated and will be evaluated locally.
    Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression
    'DefaultIfEmpty()' could not be translated and will be evaluated
    locally. Microsoft.EntityFrameworkCore.Query:Warning: The LINQ
    expression 'DefaultIfEmpty()' could not be translated and will be
    evaluated locally. Microsoft.EntityFrameworkCore.Query:Warning: The
    LINQ expression 'from Movies movies in {[grpM] => DefaultIfEmpty()}'
    could not be translated and will be evaluated locally.
    Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression
    'GroupBy(new &lt;>f__AnonymousType8<code>1(DirectorName = [d].DirectorName),
    new &lt;&gt;f__AnonymousType6</code>2(d = [d], grpM = [grpM]))' could not be
    translated and will be evaluated locally.
    Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression
    'Count()' could not be translated and will be evaluated locally.
    Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression
    'Sum()' could not be translated and will be evaluated locally.</p>
  </blockquote>
  
  <p>Here are the models</p>
  
  <pre><code>public partial class Directors
  {
      public int DirectorId { get; set; }
      public string DirectorName { get; set; }
  }
  
  public partial class Movies
  {
      public int MovieId { get; set; }
      public string MovieName { get; set; }
      public int? DirectorId { get; set; }
  }
  
  public partial class Actors
  {
      public int ActorId { get; set; }
      public string ActorName { get; set; }
      public int? MovieId { get; set; }
  }
  </code></pre>