Efcore: EF can not generate "Group by" sql ?

[John0King]

Steps to reproduce

``` C#
var list2 = from p in _Db.Purchase
group p by p.WareName into GP
select new { WareName = GP.Key, count = GP.Sum(a=>a.Count) };
var r = list2.ToList();

### The issue

this linq will  generate 

Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory:Warning: The LINQ expression 'GroupBy([p].WareName, [p])' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory:Warning: The LINQ expression 'GroupBy([p].WareName, [p])' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory:Warning: The LINQ expression 'Sum()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

``` sql
SELECT "p"."Id", "p"."AllPrice", "p"."BuyTime", "p"."Buyer", "p"."Count", "p"."Model", "p"."Price", "p"."Supplier", "p"."WareName"
FROM "Purchase" AS "p"
ORDER BY "p"."WareName"

Further technical details

EF Core version: "Microsoft.EntityFrameworkCore.Sqlite": "1.0.0"
Operating system: windows 10 x64

[John0King]

another issue is "IQueryable<int>.Sum()"' might return null in sql level, but in linq will be int that can not be null.
so every time we need write linq like this:

C# _Db.SomeTable.Where(s=>s.ListProperty.Sum(list=>(int?)list.IntProperty ?? 0 ) >0 ) // if there no data in [SomeTable] , Sum will return null

I think EF should automatic convert null to 0 for sql generating, because this is the same behavior in C# object (IEnumerable<T>)

[skimmilk]

Entity Framework Core does not translate GroupBy into SQL yet, that is a future feature.

https://docs.efproject.net/en/latest/efcore-vs-ef6/features.html

[divega]

@John0King the issue with Sum() is tracked at #800. It is in our backlog, but feel free to provide feedback there.

[divega]

The part about GroupBy() being translated to SQL is covered by #2341, which currently is also in our backlog, i.e. we are not currently planning to address it in 1.1.0. Feel free to submit additional feedback there.

[bleepzter]

Why would you guys release a half baked framework? For the sake of release? We spent hours trying to find this bug.

[rafaelmaroxa]

@John0King, I just ran into the same issue. How did you overcome this issue with the GroupBy? Thanks in advance.

[smitpatel]

@rafaelmaroxa - This is duplicate of issue #2341 GroupBy translation has been implemented in 2.1.0-preview1-final release. You can read more what is supported in first post in #2341

[bleepzter]

Why did you guys release am EF without this feature in the first place?!?
It was a core feature of EF.

You can't release a half finished framework code and call it a "Release"
then wait 2 major release cycle to actually fix it.

I had to go through hundreds of thousands of lines of code to make sure
group by wasn't used because noone in our organization was aware EF Core 1x
didn't support it.

Unbelievable! Irresponsible!

On Fri, Mar 23, 2018, 3:54 PM Smit Patel [e-mail] wrote:

@rafaelmaroxa http:///rafaelmaroxa - This is duplicate of issue #2341
https://github.com/aspnet/EntityFrameworkCore/issues/2341 GroupBy
translation has been implemented in 2.1.0-preview1-final release. You can
read more what is supported in first post in #2341
https://github.com/aspnet/EntityFrameworkCore/issues/2341

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/aspnet/EntityFrameworkCore/issues/6245#issuecomment-375808529,
or mute the thread
https://github.com/notifications/unsubscribe-auth/ADLfIeT6FKpdz-kJYWgziVVjXBU9JIzyks5thW8NgaJpZM4JcrHm
.