EF Core 2.1 group by with where condition evaluated locally

[mojtabakaviani]

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]

@smitpatel to add workaround.

[smitpatel]

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

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]

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

[mojtabakaviani]

@smitpatel your query maybe work but is very complex.

[qwertyuiope]

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]

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

[sguryev]

Hi guys. What about the case with Ranking? Let's say I have a table with clients. And I need to group them by Country and select the top1 earning client in each country. So result should be:
Country-CustomerName-Earning

I use
GroupBy(c => c.Country).Select(cg => new {Country = cg.Key, cg.ThenByDescending(c => c.Earning).Name, Earning = cg.Max(t => t.Earning)})

This query will be evaluated locally. Do you have any advises to execute it on server? EF 6.2 can do it.

[Stamo-Gochev]

@smitpatel Will this be supported (as in EF 6 for example) or should the queries be rewritten? This seems like a problem when migrating projects.

[smitpatel]

@Stamo-Gochev - This issue is in backlog milestone. We plan to implement at some point but it is not currently planned for next release.

[mojtabakaviani]

EF have many group by issues but i thinking not in man road map and EF team work in your ideas not cutomers feadbacks. Many issues that customers requested in backlog milestone for long times.

[djbios]

I'am trying to use Group by to remove duplicates, calculated by multiple columns.
There are two options (from Stackoverflow):

var query = db.Incidents
    .GroupBy(x => new { x.EmployeeId, x.DateOfIncident, x.IsAttendanceIncident })
.Select(x => x.FirstOrDefault());  // your original code which retrieves entities to not delete

var dupes = db.Incidents.Except( query ); // get entities to delete
foreach( var dupe in dupes )
{
    db.Incidents.Remove( dupe );
}

var query = db.Incidents
    .GroupBy(x => new { x.EmployeeId, x.DateOfIncident, x.IsAttendanceIncident }).
    .SelectMany( x => x.OrderBy( y => y.Id ).Skip(1) ); // gets dupes directly
foreach( var dupe in query)
{
    db.Incidents.Remove( dupe );
}

Both evaluating locally.

[smitpatel]

@djbios - #13805

[douglasg14b]

EF Core 2.2

A simple group by in LINQ to SQL evaluates locally, and also throws a NullReferenceException at Microsoft.EntityFrameworkCore.Storage.ValueBuffer.get_Count()...

• FileTags & PackageTags are join tables

from tags in _context.Tags
from fileTag in _context.FileTags
    .Where(x => x.TagId == tags.Id)
    .DefaultIfEmpty()
from packageTag in _context.FileTags
    .Where(x => x.TagId == tags.Id)
    .DefaultIfEmpty()
group tags by tags.Id into tagIds
select new
{
    Id = tagIds.Key,
    Count = tagIds.Count()
};

Generated Queries:

Query1:

SELECT "tags0"."Id"
FROM "Tags" AS "tags0"

Query2:

SELECT 1
FROM (
    SELECT NULL AS "empty"
) AS "empty4"
LEFT JOIN (
    SELECT "x4".*
    FROM "FileTags" AS "x4"
    WHERE "x4"."TagId" = @_outer_Id2
) AS "t4" ON 1 = 1

• Stops here with a NullReferenceException

Expected Query:

I'd expect along the lines of:

SELECT 
  Tags.Id,
  Count(Tags.Id) as Count
FROM
  Tags
LEFT JOIN FileTags ON Tags.Id = FileTags.TagId
LEFT JOIN PackageTags on Tags.Id = PackageTags.TagId
GROUP BY Tags.Id

This and other queries are being used on test data, but local evaluation of millions of records is a hard stop anyways...

[smitpatel]

Duplicate of #11711