EF Core“Group By”无法翻译，将在本地评估。

Question

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

有什么我错过的东西可以将GroupBy强制执行到服务器上吗？

我尝试了2种EF查询的变体：

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：

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

产生警告:

warnwarn: Microsoft.EntityFrameworkCore.Query[20500] LINQ表达式'GroupBy([h].Date, [h])'无法转换，将在本地计算。

评论中的建议：

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

- cmpbedes

---

如果您有的话，请展示Holiday类和映射。 - miechooy

有人可以纠正我，但我认为EF在生成后端的SQL查询时不支持GroupBy。 - Dortimer

@Dortimer，从Ef core 2.1开始支持：https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1#linq-groupby-translation - cmpbedes

你尝试过按DateTime的一部分进行分组吗？我赞同@Dortimer的观点，有时.NET和SQL类型不太兼容，特别是在日期方面。你可以尝试按照年份进行分组，看看是否能在数据库中得到正确的结果。 - pneuma

@kalexi，我刚刚尝试了你的建议，并更新了问题并附上了结果。 - cmpbedes

Answer 1

这是因为没有这样的SQL查询语句。

想象一下SQL。如果您想按日期组获取用户名，您需要这两者。

基本上：

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

这将产生类似于以下的SQL查询。

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

接下来，您可以使用这些数据根据自己的需要创建字典结构。

- Ercan Tırman

Answer 2

问题在于，在数据库中尝试分组时，您没有实现组内值的手段。 您只能选择分组列或聚合值（通过SUM等）的非分组列。

例如：

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

这个查询将产生一个结果集，其中每一行都有两个列，日期和名称。

让我们尝试分组:

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

这个 SQL 查询不会被评估，因为从SQL服务器的角度来看它是无效的。错误消息将是

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

- pneuma

---

我看到了我尝试做的错误。我会采用你在内存分组方面的建议，感谢你的解释！ - cmpbedes