开发者

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 nullable

Users

PK: ID

Items

PK: ID

FK: RelatedCategoryID not nullable

Categories 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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜