title: How to do a SQL "Where Exists" in LINQ to Entities?
body: <p>I really want to do something like this:</p>
  
  <pre><code>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")
  </code></pre>
  
  <p>What would the linq statement look like using Entity Framework 4 and C#?</p>
  
  <p>Update:</p>
  
  <p>Apparently .Contains() will produce "Where Exists" results.  So, another attempt<br/> (I don't know if this will even compile LOL):</p>
  
  <pre><code>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) &amp;&amp;
                     inner2.Contains( recordA.key)
               select recordA;
  </code></pre>
  
  <p>EDIT: WOW this is what actually worked:</p>
  
  <pre><code>var result = from A in Products
               where A.kfield1 == 1 &amp;&amp; A.kfield2 == 2 &amp;&amp;
                     ( from B in Btable
                       where B.otherid == "Joe" &amp;&amp;  // field I want to select by
                             B.kfield1 == A.kfield1 &amp;&amp;     
                             B.kfield2 == A.kfield2 // Can keep adding keys here
                       select A.identifier  // unique identity field 
                     ).Contains(A.identifier) &amp;&amp;
                     ( from C in Ctable
                       where C.otherid == "Kim" &amp;&amp;  // field I want to select by
                             C.kfield1 == A.kfield1 &amp;&amp;     
                             C.kfield2 == A.kfield2 // Can keep adding keys here
                       select A.identifier  // unique identity field 
                     ).Contains(A.identifier)
               select A;
  </code></pre>
  
  <p>This produced this SQL:</p>
  
  <pre><code>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]) ))
  </code></pre>
  
  <p>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)</p>