33

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")

What would the linq statement look like using Entity Framework 4 and C#?

Update:

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;

EDIT: 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;

This produced this 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]) ))

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)

Zachary Scott
  • 20,968
  • 35
  • 123
  • 205

2 Answers2

46

The .Any() extension method typically maps to exists.

David Pfeffer
  • 38,869
  • 30
  • 127
  • 202
  • 1
    That's a very good thing to know. The only thing I found on the Internet so far has been the .Contains() outside of SO answers. I have seen .Any() used but I didn't put two and two together. – Zachary Scott Nov 03 '10 at 19:15
  • 1
    This question ended up showing me code to do that: http://stackoverflow.com/questions/4084102/how-would-i-improve-this-7-line-linq-query-that-acts-as-a-specification – Zachary Scott Nov 03 '10 at 19:16
  • https://www.growingwiththeweb.com/2013/04/use-any-in-your-linq-to-sql-queries.html – George Birbilis Jun 23 '20 at 12:27
0

Have you tried adding your exists conditioning to your 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";

Not sure if that will work, but worth a shot.

AJ.
  • 16,368
  • 20
  • 95
  • 150
  • 1
    No, it won't work. The `on` clause only accepts an `equals` expression – Thomas Levesque Nov 02 '10 at 00:38
  • Looking at your question again, I'm not sure I understand why you're using `exists` in the first place. Why not just add `where B.Name == "Joe" && C.Name == "Kim"`? – AJ. Nov 02 '10 at 00:53
  • Because I am trying to filter an existing query that has nested results. I need to filter by the nested part. The only way I can think to do that is by using a Where exists ( if this is true, get the record). – Zachary Scott Nov 02 '10 at 01:03
  • 2
    You can do complex joins by using an anonymous type; ```join B in context.BEntity on new { A.Key, B.Name } equals new { B.Key, "Joe" }``` – K0D4 Oct 30 '15 at 18:49
  • Yikes @K0D4! That's ugly, but potentially very useful :-) – Auspex Oct 18 '19 at 15:19