title: Eager Loading with join
body: <p>I have a query that joins two tables that do not have a defined relationship in Entity Framework, and where the joined table has a one-to-many navigational property to a third table.</p>
  
  <p>There is a one-to-many relationship between msg and job, but there isn't a foreign key, and there isn't an association defined in the .EDMX.</p>
  
  <p>There is a one-to-many relationship between job and lock, and there is an association defined in the .EDMX, so job has a job.locks navigation propertym and lock has a lock.job navigation property.</p>
  
  <p>My original query:</p>
  
  <pre><code>var msgsAndJobs = (
      from m in dbContext.msgs
      join j in dbContext.jobs
          on new { jobid = m.jobid, priority = m.priority }
          equals new { jobid = j.jobid, priority = j.priority }
      where m.msgtype == "EMERGENCY"
      orderby new { m.recvdt }
      select new { m, j }
      );
  </code></pre>
  
  <p>I'm finding that EF is generating one query for the join, then executing a second query to populate the navigational property for each and every record returned by the join.</p>
  
  <p>Microsoft's docs explain the issue: <a href="https://msdn.microsoft.com/en-us/data/jj574232.aspx" rel="noreferrer">https://msdn.microsoft.com/en-us/data/jj574232.aspx</a></p>
  
  <p>So I thought that I'd be able to use an .Include() clause, to eagerly load the records involved. But it doesn't seem to be working:</p>
  
  <p>My new query:</p>
  
  <pre><code>var msgsAndJobs = (
      from m in dbContext.msgs
      join j in dbContext.jobs.Include("locks")
          on new { jobid = m.jobid, priority = m.priority }
          equals new { jobid = j.jobid, priority = j.priority }
      where m.msgtype == "EMERGENCY"
      orderby new { m.recvdt }
      select new { m, j }
      );
  </code></pre>
  
  <p>And it's still generating a query for each job lock.</p>
  
  <p>Any ideas what I'm doing wrong?</p>