title: Linq to Entities many to many selection: How to force the generation of a JOIN instead of a subselect clause?
body: <p><img src="https://i.stack.imgur.com/7XyyM.jpg" alt="enter image description here"></p>
  
  <p>Using EF DB first I have two entities (Supplier, Product) that have a many-to-many relationship. Entity Framework does not create an entity for the associated table (SupplierProduct) as the associated table contains only the primary keys of the strong entities.</p>
  
  <p>I have been getting all Suppliers that do not supply a given product with the following query:</p>
  
  <pre><code>var q1 = context.Suppliers.Where(s=&gt;!s.Products.Any(p=&gt;p.Id == 1));
  </code></pre>
  
  <p>The SQL produced uses an EXISTS dependent subquery similar to this:</p>
  
  <pre><code>SELECT *
  FROM Suppliers s
  WHERE NOT EXISTS
    (SELECT 1 
     FROM SupplierProduct sp WHERE sp.SupplierId = s.Id &amp;&amp; sp.ProductId = 1)
  </code></pre>
  
  <p>Is it possible, using Linq to Entities method syntax, to produce a query that uses joins on the associated table instead?</p>
  
  <p>ie:</p>
  
  <pre><code>SELECT DISTINCT s.*
  FROM SupplierProduct sp
  JOIN Supplier s ON s.Id = sp.SupplierId;
  WHERE sp.ProductId != 1
  </code></pre>
  
  <p><strong>Update</strong></p>
  
  <p>As pointed out by JoeEnos my queries above don't do the same thing. The NOT EXISTS subquery is probably the best way to go here. What if I was trying to get all suppliers who <em>did</em> supply a product? I would change my linq to entities query slightly to:</p>
  
  <pre><code>var q1 = context.Suppliers.Where(s =&gt; s.Products.Any(p=&gt;p.Id == 1));
  </code></pre>
  
  <p>And the SQL generated would be:</p>
  
  <pre><code>SELECT *
  FROM Suppliers s
  WHERE EXISTS
    (SELECT 1 
     FROM SupplierProduct sp WHERE sp.SupplierId = s.Id &amp;&amp; sp.ProductId = 1)
  </code></pre>
  
  <p>Which is fine, I get the result I want. However if I was writing SQL in this case I would normally do:</p>
  
  <pre><code>SELECT s.*
  FROM SupplierProduct sp
  JOIN Supplier s ON s.Id = sp.SupplierId;
  WHERE sp.ProductId = 1
  </code></pre>
  
  <p>Can my linq to entities query be changed to produce the above SQL?</p>