问题描述
我真的很想做这样的事情:
I really want to do something like this:
Select *
from A join B on A.key = B.key join C on B.key = C.key -- propagated keys
where exists (select null from B where A.key = B.key and B.Name = "Joe") and
exists (select null from C where B.key = C.key and C.Name = "Kim")
使用Entity Framework 4和C#时linq语句会是什么样?
What would the linq statement look like using Entity Framework 4 and C#?
更新:
显然.Contains()将产生"Where Exists"结果.因此,另一种尝试
(我不知道这是否还会编译大声笑):
Apparently .Contains() will produce "Where Exists" results. So, another attempt
(I don't know if this will even compile LOL):
var inner1 = from recordB in B
where recordB.Name = "Joe"
select recordB.key;
var inner2 = from recordC in C
where recordC.Name = "Kim"
select recordC.key;
var result = from recordA in A
where inner1.Contains( recordA.key) &&
inner2.Contains( recordA.key)
select recordA;
哇,这实际上是有效的:
WOW this is what actually worked:
var result = from A in Products
where A.kfield1 == 1 && A.kfield2 == 2 &&
( from B in Btable
where B.otherid == "Joe" && // field I want to select by
B.kfield1 == A.kfield1 &&
B.kfield2 == A.kfield2 // Can keep adding keys here
select A.identifier // unique identity field
).Contains(A.identifier) &&
( from C in Ctable
where C.otherid == "Kim" && // field I want to select by
C.kfield1 == A.kfield1 &&
C.kfield2 == A.kfield2 // Can keep adding keys here
select A.identifier // unique identity field
).Contains(A.identifier)
select A;
这产生了这个SQL:
SELECT [t0].[identifier], [t0].*
FROM [A] AS [t0]
WHERE ([t0].[kfield1] = @p0) AND ([t0].[kfield2] = @p1) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [B] AS [t1]
WHERE ([t0].[identifier] = [t0].[identifier]) AND ([t1].[otherid] = @p2) AND
([t1].[kfield1] = [t0].[kfield1]) AND
([t1].[kfield2] = [t0].[kfield2]))) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [C] AS [t2]
WHERE ([t0].[identifier] = [t0].[identifier]) AND ([t2].[otherid] = @p3) AND
([t2].[kfield1] = [t0].[kfield1]) AND
([t2].[kfiekd2] = [t0].[kfield2]) ))
我想要的是哪个.请注意[t0].[identifier] = [t0].[identifier],它会滤除null值,因为null在SQL中不等于包括自身在内的所有值
Which is what I wanted. Notice the [t0].[identifier] = [t0].[identifier], which filters out null values because null doesn't compare equal to anything including itself (in SQL)
推荐答案
.Any()
扩展方法通常映射到exists
.
The .Any()
extension method typically maps to exists
.
这篇关于如何执行SQL“存在的地方"?在LINQ to Entities中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!