title: Linq query with two many-many relationships
body: <p>I have the following entities set up which I am trying to query:</p>
  
  <ul>
  <li>Store</li>
  <li>StoreCapability. A store can have multiple capabilities (e.g. sells chocolate, is big) - but is not required to have any.</li>
  <li>Offers. An offer can require multiple store capabilities in order to be valid, but no requirements are required.</li>
  </ul>
  
  <p><code>Store -[m2m]- StoreCapability -[m2m]- Offer</code></p>
  
  <p>So there are five tables involved.</p>
  
  <p>I would like to be able to obtain the following using linq:</p>
  
  <ul>
  <li>Given an offer, a list of stores which it is valid for</li>
  <li>Given a store, a list of offers available</li>
  </ul>
  
  <p>Using SQL it would be possible to join from Store, to StoreCapability, to Offer and then group by offer and store and only obtain the stores having count() equal to the number of requirements an offer has. However I don't know where to start with this with Linq, because the many-many tables are hidden away from me by the Entity framework. Please can anyone help with how I can do this? </p>
  
  <p>The SQL could be something similar to this:</p>
  
  <pre><code>SELECT Offers.Id, offers.Name, Stores.Id, Stores.Name FROM Offers
  --join to the capabilities that this offer needs
  LEFT OUTER JOIN StoreCapabilityOffers offerRequirements ON Offers.Id = offerRequirements.Offer_Id
  --join to stores which have capability
  LEFT OUTER JOIN StoreCapabilities ON offerRequirements.StoreCapability_Id = StoreCapabilities.Id
  --join to stores
  LEFT OUTER JOIN StoreStoreCapabilities storeCap ON offerRequirements.StoreCapability_Id = storeCap.StoreCapability_Id
  LEFT OUTER JOIN Stores on storeCap.Store_Id = Stores.Id
  
  GROUP BY Offers.Id, offers.Name, Stores.Id, Stores.Name
  -- get stores who have the right number of capabilities so all requirements are all met
  HAVING COUNT(*) = (
      select COUNT(*) from StoreCapabilityOffers x where x.Offer_Id = Offers.Id
  )
  </code></pre>
  
  <p>Entities below:</p>
  
  <pre><code>public class Store
  {
    public int Id { get; set; }
    public virtual ICollection&lt;StoreCapability&gt; Capabilities { get; set; }
  }
  
  public class StoreCapability
  {
    public int Id { get; set; }
    public virtual ICollection&lt;Store&gt; Stores { get; set; }
    public virtual ICollection&lt;Offer&gt; Offers { get; set; }
  }
  
  public class Offer
  {
    public int Id { get; set; }
    public virtual ICollection&lt;StoreCapability&gt; StoreCapabilityRequirements { get; set; }
  }
  </code></pre>