使用Max()投影的NHibernate条件查询

本文关键字：NHibernate 条件 查询 投影 Max 使用 | 更新日期: 2023-09-27 18:02:50

我必须使用NHibernate来实现这样的东西

DetachedCriteria maxDateQuery = DetachedCriteria.forClass(FtpStatus.class);
ProjectionList proj = Projections.projectionList();
proj.add(Projections.max("datetime"));
proj.add(Projections.groupProperty("connectionid"));
maxDateQuery.setProjection(proj);
Criteria crit = s.createCriteria(FtpStatus.class);
crit.add(Subqueries.propertiesEq(new String[] {"datetime", "connectionid"}, maxDateQuery));
List<FtpStatus> dtlList = crit.list();

(这段代码来自Hibernate条件查询，使用Max()投影键字段并按外主键分组)

我的问题是Nhibernate没有实现"子查询"。propertiesEq" 

crit.add(Subqueries.propertiesEq(new String[] {"datetime", "connectionid"}, maxDateQuery));

你能建议一个解决方法吗?

使用Max()投影的NHibernate条件查询

这里的答案是(谈论NHibernate)使用EXISTS而不是IN。查询将如下所示

// the below outer query, has essential feature
// ALIAS name "outer"
// and this is used here, in the "subquery"
var maxDateQuery = DetachedCriteria.For<MyEntity>("myEntity")
    .SetProjection(
        Projections.ProjectionList()
        .Add(Projections.GroupProperty("MyId"))
        .Add(Projections.Max("MyDate"))
        )
        .Add(Restrictions.EqProperty(
        Projections.Max("MyDate"),
        Projections.Property("outer.MyDate")) // compare inner MAX with outer current
        )
        .Add(Restrictions.EqProperty("MyId", "outer.MyId")) // inner and outer must fit
    ;
// here ... the "outer" is essential, because it is used in the subquery
var list = session.CreateCriteria<Contact>("outer")
    .Add(Subqueries.Exists(maxDateQuery))
    ... // e.g. paging
    .List<MyEntity>();

将创建如下查询:

FROM [MyEntityTable] outer
WHERE exists 
(
  SELECT  myEntity.MyId
    , max(myEntity.MyDate) 
   FROM [MyEntityTable] myEntity
   WHERE    myEntity.MyId         = outer.MyId   // matching ID
   GROUP BY myEntity.MyId
   HAVING   max(myEntity.MyDate)  = outer.MyDate // matching max and current date
)

在QueryOver语法中可以找到相同类型的查询(甚至更复杂):

  • 查询HasMany参考
相关文章：