从SQL到LINQ的多个联接，计数和左联接 [英] SQL to LINQ with multiple join, count and left join

问题描述

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

I wrote this SQL request with multiple JOIN (including a LEFT JOIN).
It gives me the expected result.

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等效项，将其放置在应用程序的数据访问层.

However, 'Id like the Linq equivalent of this request, to put it my application's Data Access layer.

我尝试过类似的事情:

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)
              };

我试图适应这个SO问题，但我不知道出来.我对分组的子请求中的Count迷失了.
谁能解释我哪里错了?

I tried to adapt this SO question but I can't figure it out. I'm lost with the Count inside the groupped sub-request.
Can anyone explain me where i'm wrong ?

提示:如果有人可以用lambda表达式编写等价物，则可获得加分

Pro tip : Bonus point if someone can write the equivalent with a lambda expression

推荐答案

用于将SQL转换为LINQ查询理解:

For translating SQL to LINQ query comprehension:

1. 将子选择转换为单独声明的变量.
2. 按LINQ子句顺序转换每个子句，将单子运算符和聚合运算符(DISTINCT，TOP，MIN，MAX等)转换为应用于整个LINQ查询的函数.
3. 使用表别名作为范围变量.使用列别名作为匿名类型字段名称.
4. 对多个列(例如，在groupby中)使用匿名类型(new { ... }).
5. 使用First().field从groupby聚合范围变量获取非键值.
在两个表之间进行多次AND相等性测试的
6. JOIN条件应转换为equals
每一侧的匿名对象 并非所有使用AND进行相等性测试的
7. JOIN条件都必须使用联接外的where子句或叉积(from ... from ...)进行处理，然后where.如果要执行LEFT JOIN，请在联接范围变量和DefaultIfEmpty()调用之间添加一个lambda Where子句.
8. LEFT JOIN是通过使用into joinvariable 并再执行from joinvariable 后跟.DefaultIfEmpty()来模拟的.
9. 用条件运算符(?:)和null测试替换COALESCE.
10. 使用常量数组或常量列表的数组变量将IN转换为.Contains()，将NOT IN转换为! ... Contains().
11. 将 x BETWEEN low AND high 转换为 low <= x && x <= high .
12. 将CASE和IIF转换为三元条件运算符?:.
13. SELECT *必须替换为select range_variable，或者对于联接来说，是包含所有范围变量的匿名对象.
14. SELECT列必须替换为select new { ... }创建具有所有所需字段或表达式的匿名对象.
15. 对计算出的SELECT列的引用可以通过重复该表达式或在第一次使用该表达式之前使用let对该表达式进行命名来进行翻译.
16. 正确的FULL OUTER JOIN必须使用扩展方法来处理.
17. 除非两个子查询都是DISTINCT，否则将UNION转换为Concat，在这种情况下，您可以转换为Union并省去DISTINCT.

1. Translate subselects as separately declared variables.
2. Translate each clause in LINQ clause order, translating monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
3. Use table aliases as range variables. Use column aliases as anonymous type field names.
4. Use anonymous types (new { ... }) for multiple columns (e.g. in groupby).
5. Use First().field to get non-key values from the groupby aggregate range variable.
6. JOIN conditions that are multiple ANDed equality tests between the two tables should be translated into anonymous objects on each side of equals
7. JOIN conditions that aren't all equality tests with AND must be handled using where clauses outside the join, or with cross product (from ... from ...) and then where. If you are doing LEFT JOIN, add a lambda Where clause between the join range variable and the DefaultIfEmpty() call.
8. LEFT JOIN is simulated by using into joinvariable and doing another from the joinvariable followed by .DefaultIfEmpty().
9. Replace COALESCE with the conditional operator (?:)and a null test.
10. Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
11. Translate x BETWEEN low AND high to low <= x && x <= high.
12. Translate CASE and IIF to the ternary conditional operator ?:.
13. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
14. SELECT columns must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
15. References to computed SELECT columns can be translated by repeating the expression or by using let to name the expression before its first use.
16. Proper FULL OUTER JOIN must be handled with an extension method.
17. Translate UNION to Concat unless both sub-queries are DISTINCT, in which case you can translate to Union and leave off the DISTINCT.

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

Applying these rules to your SQL query, you get:

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:

The lambda translation is tricky, but the conversion of LEFT JOIN to GroupJoin...SelectMany is what is needed:

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 });

这篇关于从SQL到LINQ的多个联接，计数和左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！