title: Linq and Lambda expression for a complex sql query involving joins
body: <p>Using Linq to Entity (Entity Framework) in MVC 3 project. </p>
  
  <p>My model:</p>
  
  <p>Table - Users<br>
  UserID (PK)<br>
  ...</p>
  
  <p>Table - Clients<br>
  ClientID (PK)</p>
  
  <p>Table - PropertyItems<br>
  PropertyItemID (PK)</p>
  
  <p>Table - MemberContactPreference (Contains PropertyItems selected by Users- many to many)<br>
  UserID(FK)<br>
  PropertyItemID(FK)</p>
  
  <p>Table ClientProperties (Contains PropertyItems that belong to Clients - many to many)<br>
  ClientID (FK)<br>
  PropertyItemID (FK)</p>
  
  <p><strong>I want to list all the distinct users that have selected all the properties selected by clients.</strong> </p>
  
  <p>My Approach : </p>
  
  <p>I got a list of all properties for a particular client in</p>
  
  <pre><code>Iqueryable&lt;ClientProperty&gt; clientProperties  = GetClientProperties(ClientID)
  
  Iqueryable&lt;User&gt; UsersMatchingClientProperties = GetAllUsers();
  
  
  
  foreach (ClientProperty property in clientproperties)
  {
  
   UsersMatchingClientProperties = (from uem in UsersMatchingClientProperties
                                    join ucp in GetAllMemberContactPreferences on 
                                    ucp.UserID == uem.UserID
                                    where uem.MemberContactPreferences.SelectMany(      
                                    mcp =&gt; mcp.PropertyItemID == property.PropertyItemID)
                                    select uem).Distinct;
  }
  </code></pre>
  
  <p>It gives the right result only first time. As it doesn't reduce the number of items in UsersMatchingClientProperties with each iteration. actually it replaces the collection with new resultset. I want to filter out this collection with each iteration. </p>
  
  <p>Also, any suggestions to do this in Lambda expression without using Linq. </p>
  
  <p>Thanks </p>