c# - 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])

Answer 1

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.