title: Getting rid of unnecessary join when using link tables with EF
body: <p>I have this database schema:</p>
  
  <pre><code>User  &lt;--  UserRole  --&gt;  Role
  </code></pre>
  
  <p>The Role table has a few columns but I'm only interested in the primary key and I can get that from UserRole's foreign key so I wouldn't really need to join in the Role table at all, but I can't figure out how to avoid joining to that table with EF.</p>
  
  <p>I tried this code:</p>
  
  <pre><code>context.Users.Where(u =&gt; u.UserId == x).Single().Roles.Select(r =&gt; r.RoleId);
  </code></pre>
  
  <p>This generates two queries. One on the user table and one on a join of UserRole and Role. I can of course reduce it to one query by using .Include("Roles") or SelectMany() but that query would join three tables. Is there a way to get rid of the superfluous join? I want my SQL to resemble this:</p>
  
  <pre><code>SELECT u.*, ur.RoleId
  FROM User u
  LEFT OUTER JOIN UserRole ur on ur.UserId = u.UserId
  WHERE ...
  </code></pre>
  
  <p>In reality I'm using automapper, but I think this example demonstrates the same problem. Roles is a small table so I can live with the performance penalty this time, but it bugs me that I can't get it as efficient as handwritten SQL. Has anyone else run into this before me and figured out a solution?</p>