具有多个连接、计数和左连接的 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

然而，'我喜欢这个请求的 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 子句顺序翻译每个子句，翻译 monadic 和聚合运算符(DISTINCT、TOP、MIN、MAXcode> 等)转化为应用于整个 LINQ 查询的函数.
3. 使用表别名作为范围变量.使用列别名作为匿名类型字段名称.
4. 对多列使用匿名类型(new { ... })(例如在 groupby 中).
5. 使用 First().field 从 groupby 聚合范围变量中获取非键值.
6. 使用 EF 或 EF Core 时，可能使用 .Include() 将 JOIN 子句转换为导航属性.
7. 否则，作为两个表之间多个 AND ed 相等测试的 JOIN 子句应该被翻译成 equals 每一侧的匿名对象.
8. JOIN 不是所有带有 AND 的相等性测试的条件必须在连接外使用 where 子句处理，或者使用叉积 (from ... from ...) 然后where.如果您正在执行 LEFT JOIN，请在连接范围变量和 DefaultIfEmpty() 调用之间添加一个 lambda Where 子句.
9. LEFT JOIN 是通过使用 into joinvariable 和另一个 from joinvariable 后跟 .DefaultIfEmpty().
10. 将 FROM 子句中的多个表翻译成多个 from 子句.
11. 将FROM T1 CROSS APPLY T2 翻译成两个from 子句，一个用于T1，一个用于T2.
12. 将FROM T1 OUTER APPLY T2翻译成两个from子句，一个用于T1，一个用于T2，但是将 .DefaultIfEmpty() 添加到 T2.
13. 用条件运算符 (?:) 和 null 测试替换 COALESCE.
14. 将 IN 翻译为 .Contains() 并将 NOT IN 翻译为 !...Contains()，对常量列表使用文字数组或数组变量.
15. 将x BETWEEN low AND high 转换为low <= x && x <=高.
16. 将CASE、ISNULL 和IIF 翻译成三元条件运算符?:.
17. SELECT * 必须替换为 select range_variable 或对于连接，一个包含所有范围变量的匿名对象.
18. SELECT 列必须替换为 select new { ... } 创建一个包含所有所需字段或表达式的匿名对象.
19. 对计算出的 SELECT 列的引用可以通过重复表达式或在第一次使用之前使用 let 命名表达式来翻译.
20. 必须使用扩展方法处理正确的FULL OUTER JOIN.
21. 将UNION翻译成Concat，除非两个子查询都是DISTINCT，在这种情况下你可以翻译成Union> 并去掉 DISTINCT.
22. 使用单例GroupBy翻译没有GROUP BY的聚合查询:添加.GroupBy(r => 1)然后翻译Select 中的聚合函数.
23. 日期数学和其他一些canonical 函数以获取 DbFunctions 类(EF Core)、EntityFunctions 的实例类(EF <6)或 DbFunctions 以访问静态方法(EntityFramework 6.x).
24. 使用 (EF Core >= 2) EF.Functions.Like(column, pattern) 或 (EF 6.x) 翻译 SQL LIKE 表达式DbFunctions.Like(column, pattern).

1. Translate subselects as separately declared variables unless they reference columns outside the subselect, in which case use parentheses to create a sub-query.
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. When using EF or EF Core, translate JOIN clauses into navigation properties possibly using .Include().
7. Otherwise JOIN clauses that are multiple ANDed equality tests between the two tables should be translated into anonymous objects on each side of equals.
8. 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.
9. LEFT JOIN is simulated by using into joinvariable and doing another from the joinvariable followed by .DefaultIfEmpty().
10. Translate multiple tables in the FROM clause into multiple from clauses.
11. Translate FROM T1 CROSS APPLY T2 into two from clauses, one for T1 and one for T2.
12. Translate FROM T1 OUTER APPLY T2 into two from clauses, one for T1 and one for T2, but add .DefaultIfEmpty() to T2.
13. Replace COALESCE with the conditional operator (?:)and a null test.
14. Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
15. Translate x BETWEEN low AND high to low <= x && x <= high.
16. Translate CASE, ISNULL and IIF to the ternary conditional operator ?:.
17. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
18. SELECT columns must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
19. References to computed SELECT columns can be translated by repeating the expression or by using let to name the expression before its first use.
20. Proper FULL OUTER JOIN must be handled with an extension method.
21. Translate UNION to Concat unless both sub-queries are DISTINCT, in which case you can translate to Union and leave off the DISTINCT.
22. Translate aggregate queries that have no GROUP BY using a singleton GroupBy: add .GroupBy(r => 1) and then translate the aggregate functions in the Select.
23. Date Math and some other canonical functions can be accessed using EF.Functions to get an instance of the DbFunctions class (EF Core), EntityFunctions class (EF < 6) or DbFunctions to access the static methods (EntityFramework 6.x).
24. Translate SQL LIKE expressions using (EF Core >= 2) EF.Functions.Like(column, pattern) or (EF 6.x) DbFunctions.Like(column, pattern).

将这些规则应用于您的 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屋！