如何将统计子分类数量的MySQL CTE自连接查询转换为Linq查询

Linq没有原生CTE语法，但可以通过复用同一子查询实现等效逻辑，EF等ORM会自动优化执行计划，和你原MySQL的CTE查询效果完全一致。

前提假设 #

你的数据上下文已配置对应表的DbSet，实体字段和表结构一一对应：

• DbSet<Category> Categories 对应层级结构表Categories
• DbSet<CategoryName> CategoryNames 对应多语言名称表Category_names（你SQL中使用的表名为category_translations）

实现代码 #

1. 定义等效CTE的公共子查询 #

var cte = from c in Categories
          join ct in CategoryNames
          on c.Id equals ct.category_id
          // 过滤指定语言，和你原SQL逻辑一致
          where ct.language_id == 1
          group new { c, ct } by new { c.ParentId, c.Id, ct.language_id, ct.title } into g
          select new
          {
              ParentId = g.Key.ParentId,
              Id = g.Key.Id,
              LanguageId = g.Key.language_id,
              Title = g.Key.title,
              ItemCount = g.Count()
          };

2. 自连接分组统计子分类数量 #

你原SQL中左连接后加WHERE CatCTE.id IS NOT NULL的逻辑等效于内连接，直接用内连接执行效率更高：

var result = from parCte in cte
             join catCte in cte
             on parCte.Id equals catCte.ParentId
             group catCte by new { parCte.Id, parCte.Title } into g
             select new
             {
                 ParentId = g.Key.Id,
                 ParentTitle = g.Key.Title,
                 ChildCategoryCount = g.Count()
             };

如果需要保留没有子分类的父分类（子分类数量为0的记录也返回），改用左连接写法：

var resultWithZero = from parCte in cte
                     join catCte in cte
                     on parCte.Id equals catCte.ParentId into childGroup
                     from catCte in childGroup.DefaultIfEmpty()
                     group catCte by new { parCte.Id, parCte.Title } into g
                     select new
                     {
                         ParentId = g.Key.Id,
                         ParentTitle = g.Key.Title,
                         ChildCategoryCount = g.Count(x => x != null)
                     };

注意事项 #

EF Core 3.0及以上版本会自动识别重复使用的子查询，翻译为高效的SQL执行逻辑，和原生CTE的执行效率无明显差异。

内容的提问来源于stack exchange，提问作者Zeyn Alabedeen