使用 NHibernate 在联接中使用子选择
如果可能的话，我想在 NHibernate - Detached Criteria 中编写以下 SQl。
select * from parent
INNER JOIN child on parent.id=child.parentid
INNER JOIN
(select ChildID, MAX(ChildDate) MaxChildDate from child group by ChildID) max
ON child.childid, child.ChildDate=max.MaxChildDate
这给了我每个父母中最新的孩子。
我可以在Critera中编写子查询，但无法执行ChildID和MaxDate的双重链接。
I would like to write the following SQl in NHibernate - Detached Criteria if possible.
select * from parent
INNER JOIN child on parent.id=child.parentid
INNER JOIN
(select ChildID, MAX(ChildDate) MaxChildDate from child group by ChildID) max
ON child.childid, child.ChildDate=max.MaxChildDate
This gives me the latest child in every paret.
I can write the sub-query in Critera but cannot perform the double link of ChildID and MaxDate.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
谢谢，连接并不像示例那么简单，并且使用了很多表 - 如果在基于 SQL 集的设置中完成则很好，但是会返回大量数据以在 linq 中对其进行过滤。
不幸的是，速度必须战胜设计，因此我创建了一个具有复杂联接的视图，并将其用作主域对象上的字典表。不过还是感谢您的帮助。
干杯
斯图
Thanks, the join wasn't quite as simple as the example and there were a lot of tables being used - fine if done in a SQL set based setup but there would have been a ridiculous amount of data coming back to filter it in linq.
Unfortunately speed had to win over design so I created a view with the complex join in and used that as a table for a dictionary on the primary domain object. Thanks for all your help though.
Cheers
Stu
我会在父级上创建一个排序列表属性（按 ChildDate 排序），因此当您到达最后一个（可能带有 linq 扩展）时，您将获得所需的属性。无需创建复杂的连接。
I would create a sorted list property on the parent (sorted by ChildDate), so when you get last (with linq extensions possibly), you get the one you need. No need to create complicate joins.
我不能说我实际上知道执行您所要求的操作的方法，NHibernate 返回您正在查询的实际对象，在这种情况下它将返回父对象的列表/集。然后，该父对象将附加一个子对象的集合。据我所知，两次链接到该数据并提取不同信息的概念在 NHibernate 中不起作用。
如果您需要直接在父对象上使用它，我建议在父对象上创建另一个变量，该变量通过 Parent.hbm.xml 文件中的“where”属性预先过滤这些详细信息。如果您不想这样做，我建议使用 LINQ 或类似的东西来为集合中的每个 Parent 提取最新的 Child 对象。
您可以在 Parent.hbm.xml 文件中找到的“where”属性的示例如下：
这将仅返回与集合的 where 查询相匹配的子对象。
您可以在此处找到有关此内容的文档。尽管这是 Hibernate 参考而不是 NHibernate，但它在大多数方面几乎是相同的。
祝你好运！
I can't say I actually know a way of doing what you are asking, NHibernate returns the actual object that you're querying, in this case it would return a list/set of Parent objects. This Parent object would then have a collection of Child objects attached to it. The concept of linking to this data twice and pulling different information doesn't work in NHibernate as far as I am aware.
If you require this to be available directly on the Parent, I would suggest creating another variable on the Parent object which has these details pre-filtered via the "where" attribute in your Parent.hbm.xml file. If you don't wish to do this I would suggest using LINQ or something similar to pull out the latest Child object for each Parent in your collection.
An example of the "where" attribute that you would find in your Parent.hbm.xml file is:
This will only ever return the Child objects which match the where query of the collection.
You can find documentation on this here. Although this is the Hibernate reference and not NHibernate it is almost identical in most areas.
Good luck!