如何执行SQL“存在的地方"?在LINQ to Entities中? [英] How to do a SQL "Where Exists" in LINQ to Entities?

查看:69
本文介绍了如何执行SQL“存在的地方"?在LINQ to Entities中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我真的很想做这样的事情:

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中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆