title: LINQ AssociateWith for data context generating LEFT JOIN instead of INNER JOIN
body: <p>I'm trying to create an "AssociateWith" data load option for my context to filter rows that the current user has access to. My simple data model is as such:</p>
  
  <p><strong>Tables</strong><br>
  UserPermissions<br>
  PK: ID<br>
  FK: RelatedUserID nullable<br>
  FK: RelatedItemID not nullable  </p>
  
  <p>Users<br>
  PK: ID</p>
  
  <p>Items<br>
  PK: ID<br>
  FK: RelatedCategoryID not nullable  </p>
  
  <p>Categories
  PK: ID</p>
  
  <p>On my datacontext, I'm trying to do something like:</p>
  
  <pre><code>//dbContext is my LINQ context 
  //current_user_id is the id of my current logged in user 
  var dlo = new DataLoadOptions();
  //issue-&gt;UsersPermissions is one-&gt;many relationship
  // if I omit this LoadWith line, no associatewith filter
  // is created in the generated sql code
  dlo.LoadWith&lt;Items&gt;(i=&gt;i.UserPermissions); 
  
  dlo.AssociateWith&lt;Items&gt;(i=&gt;
  i.UserPermissions.Where(p=&gt;
  p.RelatedUserID.HasValue &amp;&amp; p.RelatedUserID.Value == current_users_id));
  dbContext.LoadOptions = dlo;
  </code></pre>
  
  <p>Everything seems reasonable to me, but the query generated in SQL looks like this:  </p>
  
  <pre><code>SELECT [t0].[ID], [t1].[ID] AS [ID2], [t1].[RelatedUserID], [t1].[RelatedItemID] 
  (SELECT COUNT(*)
  FROM [dbo].[UserPermissions] AS [t2]
  WHERE (([t2].[RelatedUserID] = @p0)) AND ([t2].[RelatedItemID] = [t0].[ID])
  ) AS [value]
  FROM [dbo].[Items] AS [t0]
  LEFT OUTER JOIN [dbo].[UserPermissions] AS [t1] ON ([t1].[RelatedUserID] = @p0) 
  AND     
  ([t1].[RelatedItemID] = [t0].[ID])
  ORDER BY [t0].[ID], [t1].[ID]
  </code></pre>
  
  <p>How can I force an INNER JOIN instead of the LEFT OUTER JOIN? Am I modeling my relationship incorrectly on the backend?</p>
  
  <p>Thanks for any help.</p>
  
  <p>MMAS</p>