EF Core“分组依据无法翻译，将在本地进行评估".

问题描述

我正在使用Entity Framework Core 2.2.6对Sql Server数据库运行一个简单查询，但是GroupBy不在服务器上执行，而是在本地执行.

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?

我尝试过的EF查询的2种变化形式:

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());

两个版本都会产生以下SQL:

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]

产生警告:

警告:Microsoft.EntityFrameworkCore.Query [20500]LINQ表达式'GroupBy([h] .Date，[h])'无法翻译，将在本地进行评估.

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

评论建议:

//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])

推荐答案

问题是，当您尝试在数据库中进行分组时，实际上并没有实现这些值的方法.您只能进入 SELECT 分组列或非分组列的汇总值(通过 SUM 等).

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.

例如:

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]

此SQL查询根本不会被评估，因为从SQL Server的角度来看它是无效的.错误消息将是

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.

总结所有这些，您可以按照@Ercan Tirman的建议进行操作，或者加载所有用户名和日期并在内存中将它们分组:

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());

这篇关于EF Core“分组依据无法翻译，将在本地进行评估".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！