EF Core 难道不支持GroupBy吗？

   最近在修改一个.NET Core的项目，其中ORM用的EF Core，在一次查询分页中，遇到了一个很奇怪的问题，每次查询都很慢，明明已经按照某个编号字段Group By并且还做了分页，为啥查询还这么的慢呢？

首选我想当的解决方案就是为 每个条件查询字段添加索引，但是依然无效，还是很慢；然后查看log日志，仔细核对EF生成的sql，发现了生成的sql根本就没有Group by 以及后面的分页操作也没有生成，sql只是到where条件判断之后就结束了，相当于查询了所有结果，当然展示的数据是我们想要的结果，所以可以肯定的是Group BY 之后的操作是在内存中处理的

原始EF 查询如下

var groupList_one = dbConContext.TMemberWelcomeLog.AsNoTracking().Where(p => p.Status == 0 &&
                                                                   p.MerchantCode == "SH202009094127602" &&
                                                                   p.CreateDateTime >= startTime &&
                                                                   p.CreateDateTime <= DateTime.Now).
                                                                   GroupBy(p => p.MemberCode);
var list_one = await groupList_one.OrderByDescending(r => r.Count()).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
var total_one = list_one.Count();

上面的语句生成的sql如下：

 SELECT [t].[Id], [t].[CreateDateTime], [t].[EnterDateTime], [t].[EnterImg], [t].[LeaveDateTime], [t].[LeaveImg], [t].[MemberCode], [t].[MerchantCode], [t].[Status], [t].[StayTime], [t].[StoreCode], [t].[UpdateDateTime]
 FROM[T_MemberWelcomeLog] AS[t]
 WHERE((([t].[Status] = 0) AND([t].[MerchantCode] = N'SH202009094127602')) AND([t].[CreateDateTime] >= @__startTime_0)) 
 AND([t].[CreateDateTime] <= GETDATE())

从上面的语句来看，很显然是没有生成Group by及以后的分页语句，为什么会是这样呢？？？

注意： EF CORE 3.0及以上版本会报错：Unable to translate the given 'GroupBy' pattern. Call 'AsEnumerable' before 'GroupBy' to evaluate it client-side

于是查询官方文档【客户端与服务器评估】

大概意思是:

EF CORE会尽可能的尝试服务器评估，生成等效的数据库查询SQL，但是有些方法是客户端特有的处理方式，例如在客户端写了一个特殊的方法，去处理EFCore查询中的某一个字段，这个时候服务端是无法预知结果，并转换成对应的sql，这个时候EF CORE会报上面的那个错
那么如何处理上面这个问题呢？官方给出了解决方案，就是需要显示客户端评估，官方话语是：在这种情况下，通过调用 AsEnumerable 或 ToList 等方法（若为异步，则调用 AsAsyncEnumerable 或 ToListAsync），以显式方式选择进行客户端评估，这个结果就是我们上面的查询列子相同，会把AsEnumerable()前面的结果从数据库查询出来，加载到内存中，然后在内存中去做分组及分页的操作

 

说了这么多，貌似跟上面的查询Group by 又有什么关系呢？为何Group by服务端会无法生成对应的sql呢？

    我们仔细思考一下 GroupBy(p => p.MemberCode)返回的是什么对象呢？是IQueryable<IGrouping<TKey, TSource>>对象，而sql中 group by 查询必须是包含在聚合函数或 GROUP BY 子句中，所以是按照sql去查询是无法返回TSource这个对象的，这个时候程序就会需要显示客户端评估，才能解决

这个时候有的小伙伴灵机一动，将上面的查询代码改成如下：

 var groupList_one = dbConContext.TMemberWelcomeLog.AsNoTracking().Where(p => p.Status == 0 &&
                                                                     p.MerchantCode == "SH202009094127602" &&
                                                                     p.CreateDateTime >= startTime &&
                                                                     p.CreateDateTime <= DateTime.Now).
                                                                     GroupBy(p => p.MemberCode).
                                                                     Select(r => new { key = r.Key, count = r.Count() });
 var list_one = await groupList_one.OrderByDescending(r => r.Count()).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
 var total_one = list_one.Count();

 

其实这样就对了，生成的SQL如下：

SELECT [t].[MemberCode] AS [Key], COUNT(*) AS [Count]
FROM[T_MemberWelcomeLog] AS[t]
WHERE((([t].[Status] = 0) AND([t].[MerchantCode] = N'SH202009094127602')) AND([t].[CreateDateTime] >= @__startTime_0)) AND([t].[CreateDateTime] <= GETDATE())
GROUP BY[t].[MemberCode]
ORDER BY COUNT(*) DESC
OFFSET 0 ROWS FETCH NEXT @__p_1 ROWS ONLY

 

在官方文档中也可以找到对应的示例【复杂查询】
可以变换成如下方案：

var groupList_two = from p in dbConContext.TMemberWelcomeLog
                      where p.Status == 0 &&
                            p.MerchantCode == "SH202009094127602" &&
                            p.CreateDateTime >= startTime &&
                            p.CreateDateTime <= DateTime.Now
                      group p by p.MemberCode
                      into g
                      select new { g.Key, Count = g.Count() };                                                
var list_two = groupList_two.OrderByDescending(r => r.Count).Skip((pageIndex - 1) * pageSize).Take(pageSize);

总结

在EF CORE查询中，一定要多去想想，客户端的方法是否真的合理吗？这样是否能生成对应的sql吗？不过现在EF CORE3.0及以上版本是可以在运行的时候，抛出异常，并且在EF CORE 3.0早期版本也是可以添加警告，官方示例代码：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
  optionsBuilder
   .UseSqlServer(@"Server=(localdb)mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
   .ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}