LINQ to Nhibernate duplicates joins

Question

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?

Answer 1

Added issue on nhibernate jira

Answer 2

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)

Answer 3

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);