EF Core Group By translation support on conditional sum

Question

I was really excited to hear that EF Core 2.1 will be supporting group by translations. I downloaded the preview and started testing it out but found that I am still not getting group by translations in a number of places.

In the code snippet below, the query for TotalFlagCases will prevent the group by translation from working. Is there anyway that I can rewrite this so that I can have group by support? Or perhaps another approach that I can take?

There are a lot of rows in this table and I don't want .NET to have to load all of these rows. I use row level data as well, but only about 15 records at a time.

var headerTask = (from c in cases
    group c by 1
    into g
    select new CaseHeader
    {
        TotalCases = g.Count(),
        // ... A number of other aggregates
        TotalFlagCases = g.Where(a => a.Flag).Sum(b => 1),
    })
.DefaultIfEmpty(new CaseHeader()).FirstAsync();

Answer 1

There is a way to do a conditional sum in this version of EF Core. Provided code is not going to be translated into desired SQL with GROUP BY but maybe some future version will support it this way. For now you can try something like this:

var headerTask = cases
    .Select(c => new
    {
        c.Flag,
        c.YourKey,
        //other properties
    })
    .GroupBy(c => c.YourKey, (k, g) => new CaseHeader
    {
        TotalCases = g.Count(),
        // ... A number of other aggregates
        TotalFlagCases = g.Sum(b => a.Flag ? 1 : 0)
    });

When you project your entity into an anonymous type and then group it and use conditional operator in an aggregate function it will be translated into SQL with GROUP BY and aggregates like:

SELECT COUNT(*) AS [TotalCases], SUM(CASE
    WHEN [c].[Flag] = 1
    THEN 1 ELSE 0
END) AS [TotalFlagCases]
FROM [Cases] AS [c]
GROUP BY [c].[YourKey]

When you do not project it to an anonymous type so when there is the above Select function missing it will not be translated into SQL with GROUP BY. It looks like the query translator for this prerelease does not support it or it's a bug.