从SQL到LINQ的多个联接，计数和左联接

Question

我用多个JOIN（包括一个LEFT JOIN）编写了这个SQL请求。
它给了我预期的结果 。

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

但是，“ Id就像此请求的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)
              };

我试图适应这个问题，但我无法弄清楚。我Count对分组子请求的内部迷失了。
谁能解释我哪里错了？

专家提示：如果有人可以用lambda表达式编写等效项，则可获赠积分

Answer 1

要将SQL转换为LINQ查询理解：

1. 将子选择转换为单独声明的变量。
2. 翻译在LINQ子句顺序每个子句，翻译一元和聚集操作符（DISTINCT，TOP，MIN，MAX等等）转换成适用于整个LINQ查询功能。
3. 使用表别名作为范围变量。使用列别名作为匿名类型字段名称。
4. 对多个列（例如中的）使用匿名类型（new {… ）。}``groupby
5. 用于First().field从groupby聚合范围变量获取非键值。
6. JOIN``AND在两个表之间进行多次ed相等测试的条件应转换为匿名对象，在每个对象的每一侧equals
7. JOIN不是所有相等测试的条件都AND必须使用where联接外的子句或叉积（from… from…）然后使用来处理where。如果要这样做LEFT JOIN，请Where在连接范围变量和DefaultIfEmpty()调用之间添加一个lambda子句。
8. LEFT JOIN通过使用模拟into joinvariable 做是另一回事from了 joinvariable 其次.DefaultIfEmpty()。
9. 替换COALESCE为条件运算符（?:）和一个null测试。
10. 翻译IN来.Contains()和NOT IN到!… Contains()，使用文字阵列或恒定列表数组变量。
11. 翻译 X BETWEEN 低 AND 高 到 低 <= X && X <= 高 。
12. 翻译CASE，ISNULL并IIF以三元条件运算符?:。
13. SELECT * 必须用select range_variable替换，或者对于联接来说，是包含所有范围变量的匿名对象。
14. SELECT列必须替换为select new {…}创建具有所有所需字段或表达式的匿名对象。
15. SELECT可以通过重复表达式或let在第一次使用表达式之前使用来命名对计算列的引用。
16. FULL OUTER JOIN必须使用扩展方法来正确处理。
17. 除非两个子查询都为，否则翻译UNION为，在这种情况下，您可以翻译为，而不必使用。Concat``DISTINCT``Union``DISTINCT
18. GROUP BY使用单例来转换没有查询的聚合查询GroupBy：添加.GroupBy(r => 1)，然后在转换聚合函数Select。
19. 可以使用获取类（EF Core），类（EF <6）的实例或访问静态方法（EntityFramework 6.x）来访问Date Math和其他一些规范函数。EF.Functions``DbFunctions``EntityFunctions``DbFunctions
20. LIKE使用（EF Core> = 2）EF.Functions.Like(column, pattern)或（EF 6.x）转换SQL表达式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 });