title: Linq, emulating joins, and the Include method
body: <p>I'm looking into an issue that is related to...
  <a href="https://stackoverflow.com/questions/416847/join-and-include-in-entity-framework">Join and Include in Entity Framework</a></p>
  
  <p>Basically the following query returns the list of "Property" objects the current user has permissions ( ACLs ) to view.</p>
  
  <pre><code>IQueryable&lt;Property&gt; currPropList 
                  = from p in ve.Property
                                  .Include("phyAddress")
                                  .Include("Contact")
                    from a in ve.ACLs
                    from u in ve.Account
                    from gj in ve.ObjectGroupJoin
                    where u.username == currUsername              // The username
                          &amp;&amp; (a.Account.id == u.id                // The ACLs
                              &amp;&amp; a.objType == (int)ObjectType.Group)
                          &amp;&amp; (gj.ObjectGroup.id == a.objId        // The groups
                              &amp;&amp; gj.objId == p.id)                // The properties
                    select p;
  </code></pre>
  
  <p>The query returns the correct list of properties and in large works fine.</p>
  
  <p>But the "Include" calls in the linq query above does not load the objects.  If I call "Load()" explicitly after the LINQ query then the objects load.</p>
  
  <p>The <a href="https://stackoverflow.com/questions/416847/join-and-include-in-entity-framework">related SO question</a> suggested that there may be a conflict between the "Include" call and the where clause.  How can that be the case?</p>
  
  <p>But at any rate, how can I restructure this query to load the "phyAddress" and "Contract" members?  Specifically, I'd only like to load the members on <em>returned</em> objects, not all the "phyAddress" and "Contact" objects in the database.</p>
  
  <p>Thanks.</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I've tracked down the issue to the use of multiple <em>from</em> clauses</p>
  
  <p>This works...</p>
  
  <pre><code>IQueryable&lt;Property&gt; currPropList 
              = from p in ve.Property
                              .Include("phyAddress")
                select p;
  </code></pre>
  
  <p>And the "phyAddress" member is loaded.</p>
  
  <p>But this doesn't work...</p>
  
  <pre><code>IQueryable&lt;Property&gt; currPropList 
              = from p in ve.Property
                              .Include("phyAddress")
                from a in ve.ACLs
                select p;
  </code></pre>
  
  <p>Basically the <em>Include</em> call is ignored when there are multiple <em>from</em> clauses.  Does anyone know of a work around for this?</p>
  
  <p><strong>Edit 2</strong></p>
  
  <p>One workaround is to cast the <em>IQueryable</em> result as a <em>ObjectQuery</em> and get the include off of that.  But I would like to prevent the second roundtrip to the database I am assuming this causes.</p>
  
  <p>Eg.  This works....</p>
  
  <pre><code>IQueryable&lt;Property&gt; currPropList 
          = ((from p in ve.Property
            from a in ve.ACLs
            select p) as ObjectQuery&lt;Property&gt;).Include("phyAddress");
  </code></pre>
  
  <p>Is there a way to do this with only a single query?</p>
  
  <p><strong>Edit 3</strong></p>
  
  <p>No second query because of <em>deferred execution</em> [ <a href="http://blogs.msdn.com/charlie/archive/2007/12/09/deferred-execution.aspx" rel="nofollow noreferrer">http://blogs.msdn.com/charlie/archive/2007/12/09/deferred-execution.aspx</a>.  So edit 2 would be the solution.</p>