LINQ AssociateWith for data context generating LEFT JOIN instead of INNER JOIN
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:
开发者_开发知识库Tables
UserPermissions PK: ID FK: RelatedUserID nullable FK: RelatedItemID not nullableUsers
PK: IDItems
PK: ID FK: RelatedCategoryID not nullableCategories PK: ID
On my datacontext, I'm trying to do something like:
//dbContext is my LINQ context
//current_user_id is the id of my current logged in user
var dlo = new DataLoadOptions();
//issue->UsersPermissions is one->many relationship
// if I omit this LoadWith line, no associatewith filter
// is created in the generated sql code
dlo.LoadWith<Items>(i=>i.UserPermissions);
dlo.AssociateWith<Items>(i=>
i.UserPermissions.Where(p=>
p.RelatedUserID.HasValue && p.RelatedUserID.Value == current_users_id));
dbContext.LoadOptions = dlo;
Everything seems reasonable to me, but the query generated in SQL looks like this:
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]
How can I force an INNER JOIN instead of the LEFT OUTER JOIN? Am I modeling my relationship incorrectly on the backend?
Thanks for any help.
MMAS
LINQ to SQL generates the LEFT OUTER JOIN simply because UserPermissions.RelatedUserID
column is nullable. The underlying LINQ provider just isn't 'smart' enough to further optimize that query to a simple INNER JOIN. I don't think there is anything you can do about this.
精彩评论