LINQ to Nhibernate 重复联接

发布于 2024-12-15 14:29:09 字数 1548 浏览 5 评论 0原文

我有一个这样的查询

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

由于这个 bug，where 子句需要在 fetch 之后。问题在于 Fetch 调用会发出额外的连接。在 SQL 查询中，如下所示：

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

它执行内连接以在 CommunityId 上添加条件，并执行左外连接以执行获取。

我发现类似的问题，但是我的查询有不同的执行计划，有或没有额外的连接。

这是 LINQ 提供程序中的错误吗？也许有解决方法？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

菊凝晚露 2024-12-22 14:29:09

在 nhibernate jira 上添加了问题

回复收藏 0 原文

執念 2024-12-22 14:29:09

不完全确定，但删除您的 where 查询，而是使用 join o in x.CommunityMember.Community 行上的 join 内容

on communityId equals x.communityMember.Community.Id （我的语法已完全退出，但可能会为您提供提示）

回复收藏 0 原文

橘香 2024-12-22 14:29:09

正如 Sly 提到的，这是 Linq to NHibernate 的一个已知问题。

我可以通过使用 HQL 而不是 Linq 来解决这个问题。你的结果会是这样的：

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

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

回复收藏 0 原文

~没有更多了~