具有多个连接、计数和左连接的 SQL 到 LINQ

Question

我用多个 JOIN 写了这个 SQL 请求(包括 LEFT JOIN )。
它给了我预期的结果 .

SELECT DISTINCT c.Id, 
       c.Title, 
       COUNT(v.Id) AS 'Nb_V2',
       COUNT(DISTINCT v.IdUser) AS 'Nb_V1',
       r.cnt AS 'Nb_R'
FROM TABLE_C c
JOIN TABLE_V v on c.Id = v.Id
LEFT JOIN ( 
    SELECT Id, COUNT(*)  AS cnt 
    FROM TABLE_R 
    GROUP BY Id
) r ON c.Id = r.Id
WHERE c.IdUser = '1234'
GROUP BY c.Id, c.Title, r.cnt

然而，我喜欢这个请求的 Linq 等价物，把它放在我的应用程序的数据访问层。

我试过类似的东西:

var qResult = from c in dbContext.TABLE_C
              join v in dbContext.TABLE_V on c.IdC equals v.IdC
              join r in dbContext.TABLE_R on v.IdC equals r.IdC into temp
              from x in temp.DefaultIfEmpty()
              group x by new { c.IdC, c.Title /*miss something ?*/} into grouped
              select new
              {
                  IdC = grouped.Key.IdC,          --good result
                  Title = grouped.Key.Title,      --good result
                  NbR = grouped.Distinct().Count(t => t.IdC > 0), --good, but "t.Id > 0" seems weird
                  Count = --I'm lost. No idea how to get my COUNT(...) properties (Nb_V1 and Nb_V2)
              };

我试着适应 this SO question但我想不通。我迷路了 Count在分组的子请求中。
谁能解释我哪里错了？

专业提示:如果有人可以用 lambda 表达式写出等价物，那就加分了

Answer 1

将 SQL 转换为 LINQ 查询理解:

将子选择翻译为单独声明的变量。

以 LINQ 子句顺序翻译每个子句，将 monadic 和聚合运算符( DISTINCT 、 TOP 、 MIN 、 MAX 等)转换为应用于整个 LINQ 查询的函数。

使用表别名作为范围变量。使用列别名作为匿名类型字段名称。

对多列使用匿名类型( new { ... } )(例如在 groupby 中)。

使用 First().field从 groupby 获取非键值聚合范围变量。

JOIN多个条件 AND两个表之间的 ed 相等性测试应转换为 equals 每一侧的匿名对象。

JOIN并非所有相等性测试的条件 AND必须使用 where 处理连接外的子句，或具有叉积 ( from ... from ...) 然后是 where .如果您正在做 LEFT JOIN , 添加一个 lambda Where连接范围变量和 DefaultIfEmpty() 之间的子句称呼。

LEFT JOIN使用 into 模拟joinvariable 并做另一个 from后跟 .DefaultIfEmpty() 的连接变量.

替换 COALESCE使用条件运算符 ( ?: ) 和 null测试。

翻译 IN至 .Contains()和 NOT IN至 ! ... Contains() , 对常量列表使用文字数组或数组变量。

翻译 x BETWEEN低AND从高到低 <= x && x <=高的。

翻译 CASE , ISNULL和 IIF到三元条件运算符 ?: .

SELECT *必须替换为 select range_variable 或对于连接，一个包含所有范围变量的匿名对象。

SELECT列必须替换为 select new { ... }创建具有所有所需字段或表达式的匿名对象。

对计算的引用 SELECT可以通过重复表达式或使用 let 来翻译列。在第一次使用之前命名表达式。

正确 FULL OUTER JOIN必须用 extension method 处理.

翻译 UNION至 Concat除非两个子查询都是 DISTINCT ，在这种情况下，您可以转换为 Union并离开 DISTINCT .

翻译没有 GROUP BY 的聚合查询使用单例 GroupBy : 添加 .GroupBy(r => 1)然后翻译Select中的聚合函数.

日期数学和其他一些 canonical可以使用 EF.Functions 访问函数获取 DbFunctions 的实例类(EF 核心)，EntityFunctions等级 (EF < 6) 或 DbFunctions访问静态方法(EntityFramework 6.x)。

翻译 SQL LIKE表达式使用 (EF Core >= 2) EF.Functions.Like(column, pattern)或 (EF 6.x) DbFunctions.Like(column, pattern) .

将这些规则应用于您的 SQL 查询，您将获得:

var subrq = from r in Table_R
            group r by r.Id into rg
            select new { Id = rg.Key, cnt = rg.Count() };

var ansq = (from c in Table_C
            join v in Table_V on c.Id equals v.Id
            join r in subrq on c.Id equals r.Id into rj
            from r in rj.DefaultIfEmpty()
            where c.IdUser == "1234"
            group new { c, v, r } by new { c.Id, c.Title, r.cnt } into cvrg
            select new {
                cvrg.Key.Title,
                Nb_V2 = cvrg.Count(),
                Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(),
                Nb_R = (int?)cvrg.Key.cnt
            }).Distinct();

lambda 转换很棘手，但 LEFT JOIN 的转换至 GroupJoin ... SelectMany需要的是什么:

var subr2 = Table_R.GroupBy(r => r.Id).Select(rg => new { Id = rg.Key, cnt = rg.Count() });
var ans2 = Table_C.Where(c => c.IdUser == "1234")
                  .Join(Table_V, c => c.Id, v => v.Id, (c, v) => new { c, v })
                  .GroupJoin(subr, cv => cv.c.Id, r => r.Id, (cv, rj) => new { cv.c, cv.v, rj })
                  .SelectMany(cvrj => cvrj.rj.DefaultIfEmpty(), (cvrj, r) => new { cvrj.c, cvrj.v, r })
                  .GroupBy(cvr => new { cvr.c.Id, cvr.c.Title, cvr.r.cnt })
                  .Select(cvrg => new { cvrg.Key.Title, Nb_V2 = cvrg.Count(), Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(), Nb_R = (int?)cvrg.Key.cnt });