Getting counts of LEFT JOINED entities via group by

Question

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.

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

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.

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 => g.grpM.Count()),
        ActorAcount = grp.Sum(g => g.grpA.Count())
    }
).ToList();

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 <>f__AnonymousType81(DirectorName = [d].DirectorName), new <>f__AnonymousType62(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 <>f__AnonymousType81(DirectorName = [d].DirectorName), new <>f__AnonymousType62(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.

Here are the models

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; }
}

Answer 1

I'm not sure why your Actors only have an int for Movie Id when an Actor usually has a list of Movies but I believe you need to include all three object types in your group and then select distinct movies and distinct actors to get the counts.

Example:

var directors = new[] { new { DirectorName = "Director A", DirectorID = 1 },
                        new { DirectorName = "Director B", DirectorID = 2 }};
var movies = new[] { new { MovieName = "Movie A", MovieID = 1, DirectorID = 1 },
                     new { MovieName = "Movie B", MovieID = 2, DirectorID = 2 }};
var actors = new[] { new { ActorName = "Actor A", ActorID = 1, MovieID = 1},
                     new { ActorName = "Actor B", ActorID = 2, MovieID = 1},
                     new { ActorName = "Actor C", ActorID = 3, MovieID = 1},
                     new { ActorName = "Actor D", ActorID = 4, MovieID = 2}};

var results = from d in directors
              from m in movies
                .Where(m => m.DirectorID == d.DirectorID)
              from a in actors
                .Where(a => a.MovieID == m.MovieID)
              where d.DirectorID == 1
              group new { d, m, a } by d.DirectorName into grp
              select new 
              { DirectorName = grp.Key,
                MovieCount = grp.Select(x => x.m).Distinct().Count(),
                ActorCount = grp.Select(x => x.a).Distinct().Count()
              };

Will yield

result = new [] { new { DirectorName = "Director A", MovieCount = 1, ActorCount = 3}};