title: Force inner join with many-to-many relationship entity framework
body: <p>I have a many-to-many relationship setup in my database like so:</p>
  
  <pre><code>User
  -------
  Id (PK, Identity)
  First
  Last
  ...various other fields
  
  Skill
  -------
  Id (PK, Identity)
  Description
  
  UserSkill
  -----------
  UserId (PK, FK on User.Id)
  SkillId (PK, FK On Skill.Id)
  </code></pre>
  
  <p>When I run this LINQ query on the DbContext:</p>
  
  <pre><code>from u in Users 
  from s in u.Skills 
  where s.Id == 5 
  select new 
  {
      u.Id,
      s.Description
  })
  </code></pre>
  
  <p>The SQL generated contains all inner joins which is what I want:</p>
  
  <pre><code>SELECT 
  [Extent1].[UserId] AS [UserId], 
  [Extent2].[Description] AS [Description]
  FROM  [dbo].[UserSkill] AS [Extent1]
  INNER JOIN [dbo].[Skill] AS [Extent2] ON [Extent1].[SkillId] = [Extent2].[Id]
  WHERE 5 = [Extent2].[Id]
  </code></pre>
  
  <p>However, when I add a simple extra where clause:</p>
  
  <pre><code>from u in Users 
  from s in u.Skills 
  where s.Id == 5 
      &amp;&amp; u.Last == "test"
  select new 
  {
      u.Id,
      s.Description
  })
  </code></pre>
  
  <p>The SQL generated now uses a sub-query:</p>
  
  <pre><code>[Extent1].[Id] AS [Id], 
  [Filter1].[Description] AS [Description]
  FROM  [dbo].[User] AS [Extent1]
  INNER JOIN  (SELECT [Extent2].[UserId] AS [UserId], [Extent3].[Description] AS [Description]
      FROM  [dbo].[UserSkill] AS [Extent2]
      INNER JOIN [dbo].[Skill] AS [Extent3] ON [Extent3].[Id] = [Extent2].[SkillId]
      WHERE 5 = [Extent3].[Id] ) AS [Filter1] ON [Extent1].[Id] = [Filter1].[UserId]
  WHERE 'test' = [Extent1].[Last]
  </code></pre>
  
  <p>Maybe I am missing something, but I would think EF would just add another join back to the User table for this query and be able to do a where on User.Last instead of doing a sub-query.  Is there any way to force this kind of behavior?  Am I doing something wrong? </p>
  
  <p>Thanks.</p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Cosmin, I am wanting the query to come out like this:</p>
  
  <pre><code>SELECT u.Id, s.Description
  FROM [User] u INNER JOIN
          [UserSkill] us ON u.Id = us.UserId INNER JOIN
          [Skill] s ON us.SkillId = s.Id
  WHERE s.Id = 2 AND u.Last = 'test'
  </code></pre>