Multiple SUM issue with EF SQL

Question

I'm trying to use Linq to generate a SQL command like this

return await query.GroupBy(x => new
                                {
                                    Id = x.xId,
                                    Date = x.Date
                                })
                  .Select(x => new OutputClass(x.Key.Id,                     
                                               x.Key.Date,
                                               x.Sum(g => g.Tot.Value),
                                               x.Sum(g => g.Table1.Table2.Ind ? g.Tot.Value : 0),
                                               x.Sum(g => g.Table1.Table2.War ? g.Tot.Value : 0),
                                               x.Sum(g => g.Table1.Table2.Non ? g.Tot.Value : 0)
                                               )
                          )
                  .ToListAsync();

The expected result is something like

SELECT 
    SUM(...),
    SUM(CASE WHEN ...),
    SUM(CASE WHEN ...),
    SUM(CASE WHEN ...)
FROM

however the translation to SQL is a join between multiple SUM that repeat the same query.

Of course performance are a disaster. I have tried to put a Select before the GroupBy, but it didn't solve.

Any idea in order to execute everything in database (not in memory), do not use pure SQL and avoid create a view?

Answer 1

Well it wasn't an easy one. But it does look like EF issue. And it seems to be appearing from EF version 7.

From the source

Pseudocode:

var qAgg = from x in db.B
           group x by new { x.Process.Element.Customer.Kind }
           into g
           select new
           {
               Kind = g.Key.Kind,
               Region1Rule1 = g.Count(x => x.Region1 && x.Process.Rule == Rule.Rule1),
               Region2Rule1 = g.Count(x => x.Region2 && x.Process.Rule == Rule.Rule1),
               Region3Rule1 = g.Count(x => x.Region3 && x.Process.Rule == Rule.Rule1),
               Region4Rule1 = g.Count(x => x.Region4 && x.Process.Rule == Rule.Rule1),
               Region1Rule2 = g.Count(x => x.Region1 && x.Process.Rule == Rule.Rule2),
               Region2Rule2 = g.Count(x => x.Region2 && x.Process.Rule == Rule.Rule2),
               Region3Rule2 = g.Count(x => x.Region3 && x.Process.Rule == Rule.Rule2),
               Region4Rule2 = g.Count(x => x.Region4 && x.Process.Rule == Rule.Rule2),               
           };

On EF Core 6 this used to be translated to something like

SELECT 
    e.kind AS "Kind",
    COUNT(CASE WHEN b.region1 AND (z1.rule = 1) THEN 1 END)::INT AS "Region1Rule1", 
    COUNT(CASE WHEN b.region2 AND (z1.rule = 1) THEN 1 END)::INT AS "Region2Rule1", 
    COUNT(CASE WHEN b.region3 AND (z1.rule = 1) THEN 1 END)::INT AS "Region3Rule1",
    COUNT(CASE WHEN b.region4 AND (z1.rule = 1) THEN 1 END)::INT AS "Region4Rule1",
    COUNT(CASE WHEN b.region1 AND (z1.rule = 2) THEN 1 END)::INT AS "Region1Rule2", 
    COUNT(CASE WHEN b.region2 AND (z1.rule = 2) THEN 1 END)::INT AS "Region2Rule2", 
    COUNT(CASE WHEN b.region3 AND (z1.rule = 2) THEN 1 END)::INT AS "Region3Rule2",
    COUNT(CASE WHEN b.region4 AND (z1.rule = 2) THEN 1 END)::INT AS "Region4Rule2"
FROM b
INNER JOIN z /* ...*/
INNER JOIN e /* ...*/
INNER JOIN z1 /* ...*/
GROUP BY e.kind

On EF Core 7 every single COUNT is translated to a subquery, resulting in a huge and repetitive query that takes forever to complete.

So basically it looks to me that your expectation of how code works refers to EF Core version 6. Which is not true anymore. You can find out more in the issue I mentioned in source link.

A workaround for this issue is to use .Distinct on projection before grouping.

Original issue where workaround was suggested:. Quoted below

The idea is to expand and project all the needed navigations before you do group by. EF is smart enough to counter act this, but for that purpose the workaround introduces Distinct - it causes a pushdown and "locks" the shape of projection, so EF engine can't do anything but accept the shape. Distinct can change the results (if there are any duplicates), so to counteract that, we introduce a dummy guid, that always guarantees the uniqueness. You can drop the dummy guid if you are sure the results are unique. But you also need to assess if the distinct slowdown is bad or not.

edit: actually, projecting the owner key instead of generating guid should be better/faster in almost every case

So this worked:

var str = db.MyDatas
            .Select(m => new
            {
                Id = m.xId,
                Date = m.Date,
                TotValue = m.Tot != null ? m.Tot.Value : 0,
                IndValue = m.Table1 != null && m.Table1.Table2 != null && m.Table1.Table2.Ind ? m.Tot != null ? m.Tot.Value : 0 : 0,
                WarValue = m.Table1 != null && m.Table1.Table2 != null && m.Table1.Table2.War ? m.Tot != null ? m.Tot.Value : 0 : 0,
                NonValue = m.Table1 != null && m.Table1.Table2 != null && m.Table1.Table2.Non ? m.Tot != null ? m.Tot.Value : 0 : 0
            }).Distinct()
            .GroupBy(x => new
                                {
                                    Id = x.Id,
                                    Date = x.Date
                                })
                  .Select(g => new OutputClass(
                g.Key.Id,
                g.Key.Date,
                g.Sum(x => x.TotValue),
                g.Sum(x => x.IndValue),
                g.Sum(x => x.WarValue),
                g.Sum(x => x.NonValue)
            )).ToQueryString();