title: linq to entities, a where in where clause? (inner where)
body: <p>I have a table with a one to many mapping to a table that has a many to many mapping to another table. I'd like to do the following:</p>
  
  <pre><code>var results = context.main_link_table
                       .Where(l =&gt; l.some_table.RandomProperty == "myValue" &amp;&amp;
                              l.some_table.many_to_many_table
                               .Where(m =&gt; m.RandomProperty == "myValue"));
  </code></pre>
  
  <p>How can I achieve this? The first part will work but when trying it without the 'inner WHERE', I can't access the many_to_many_table's properties, but the "inner where" obviously won't compile. I basically want to achieve something like the following SQL query:</p>
  
  <pre><code>SELECT * from main_link_table
  INNER JOIN some_table AS t1 ON t1.association = main_link_table.association
  INNER JOIN many_to_many_table AS t2 ON t2.association = some_table.association
  WHERE t1.RandomProperty = 'MyValue' AND t2.RandomProperty = 'MyValue'
  </code></pre>
  
  <p>It's seemingly simple but I can't find a way to achieve it in one single line of linq - using multiple lines to achieve the desired effect returns too much results and I end up having to loop through them. I also tried stuff like:</p>
  
  <pre><code>var results = main_link_tbl.Include("some_table.many_to_many_table")
                             .Where(l =&gt; l.some_table.many_to_many_table.&lt;property&gt;
                                         == "MyValue")
  </code></pre>
  
  <p>But at this point I can't select a property of many_to_many_table unless I add a FirstOrDefault(), which nullifies the effect since it won't search through all the records.</p>
  
  <p>What did work, but requires multiple lines of code and in the background returns too many results in the SQL query built by the linq-to-entities framework:</p>
  
  <pre><code>var results = db.main_link_table.Include("some_table")
                                  .Include("some_table.many_to_many_table")
                                  .Where(s =&gt; s.some_table.RandomProperty 
                                              == "myValue")
                                  .Select(s =&gt; s.some_table);
  
  foreach(var result in results) {
      var match_data = result.Where(s =&gt; s.many_to_many_table.RandomProperty
                                         == "myValue");
  }
  </code></pre>
  
  <p>This piece of code will return all rows inside some_table that match the first Where condition and then applies the next Where condition, while I obviously only need a single row where the many_to_many_table.RandomProperty equals myValue.</p>