具有多个连接、计数和左连接的 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:
- 将子选择翻译为单独声明的变量除非它们引用子选择之外的列,在这种情况下使用括号创建子查询.
- 按 LINQ 子句顺序翻译每个子句,翻译 monadic 和聚合运算符(
DISTINCT
、TOP
、MIN
、MAX
code> 等)转化为应用于整个 LINQ 查询的函数. - 使用表别名作为范围变量.使用列别名作为匿名类型字段名称.
- 对多列使用匿名类型(
new {
...}
)(例如在groupby
中). - 使用
First().field
从groupby
聚合范围变量中获取非键值. - 使用 EF 或 EF Core 时,可能使用
.Include()
将JOIN
子句转换为导航属性. - 否则,作为两个表之间多个
AND
ed 相等测试的JOIN
子句应该被翻译成equals
每一侧的匿名对象. JOIN
不是所有带有AND
的相等性测试的条件必须在连接外使用where
子句处理,或者使用叉积 (from
...from
...) 然后where
.如果您正在执行LEFT JOIN
,请在连接范围变量和DefaultIfEmpty()
调用之间添加一个 lambdaWhere
子句.LEFT JOIN
是通过使用into
joinvariable 和另一个from
joinvariable来模拟的em> 后跟.DefaultIfEmpty()
.- 将
FROM
子句中的多个表翻译成多个from
子句. - 将
FROM T1 CROSS APPLY T2
翻译成两个from
子句,一个用于T1
,一个用于T2
. - 将
FROM T1 OUTER APPLY T2
翻译成两个from
子句,一个用于T1
,一个用于T2
,但是将.DefaultIfEmpty()
添加到T2
. - 用条件运算符 (
?:
) 和null
测试替换COALESCE
. - 将
IN
翻译为.Contains()
并将NOT IN
翻译为!
...Contains()
,对常量列表使用文字数组或数组变量. - 将x
BETWEEN
lowAND
high 转换为low<=
x&&
x<=
高. - 将
CASE
、ISNULL
和IIF
翻译成三元条件运算符?:
. SELECT *
必须替换为 select range_variable 或对于连接,一个包含所有范围变量的匿名对象.SELECT
列必须替换为select new {
...}
创建一个包含所有所需字段或表达式的匿名对象.- 对计算出的
SELECT
列的引用可以通过重复表达式或在第一次使用之前使用let
命名表达式来翻译. - 必须使用扩展方法处理正确的
FULL OUTER JOIN
. - 将
UNION
翻译成Concat
,除非两个子查询都是DISTINCT
,在这种情况下你可以翻译成Union
> 并去掉DISTINCT
. - 使用单例
GroupBy
翻译没有GROUP BY
的聚合查询:添加.GroupBy(r => 1)
然后翻译Select
中的聚合函数. - 日期数学和其他一些canonical 函数以获取
DbFunctions
类(EF Core)、EntityFunctions
的实例类(EF <6)或DbFunctions
以访问静态方法(EntityFramework 6.x). - 使用 (EF Core >= 2)
EF.Functions.Like(column, pattern)
或 (EF 6.x)翻译 SQL
.LIKE
表达式DbFunctions.Like(column, pattern)
- Translate subselects as separately declared variables unless they reference columns outside the subselect, in which case use parentheses to create a sub-query.
- 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. - Use table aliases as range variables. Use column aliases as anonymous type field names.
- Use anonymous types (
new {
...}
) for multiple columns (e.g. ingroupby
). - Use
First().field
to get non-key values from thegroupby
aggregate range variable. - When using EF or EF Core, translate
JOIN
clauses into navigation properties possibly using.Include()
. - Otherwise
JOIN
clauses that are multipleAND
ed equality tests between the two tables should be translated into anonymous objects on each side ofequals
. JOIN
conditions that aren't all equality tests withAND
must be handled usingwhere
clauses outside the join, or with cross product (from
...from
...) and thenwhere
. If you are doingLEFT JOIN
, add a lambdaWhere
clause between the join range variable and theDefaultIfEmpty()
call.LEFT JOIN
is simulated by usinginto
joinvariable and doing anotherfrom
the joinvariable followed by.DefaultIfEmpty()
.- Translate multiple tables in the
FROM
clause into multiplefrom
clauses. - Translate
FROM T1 CROSS APPLY T2
into twofrom
clauses, one forT1
and one forT2
. - Translate
FROM T1 OUTER APPLY T2
into twofrom
clauses, one forT1
and one forT2
, but add.DefaultIfEmpty()
toT2
. - Replace
COALESCE
with the conditional operator (?:
)and anull
test. - Translate
IN
to.Contains()
andNOT IN
to!
...Contains()
, using literal arrays or array variables for constant lists. - Translate x
BETWEEN
lowAND
high to low<=
x&&
x<=
high. - Translate
CASE
,ISNULL
andIIF
to the ternary conditional operator?:
. SELECT *
must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.SELECT
columns must be replaced withselect new {
...}
creating an anonymous object with all the desired fields or expressions.- References to computed
SELECT
columns can be translated by repeating the expression or by usinglet
to name the expression before its first use. - Proper
FULL OUTER JOIN
must be handled with an extension method. - Translate
UNION
toConcat
unless both sub-queries areDISTINCT
, in which case you can translate toUnion
and leave off theDISTINCT
. - Translate aggregate queries that have no
GROUP BY
using a singletonGroupBy
: add.GroupBy(r => 1)
and then translate the aggregate functions in theSelect
. - Date Math and some other canonical functions can be accessed using
EF.Functions
to get an instance of theDbFunctions
class (EF Core),EntityFunctions
class (EF < 6) orDbFunctions
to access the static methods (EntityFramework 6.x). - 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屋!