多对多关系中的 Criteria API With 子句
NHibernate 3 为 Criteria API 引入了 With 子句。我试图使用它来限制多对多关系上的联接，并且 NHibernate 正在将附加联接限制应用于链接表而不是正确的表。
我在 Player 和 Address 之间存在多对多关系，并且我想通过限制 Address.IsMailingAddress 返回 Player 及其邮寄地址。在 where 子句中限制 IsMailingAddress 不会返回没有邮寄地址（或根本没有地址）的记录，因此我需要在联接中对其进行限制。下面的代码是简化的，我不能使用HQL。
该查询：
var target = session.CreateCriteria<Player>()
.SetProjection(Projections.Property("PlayerId"))
.CreateAlias("Addresses", "ad", JoinType.LeftOuterJoin, Restrictions.Eq("ad.IsMailingAddress", true))
.Add(Restrictions.Eq("LastName", "Anonymous"))
.List();
生成 SQL：
SELECT this_.PlayerId as y0_
FROM dbo.VPlayerExisting this_
left outer join dbo.LinkPlayAddr addresses3_
on this_.PlayerId = addresses3_.PlayerId
and (ad1_.MailingAddressFlag = 1 /* @p0 */)
left outer join dbo.VAddress ad1_
on addresses3_.AddressId = ad1_.AddressId
and (ad1_.MailingAddressFlag = 'Anonymous' /* @p1 */)
WHERE this_.Name_Last = @p2
MailingAddressFlag（映射到 IsMailingAddress 的列）在多对多链接表中受到限制，该链接表不存在，并且 LastName 限制应用于与 Address 表的联接中。确实发出了查询，但数据库服务器当然会引发异常。
这是一个错误，不受支持还是我做错了？
NHibernate 3 introduced the With clause for the Criteria API. I'm trying to use it to restrict the join on a many-to-many relationship and NHibernate is applying the additional join restriction to the linking table rather than the correct table.
I have a many-to-many relationship between Player and Address and I want to return a Player and its mailing address by restricting Address.IsMailingAddress. Restricting IsMailingAddress in the where clause does not return records that do not have a mailing address (or no address at all), so I need to restrict it in the join. The code below is simplified and I can't use HQL.
The query:
var target = session.CreateCriteria<Player>()
.SetProjection(Projections.Property("PlayerId"))
.CreateAlias("Addresses", "ad", JoinType.LeftOuterJoin, Restrictions.Eq("ad.IsMailingAddress", true))
.Add(Restrictions.Eq("LastName", "Anonymous"))
.List();
generates the SQL:
SELECT this_.PlayerId as y0_
FROM dbo.VPlayerExisting this_
left outer join dbo.LinkPlayAddr addresses3_
on this_.PlayerId = addresses3_.PlayerId
and (ad1_.MailingAddressFlag = 1 /* @p0 */)
left outer join dbo.VAddress ad1_
on addresses3_.AddressId = ad1_.AddressId
and (ad1_.MailingAddressFlag = 'Anonymous' /* @p1 */)
WHERE this_.Name_Last = @p2
MailingAddressFlag (the column that's mapped to IsMailingAddress) is restricted in the many-to-many linking table where it doesn't exist and the LastName restriction is applied in the join to the Address table. The query does get issued but of course the database server raises an exception.
Is this a bug, unsupported or am I doing it wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只是内部连接到邮寄地址并限制 IsMailingAddress？
应该带回玩家（其中姓氏为“匿名”）及其邮寄地址，其中他们有一个地址，并且该地址被标记为 IsMailingAddress。
Just inner join to mailing address with the restriction on IsMailingAddress?
Should bring back Players (where LastName is "Anonymous") with their MailingAddress where they have an Address and that address is flagged as IsMailingAddress.
解决方案如下。应该只有一个设置了 IsMailingAddress 的地址，因此，如果有一个或没有一个使用内部联接，则 or 条件允许联接成功。
The solution is below. There should be just one address with IsMailingAddress set, so the or condition allows the join to succeed if there is one or none using an inner join.