具有多重联接、计数和左联接的 SQL 到 LINQ

我用多个语句编写了这个 SQL 请求JOIN（包括一个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)
              };

我尝试去适应这个问题 https://stackoverflow.com/questions/19356439/left-join-in-linq-to-entities但我不明白。我迷失了Count在分组的子请求内。
谁能解释我哪里错了？

专家提示：如果有人可以用 lambda 表达式编写等价的内容，那就加分了

将 SQL 转换为 LINQ 查询理解：

1. 将子选择转换为单独声明的变量除非它们引用子选择之外的列，在这种情况下使用括号创建子查询。
2. 按 LINQ 子句顺序翻译每个子句，翻译单个单子和聚合运算符 (DISTINCT, TOP, MIN, MAX等）转换为应用于整个 LINQ 查询的函数。
3. 使用表别名作为范围变量。使用列别名作为匿名类型字段名称。
4. 使用匿名类型（new { ... }）对于多列（例如在groupby).
5. Use First().field从中获取非键值groupby聚合范围变量。
6. 使用 EF 或 EF Core 时，翻译JOIN导航属性中的子句可能使用.Include().
7. 否则JOIN多个子句AND两个表之间的 ed 相等测试应转换为两侧的匿名对象equals.
8. JOIN并非全部相等测试的条件AND必须使用处理where连接外部的子句，或带有叉积 (from ... from...） 进而where。如果你正在做LEFT JOIN, 添加一个 lambdaWhere连接范围变量和DefaultIfEmpty() call.
9. LEFT JOIN是通过使用模拟into 连接变量并做另一个from the 连接变量其次是.DefaultIfEmpty().
10. 翻译多个表FROM子句分成多个from条款。
11. 翻译FROM T1 CROSS APPLY T2一分为二from条款，一项用于T1和一个用于T2.
12. 翻译FROM T1 OUTER APPLY T2一分为二from条款，一项用于T1和一个用于T2，但添加.DefaultIfEmpty() to T2.
13. Replace COALESCE与条件运算符 (?:)and a null test.
14. 翻译IN to .Contains() and NOT IN to !...Contains()，使用文字数组或数组变量作为常量列表。
15. 翻译x BETWEEN low AND high to low <= x && x <= high.
16. 翻译CASE, ISNULL and IIF到三元条件运算符?:.
17. SELECT *必须替换为 select range_variable 或连接（包含所有范围变量的匿名对象）。
18. SELECT列必须替换为select new { ... }创建具有所有所需字段或表达式的匿名对象。
19. 参考计算SELECT可以通过重复表达式或使用来翻译列let在第一次使用之前命名表达式。
20. Proper FULL OUTER JOIN必须用一个处理扩展方法 https://stackoverflow.com/a/43669055/2557128.
21. 翻译UNION to Concat除非两个子查询都是DISTINCT，在这种情况下你可以翻译为Union并留下DISTINCT.
22. 翻译具有多个没有结果列的聚合查询GROUP BY使用单例GroupBy: add .GroupBy(r => 1) (or group...by 1 into g），然后翻译聚合函数Select new { }.
23. 日期 数学 和其他一些典范 https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/canonical-functions可以使用以下方式访问函数EF.Functions获取一个实例DbFunctions类（EF 核心），EntityFunctions等级 (EF DbFunctions访问静态方法 (EntityFramework 6.x)。
24. 翻译 SQLLIKE使用 (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 to 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 });