如何使用NHibernate表示加入分组的子查询? [英] How can I express joining to a grouped subquery using NHibernate?

问题描述

我试图使用NHibernate的Criteria API表示SQL查询，但由于遇到问题，因为我正在以数据库为中心的方式进行思考，而NHibernate以对象为中心.

I'm trying to express a SQL query using NHibernate's Criteria API, and I'm running into difficulty because I'm thinking in a database-centric way while NHibernate is object-centric.

SQL(效果很好):

SQL (works great):

select outerT.id, outerT.col1, outerT.col2, outerT.col3
from tbl outerT
inner join
    (select max(innerT.id)
     from tbl innerT
     group by innerT.col1) grpT
on outerT.id = grpT.id

从本质上讲，这是一个针对表本身的子集的表自联接.我想我可以尝试将自连接变成一个限制:

Essentially, this is a self-join of a table against a subset of itself. I suppose I could try turning the self-join into a restriction:

select outerT.id, outerT.col1, outerT.col2, outerT.col3
from tbl outerT
where outerT.id in (select max(innerT.id) from tbl innerT group by innerT.col1)

但是我也不知道如何使用NHibernate来表达这一点.我正在与DetachedCriteria的ProjectionList作战，并且想在按col1分组时仅选择max(id).

But I'm not sure how to express that using NHibernate either; I'm fighting with the DetachedCriteria's ProjectionList and wanting to select only max(id) while grouping by col1.

非常感谢您的建议！

推荐答案

我不知道该将其发布为新答案还是将其添加为对原始问题的评论，但我认为我已经解决了这个线程中有一个类似的问题:

I don't know if I should post this as a new answer or to add it as a comment on the original question, but I think I've resolved a similar problem in this thread:

在NHibernate中选择子查询Critieria API

这篇关于如何使用NHibernate表示加入分组的子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！