如何在LINQ to Entities中执行SQL "Where Exists“？
如何在LINQ to Entities中执行SQL "Where Exists“？
提问于 2010-11-02 07:42:04
我真的想做这样的事情：
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 Framework4和C#，linq语句会是什么样子？
更新：
显然，.Contains()将产生"Where Exists“结果。所以，另一次尝试
(我甚至不知道这是否会编译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;编辑:哇，这就是实际有效的方法：
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中)
回答 2
Stack Overflow用户
回答已采纳
发布于 2010-11-04 00:12:48
.Any()扩展方法通常映射到exists。
Stack Overflow用户
发布于 2010-11-02 07:49:52
你有没有试过把你的exists条件反射添加到你的joins中？
from a in context.AEntity
Join B in context.BEntity on A.Key equals B.Key && B.Name == "Joe"
Join C in context.CEntity on B.Key equals C.Key && C.Name == "Kim";不确定这是否有效，但值得一试。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4074013
复制相关文章
相似问题
腾讯云开发者
