efcore: Query: query with group by in subquery produces invalid SQL

Well, here is the point of view of someone earning money with making software:

I HAVE TO SHIP. You obviously do not, at least not something working, but I do have to ship.

Yes, this whole post will come out as a rant - but please understand it is arant build on months of frustration with a team that seems to not understand at all that their product is USED in commercial software and contineus to not care about fixing bugs, constantly delaing them repeatedly to the next version.

You talk about breaking things, for me EfCore 3.1 is in the same state ANY VERSION WAS: UNUSABLE. Seriously. Unusable except for the most simplistic cases and please do not use any feature - up before 3.1 global query filters broke the whole stack. NICE. There is nothing to break.

I have projects moved to .NET Core (now 3.1) and the only way we could ever use EfCore was to load the whole table into memory on every request, then filter in memory. EfCore was fundamentally broken since I started using it in 2.1. Now you tell me that this will stay so for the foreseeable future - what are you afraid of breaking in a product that is unusable? What is the logic there? Why not make a beta release so people can try it out, like - modern software development?

3.1 solved most of the issues that forced my workaround - mostly the total inability to use global query filters by fixing the bug in there that was overlooked then deemed not worthy fixing. It introduced a “one step forward, 5 steps back” approach by ripping out core functionality and STILL not generating valid SQL and then telling people to wait. Well, I release next week.

So, here is what I will do. I will rip out EfCore, the sperior product (except: it is not) and replace it with Ef non core - because THAT ONE WORKS. It may be architecturally inferior (but working), it is slower (except it is not), it has less support for non sql sources (which we do not use), it has less options for migrations (which are an antipattern for enterprise software), it has less features (but they actually WORK, not like EfCore features that are broken and should not even count) and it lacks global query filters (except there is a third party branch EntityFramework Clasic that does have them) and it has less linq features (which actually is wrong beacuse try doing a union or intersect in efcore and you realize that it only supports a VERY small subset of LINQ operations). Generally it is now - with 3 MAJOR releases of EfCore, STILL the superior product and one actually usable.

You REALLY need to get a point of view outside of the “we are super, why do people complain, lets delay critical fixes” and start thinking with the view of someone who has to ship. I have tried to wait as long as I could - time to move back and accept that the best thing which happened to EfCore now is the ability to dump it.

ANY bad sql bug is critial because you totally invalidate the use case for an ORM mapper if you are unable to actually use it.

In any sensible world, you would put out a beta branch with fixes and see whether people complain. You would actually fix our product and not tell people ALL OVER FOR EVERY RELEASE TO WAIT FOR THE NEXT ONE. I waited for fixes to EfCore 2.1, then was told in 2.2 to wait for 3.0 then was told ithe fixes where delayed for 3.1 then NOW I read that yeah, maybe in November we fix our crappy SQL generation, please delay your products AGAIN. EfCore turned from a rework of the overengineered but working EF product into the joke of the whole stack. Maybe it turns usable before it reaches version 10 - but I actually have to ship.

For anyone else who is in the same situation: in Dotnetcore 3.1 you CAN use Ef - non core - and that one actually works. If you prefer to have global query filters and a lot more functionality you can head over to https://entityframework-classic.net/ which is a branch that works in netstandard 2.0 and incorporates global query filters.

I personally - i have a test project now and for every major release that is released I will check whether it FINALLY is usable there, if not - no need for me to waste my time even opening bug reports.

This is probably not be the best place to discuss EF Core policy, but I want to answer to some points @NetTecture made that I consider valid :

• Releases are too few and far between. For a highly-used product like this (NuGet says 3.1.0 was downloaded over 1.3 million times), having to wait over a month for a critical fix https://github.com/dotnet/efcore/milestone/78 doesn’t cut it. I don’t expect a release every day, but every other week would be much more acceptable.
• Too many bugs get punted to oblivion. As someone who has been using .Net Core and EF Core every day since 1.0.0-rc1-update1 in 2016, and who opened a lot of issues here, more often than not we are told to wait until the next major release. This was especially a problem in the transition from 2.0 to 2.1 and 2.2 where some issues were stuck for months. If there is no workaround, you gotta re-engineer everything and wait 6 months.
• The policy is unclear at best. 3.1.0 is supposed to be LTS, yet many bug fixes are punted to 5.0.0, which will no doubt bring a whole lot of new bugs, and the cycle will restart again. This already happened with the 2.1 LTS branch where many issues were punted to 3.0, which defeats the purpose of having LTS.
• Rewriting rewriting rewriting. 1.0 was the “beta test”, that’s OK. Then, everything got rewritten for 2.0, and then again for 3.0, each time breaking stuff that had to be fixed after the fact. 5.0 will probably be the same.

All of that being said, for me, EF Core works. I’ve been using it continuously since 2016 and I’m very happy about it. Most of the time, it’s transparent to use and doesn’t get in the way. When it does, usually there’s a fix, and unless you get some edge case (which happened to me) you can get your code working fast.

I wouldn’t go back to .Net 4 to save my life. The EF and .Net Core team are doing a tremendous job and I hope that this will continue improving going forward 👍

Feel free to move this post to a more appropriate place if needed.

The issue here is the nested collection. You are not running group by query on products directly. You are running it on packages. i.e. for each package run Group by query. It cannot be translated to SQL GROUP BY directly and requires outer apply at best case. (Currently EF Core fails to lift it in single query and generates N+1 queries). Since you are grouping all products of given package into one, it is very easy to group by PackageId instead. Following query will generate same result and does server evaluation. The shape of result is slightly different. In your case, it will be List<List<'a>> where each inner list is going to contain only 1 entry. In following query the result is List<'a> which is easy to convert to desired shape on client side.

var query = db.Set<Product>()
    .Select(y => new
    {
        PackageId = EF.Property<int?>(y, "PackageId"),
        Price = y.SalePrice * y.Coefficient / 2.4,
        Potential = y.SalePrice * y.Valorization / 4.8
    })
    .GroupBy(p => p.PackageId)
    .Select(y => new
    {
        Result = y.Sum(z => z.Price),
        Next = y.Sum(z => z.Potential)
    }).ToList();

Generated SQL

      SELECT SUM(([y].[SalePrice] * [y].[Coefficient]) / 2.3999999999999999E0) AS [Result], SUM(([y].[SalePrice] * [y].[Valorization]) / 4.7999999999999998E0) AS [Next]
      FROM [Product] AS [y]
      GROUP BY [y].[PackageId]

@SebastiaanPolfliet Correct.

@smitpatel wrt case 1. after making the change the scenario is still broken - we now produce group by clause, but still there are extra columns in the subquery projection:

SELECT [c].[CustomerID], [t].[c], [t].[OrderID]
FROM [Customers] AS [c]
LEFT JOIN (
    SELECT SUM([o].[OrderID]) AS [c], [o].[OrderID], [o].[CustomerID]
    FROM [Orders] AS [o]
    GROUP BY [o].[CustomerID]
) AS [t] ON [c].[CustomerID] = [t].[CustomerID]
ORDER BY [c].[CustomerID], [t].[OrderID]

I have verified that all queries mentioned in this thread now work correctly

Basic Sum in GroupBy projection is tested by GroupBy_Property_Select_Sum_Min_Key_Max_Avg. Conditionality in GroupBy is tested on key by GroupBy_aggregate_projecting_conditional_expression_based_on_group_key, #17442 also adds conditional on the aggregate function itself.

Will GroupBy with Sum and conditions be supported by EF Core 3.0 ?

SELECT Id, Count(Num) as  ICount, 
   CASE WEHN Count(Id) == 0 
    Then 1.0
    ELSE   SUM(CASE WHEN Num % 2 = 0 THEN 1.0 ELSE 0.0 END) / Count(Num)
    END as  IRate
FROM tb
GROUP BY Id

the linq should be

ctx.Tb.GroupBy(x=>x.Id)
    .Select(x=> new 
    { 
        Id = x.Key,   
        ICount = x.Count(),
        IRate = x.Count() == 0 ? 1.0 : g.Sum(y=>y.Num % 2 == 0 ? 1.0 : 0.0) / x.Count() 
        //OR
         IRate = x.Count() == 0 ? 1.0 : g.Count(y=>y.Num % 2 == 0) / x.Count() 
       // Count should also be translate to Sum when condition
     })