EF Core "Group By could not be translated and will be evaluated locally."

Question

I am running a simple query against an Sql Server database using Entity Framework Core 2.2.6 however the GroupBy is not being executed on the server, instead it is being executed locally.

Is there something i'm missing that will force the group by onto the server?

The 2 variations of EF query i have tried:

public class Holiday
{
    public int Id {get;set;}
    public DateTime Date {get;set;}
    public string Username {get;set;}
    public string Approver {get;set;}
}

//version 1
await _dbContext.Holidays
    .GroupBy(h => new { h.Date})
    .ToDictionaryAsync(x => x.Key.Date, x => x.Select(x1 => x1.Username).ToList());

//version 2
await _dbContext.Holidays
    .GroupBy(h => h.Date)
    .ToDictionaryAsync(x => x.Key, x => x.Select(x1 => x1.Username).ToList());

Both variations produces the following SQL:

SELECT [h].[Id], [h].[Approver], [h].[Date], [h].[HolidayTypeId], [h].[OwningRequestId], [h].[HolidayStatusId], [h].[Username]
FROM [Holidays] AS [h]
ORDER BY [h].[Date]

warning produced:

warnwarn: Microsoft.EntityFrameworkCore.Query[20500] The LINQ expression 'GroupBy([h].Date, [h])' could not be translated and will be evaluated locally.

Suggestions from comments:

//group by string
await _dbContext.Holidays
     .GroupBy(h => h.Username)
     .ToDictionaryAsync(x => x.Key, x => x.Select(x1 => x1.Username).ToList());

//group by part of date
await _dbContext.Holidays
     .GroupBy(h => h.Date.Year)
     .ToDictionaryAsync(x => x.Key, x => x.Select(x1 => x1.Username).ToList());

--group by string
SELECT [h].[Id], [h].[Approver], [h].[Date], [h].[HolidayTypeId], [h].[OwningRequestId], [h].[HolidayStatusId], [h].[Username]
FROM [Holidays] AS [h]
ORDER BY [h].[Username]

--group by part of date
SELECT [h].[Id], [h].[Approver], [h].[Date], [h].[HolidayTypeId], [h].[OwningRequestId], [h].[HolidayStatusId], [h].[Username]
FROM [Holidays] AS [h]
ORDER BY DATEPART(year, [h].[Date])

Somebody can correct me if I'm wrong, but I think EF doesn't support `GroupBy` when it generates the SQL query on the back end. — Dortimer, Sep 05 '19 at 13:52
@Dortimer it is supported as of Ef core 2.1: https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1#linq-groupby-translation — cmpbedes, Sep 05 '19 at 13:53
@cmpbedes This is still just a guess, but how does it handle grouping on something other than Date? Sometimes `.Net` types and `SQL` types don't play along very well, and it could be a matter of it not being able to evaluate `DateTime` against the datatype in the database. — Dortimer, Sep 05 '19 at 13:59
@Dortimer tried grouping on Username which is a string and got the same result. — cmpbedes, Sep 05 '19 at 14:08
@PanagiotisKanavos - the anonymous type version is common amongst the tutorials i've seen so i thought it might be necessary. I included it here to show that i've tried that way too — cmpbedes, Sep 05 '19 at 14:08
Have you tried to group by a part of DateTime? I second @Dortimer, sometimes `.NET` and `SQL` types don't play together well, especially with the date. Could you try to group by the `Year` for example and see whether this evaluates in the DB? — pneuma, Sep 05 '19 at 14:08
@kalexi i've just attempted your suggestions and updated the question with the results — cmpbedes, Sep 05 '19 at 14:15
@cmpbedes: Take a look at https://gunnarpeipman.com/aspnet/ef-core-linq-groupby/ and https://github.com/aspnet/EntityFrameworkCore/issues?q=is%3Aissue+is%3Aopen+GroupBy+label%3Aregression — dropoutcoder, Sep 05 '19 at 14:24

score 17 · Answer 1 · answered Sep 05 '19 at 14:27

It's because there is no SQL query like that.

Think like SQL. If you want to get Usernames by group of Dates, you need both of those.

Basically :

await _dbContext.Holidays
    .GroupBy(h => new { h.Date, h.Username})
    .Select(g => new
        {
          g.Key.Date,
          g.Key.Username
        });

This will produce a SQL query like this.

SELECT [h].[Date],[h].[Username]
FROM [Holidays] AS [h]
GROUP BY [h].[Date],[h].[Username]

After that you can use the data to create the structure of your dictionary however you want.

score 17 · Accepted Answer · answered Sep 05 '19 at 14:33

The problem is that when you're trying to group in the database, you don't really have the means to materialize values inside a group. You only get to SELECT grouped columns or aggregated values (via SUM, etc.) of non-grouped columns.

For example:

SELECT [h].[Date], [h].[Username]
FROM [Holidays] AS [h]

This query would produce result set of which every row would have two columns, date and name.

Let's try grouping though:

SELECT [h].[Date], [h].[Username]
FROM [Holidays] AS [h]
GROUP BY [h.Date]

This SQL query wouldn't be evaluated at all because it's invalid from SQL server perspective. Error message would be

Column 'Holidays.Username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Summing all this up, you can either do what @Ercan Tirman has suggested, or, load all the usernames and dates and group them in-memory:

var dateAndUsername = await _dbContext.Holidays
    .Select(x => new {x.Date, x.Username})
    .ToArrayAsync();

Dictionary<DateTime, List<string>> grouped = dateAndUsername
    .GroupBy(x => x.Date)
    .ToDictionary(x => x.Key, x => x.Select(y => y.Username).ToList());

I see the mistake in what i was trying to do. i'll go with your grouping in memory suggestion thanks for the explanation! — cmpbedes, Sep 05 '19 at 14:42

EF Core "Group By could not be translated and will be evaluated locally."

2 Answers2

Linked