LINQ to Nhibernate duplicates joins
I have a query like this
var orderedQueryable = this.participationRequests
.Fetch(x => x.CommunityEvent)
.Fetch(x => x.CommunityMember)
.ThenFetch(x => x.User)
.Where(x => x.CommunityMember.Community.Id == communityId)
.OrderBy(x => x.CreateDate);
The where clause needs to be after fetch due to this bug. The problem is that thouse Fetch calls issue additional joins. In SQL query looks like the following:
select *
from ParticipationRequests participat0_
left outer join CommunityEvents communitye1_
on participat0_.CommunityEventId = communitye1_.Id
left outer join CommunityMembers communitym2_
on participat0_.CommunityMemberId = communitym2_.Id
left outer join Users user3_
on communitym2_.UserId = user3_.Id
inner join CommunityMembers communitym4_
on participat0_.CommunityMemberId = communitym4_.Id
inner join CommunityMembers communitym5_
on participat0_.CommunityMemberId = communitym5_.Id
inner join Communities community6_
on communitym5_.CommunityId = community6_.Id
where community6_.Id = 2002 /* @p0 */
order by participat0_.CreateDate asc
It does inner join to put a condition on CommunityId and does left outer join to do fetching.
I've found similar question, but my query has different execution plan with and without additional joins.
Is it a bug in LINQ provider? Maybe there is a workaround?
在nhibernate jira上添加了问题
Not exactly sure but remove your where query and instead use a join something on the lines of
join o in x.CommunityMember.Community on communityId equals x.communityMember.Community.Id (my syntax is comp0letely out, but may serve you as a hint)
As Sly mentioned, this is a known issue with Linq to NHibernate.
I was able to work around this by using HQL instead of Linq. Your result would be something like:
CommunityEvent ce = null;
CommunityMember cm = null;
var queryable = this.participationRequests
.JoinAlias(x => x.CommunityEvent, () => ce)
.JoinAlias(x => x.CommunityMember, () => cm)
.Where(() => cm.Community.Id == communityId)
.OrderBy(x => x.CreationDate);
上一篇: 在nHibernate查询中左连接挣扎