I'm working on improving back-end performance with Entity Framework Core. This is a small and simple example to illustrate what I want to return:

var filteredMovies = _dataService
    .Repository
    .Where(movie => movie.Genre == 'Drama')
    .Where(...many other conditions, joins, etc...);

var counts1 = filteredMovies.Count(movie => movie.director == 'Spielberg');
var counts2 = filteredMovies.Count(movie => movie.director == 'Nolan');

return new Summary 
{
    SpielbergCount = counts1,
    NolanCount = counts2,
};

This will generate 2 SQL queries, that each go through all the WHERE, JOIN steps. What I've seen, in SQL, is that I could write it this way to only execute 1 query:

SELECT
    SUM(CASE WHEN Director = 'Spielberg' THEN 1 ELSE 0 END) SpielbergCount,
    SUM(CASE WHEN Director = 'Nolan' THEN 1 ELSE 0 END) NolanCount
FROM Movies
JOIN ....
WHERE ....

2 questions:

1. How can I translate this last query into EntityFramework, to prevent executing 2 different SQL queries?
2. Is it going to improve performance? (ie: This is a small example, but I have many queries to improve, some of them very big, with most of the query being the same, except for one condition, like the Director in this example) Or is it not actually improving anything?

Thank you

CodePudding user response：

In order to do multiple top level aggregates in a select you have to do a group by on a constant value.

var counts = _dataService
    .Repository
    .Where(movie => movie.Genre == 'Drama')
    .Where(...many other conditions, joins, etc...)
    .GroupBy(x => 1)
    .Select(grp => new Summary 
    {
        SpielbergCount = grp.Count(movie => movie.director == 'Spielberg'),
        NolanCount = grp.Count(movie => movie.director == 'Nolan'),
    });