EFCore转SQL语句，如何避免Select嵌套

Question

这是我想要的Sql语句

SELECT 
  SUM(`IsRelease`) AS `ItemDownshelfCount`,
  SUM(
    CASE
      WHEN `IsRelease` = 0
      THEN 1 
    END
  ) AS `GoodsCount` 
FROM
  `eshop_goods_tb` 
WHERE 
    `TenantId` = @__tid_0
    AND `IsDelete` = 0;

这个是EFCore查询代码，

            var query = await GetQueryableAsync(w => w.TenantId.Equals(tid) && w.IsDelete.Equals((int)YesOrNoEnum.No));
            var result = query.GroupBy(x=>new { })
                .Select(s => new GoodsOverview
            {
                ItemDownshelfCount = s.Where(w => w.IsRelease == (int)YesOrNoEnum.Yes).Count(),
                GoodsCount = s.Where(w=>w.IsRelease == (int)YesOrNoEnum.No).Count()
            });
            return result.FirstOrDefault();

这是用EFCore拼接出来的Sql语句

主要纠结点是，有两层Select嵌套，这个可以优化么

SELECT CAST(COUNT(CASE
    WHEN `t`.`IsRelease` = 1 THEN 1
END) AS SIGNED) AS `ItemDownshelfCount`, CAST(COUNT(CASE
    WHEN `t`.`IsRelease` = 0 THEN 1
END) AS SIGNED) AS `GoodsCount`
FROM (
    SELECT `e`.`IsRelease`, 1 AS `Key`
    FROM `eshop_goods_tb` AS `e`
    WHERE (`e`.`TenantId` = @__tid_0) AND (`e`.`IsDelete` = 0)
) AS `t`
GROUP BY `t`.`Key`
LIMIT 1

我想知道，这个IQueryable应该怎么写比较好？

Answer 1

可以尝试使用EF Core的扩展方法AsEnumerable()来解决这个问题。将AsEnumerable()插入到查询中，这样EF Core就不会继续生成SQL语句了，而是将查询结果转换成内存中的集合，然后在内存中进行分组和计算。

示例代码如下：

var query = await GetQueryableAsync(w => w.TenantId.Equals(tid) && w.IsDelete.Equals((int)YesOrNoEnum.No));
 var result = query.AsEnumerable()
                   .GroupBy(x=>new { })
                   .Select(s => new GoodsOverview
                   {
                       ItemDownshelfCount = s.Where(w => w.IsRelease == (int)YesOrNoEnum.Yes).Count(),
                       GoodsCount = s.Where(w=>w.IsRelease == (int)YesOrNoEnum.No).Count()
                   });
 return result.FirstOrDefault();

这样就可以避免嵌套的 Select 了。