New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

EF can not generate "Group by" sql ? #6245

Closed
John0King opened this Issue Aug 4, 2016 · 8 comments

Comments

Projects
None yet
6 participants
@John0King

John0King commented Aug 4, 2016

Steps to reproduce

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']
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

This comment has been minimized.

John0King commented Aug 4, 2016

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:

_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

This comment has been minimized.

skimmilk commented Aug 4, 2016

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

This comment has been minimized.

Member

divega commented Aug 8, 2016

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

@divega

This comment has been minimized.

Member

divega commented Aug 8, 2016

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.

@divega divega closed this Aug 8, 2016

@divega divega modified the milestone: 1.1.0 Aug 8, 2016

@bleepzter

This comment has been minimized.

bleepzter commented Jan 16, 2018

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

@rafaelmaroxa

This comment has been minimized.

rafaelmaroxa commented Mar 23, 2018

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

@smitpatel

This comment has been minimized.

Contributor

smitpatel commented Mar 23, 2018

@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

This comment has been minimized.

bleepzter commented Mar 23, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment