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 Core 2.1 group by with where condition evaluated locally #12255

Open
mojtabakaviani opened this Issue Jun 6, 2018 · 7 comments

Comments

Projects
None yet
4 participants
@mojtabakaviani

mojtabakaviani commented Jun 6, 2018

var books = from b in db.Books
                    group b by b.Library into g
                    orderby b.History
                    where g.Sum(s=> s.Enter - s.Exit) > 0
                    select new { f.Key.Library.Name, Stock =g.Sum(s=> s.Enter - s.Exit) };

Warning

Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'GroupBy(new <>f__AnonymousType66`4(Library = [b.Library]), [b])' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
Microsoft.EntityFrameworkCore.Query:Warning: The LINQ expression 'where (({from Book b in [g] select ([b].Enter - [b].Exit) => Sum()} > 0))' could not be translated and will be evaluated locally.

Further technical details

EF Core version: 2.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 x64
IDE: Visual Studio 2017 15.8 Preview 2

@ajcvickers ajcvickers added this to the Backlog milestone Jun 6, 2018

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers Jun 6, 2018

Member

@smitpatel to add workaround.

Member

ajcvickers commented Jun 6, 2018

@smitpatel to add workaround.

@smitpatel smitpatel removed this from the Backlog milestone Jun 7, 2018

@smitpatel

This comment has been minimized.

Show comment
Hide comment
@smitpatel

smitpatel Jun 7, 2018

Contributor

@mojtabakaviani - The query posted above does not compile in C#. It seems that you are trying to GroupBy an entity type. What are your expectations in terms of how it would translate to server GROUP BY?

Contributor

smitpatel commented Jun 7, 2018

@mojtabakaviani - The query posted above does not compile in C#. It seems that you are trying to GroupBy an entity type. What are your expectations in terms of how it would translate to server GROUP BY?

@smitpatel

This comment has been minimized.

Show comment
Hide comment
@smitpatel

smitpatel Jun 7, 2018

Contributor

If the expectation is to find stock for all books per library then following query could help

var q = (from l in db.Set<Library>()
            join s in db.Books.GroupBy(b => b.LibraryId, b => b.Enter - b.Exit)
                .Where(g => g.Sum() > 0)
                .Select(g => new
                {
                    g.Key,
                    Stock = g.Sum()
                })
                on l.Id equals s.Key
            select new
            {
                LibraryName = l.Name,
                s.Stock
            }).ToList();

Produces SQL

      SELECT [t].[Key], [t].[Stock], [l].[Name] AS [LibraryName]
      FROM [Library] AS [l]
      INNER JOIN (
          SELECT [b].[LibraryId] AS [Key], SUM([b].[Enter] - [b].[Exit]) AS [Stock]
          FROM [Books] AS [b]
          GROUP BY [b].[LibraryId]
          HAVING SUM([b].[Enter] - [b].[Exit]) > 0
      ) AS [t] ON [l].[Id] = [t].[Key]
Contributor

smitpatel commented Jun 7, 2018

If the expectation is to find stock for all books per library then following query could help

var q = (from l in db.Set<Library>()
            join s in db.Books.GroupBy(b => b.LibraryId, b => b.Enter - b.Exit)
                .Where(g => g.Sum() > 0)
                .Select(g => new
                {
                    g.Key,
                    Stock = g.Sum()
                })
                on l.Id equals s.Key
            select new
            {
                LibraryName = l.Name,
                s.Stock
            }).ToList();

Produces SQL

      SELECT [t].[Key], [t].[Stock], [l].[Name] AS [LibraryName]
      FROM [Library] AS [l]
      INNER JOIN (
          SELECT [b].[LibraryId] AS [Key], SUM([b].[Enter] - [b].[Exit]) AS [Stock]
          FROM [Books] AS [b]
          GROUP BY [b].[LibraryId]
          HAVING SUM([b].[Enter] - [b].[Exit]) > 0
      ) AS [t] ON [l].[Id] = [t].[Key]

@ajcvickers ajcvickers added this to the Backlog milestone Jun 8, 2018

@ajcvickers

This comment has been minimized.

Show comment
Hide comment
@ajcvickers

ajcvickers Jun 8, 2018

Member

Putting this on the backlog because the translation seems desirable, while recognizing that the cost of doing so may ultimately be prohibitive.

Member

ajcvickers commented Jun 8, 2018

Putting this on the backlog because the translation seems desirable, while recognizing that the cost of doing so may ultimately be prohibitive.

@mojtabakaviani

This comment has been minimized.

Show comment
Hide comment
@mojtabakaviani

mojtabakaviani Jun 11, 2018

@smitpatel your query maybe work but is very complex.

mojtabakaviani commented Jun 11, 2018

@smitpatel your query maybe work but is very complex.

@qwertyuiope

This comment has been minimized.

Show comment
Hide comment
@qwertyuiope

qwertyuiope Jun 13, 2018

I am also having this problem when I am using conditional count:

            context.Jobs.GroupBy(a => a.Agent).Select(a => new
            {
                a.Key,
                MaxDate = a.Max(b => b.CreatedDate),
                RelevantRank = a.Count(b => b.Rank > 10000)

            }).ToList();

qwertyuiope commented Jun 13, 2018

I am also having this problem when I am using conditional count:

            context.Jobs.GroupBy(a => a.Agent).Select(a => new
            {
                a.Key,
                MaxDate = a.Max(b => b.CreatedDate),
                RelevantRank = a.Count(b => b.Rank > 10000)

            }).ToList();
@smitpatel

This comment has been minimized.

Show comment
Hide comment
@smitpatel

smitpatel Jun 14, 2018

Contributor

@qwertyuiope - The feature you are looking for is #11711

Contributor

smitpatel commented Jun 14, 2018

@qwertyuiope - The feature you are looking for is #11711

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