如何使用NHibernate QueryOver实现带排序的分组查询?
用NHibernate QueryOver实现分组排序查询
我需要用NHibernate QueryOver实现如下SQL的功能:
SELECT TOP 10 * from (SELECT aColumn, min(WorkingDay) as d FROM aTable GROUP BY aColumn) as a ORDER BY a.d asc
这段查询的核心是对分组后的结果进行排序。我已经写出了子查询的代码:
var subquery = QueryOver<aTable>.Of(() => alias) .Select( Projections.ProjectionList() .Add(Projections.Group<aTable>(p => p.aColumn).WithAlias(() => alias.aColumn)) .Add(Projections.Min(() => alias.WorkingDay).WithAlias(() => alias.WorkingDay)) );
但不知道怎么构建对应的外层查询,希望能得到帮助。
更新:
在Firo的帮助下,最终的查询代码如下:
aTable t = null; var min = Projections.Min<aTable>(p => p.WorkingDay).WithAlias(() => t.WorkingDay); items = await s.QueryOver<aTable>(() => t) .WhereRestrictionOn(f => f.State).IsIn(states) .Select(Projections.ProjectionList() .Add(Projections.Group<aTable>(p => p.aColumn).WithAlias(() => t.aColumn)) .Add(min) ) .OrderBy(min).Asc .TransformUsing(NHibernate.Transform.Transformers.AliasToBean<aTable>()) .Take(10) .ListAsync();
内容的提问来源于stack exchange,提问作者Peter Larsen 'CPH'
相关产品推荐
相关产品推荐

