NHibernate QueryOver group by 不选择 grouped by 列 [英] NHibernate QueryOver group by without selecting the grouped by column

问题描述

查询如下:

var subquery = SessionFactory.GetCurrentSession()
    .QueryOver<SomeEntity>()
    .Where(_ => _.SomeOtherEntity.Id == someId)
    .SelectList(list => list
        .SelectGroup(x => x.SomeGroupByProperty)
        .SelectMax(x => x.MaxPerGroupProperty))
    .List<dynamic>();

生成的sql同时选择SomeGroupByProperty和MaxPerGroupProperty的最大值.是否可以让它在 SomeGroupByProperty 上分组但只选择 MaxPerGroupProperty 的最大值?这是为了将子查询结果与父查询中的 contains 一起使用.

The generated sql is selecting both SomeGroupByProperty and maximum of MaxPerGroupProperty. Is it possible to get it to group on SomeGroupByProperty but only select maximum of MaxPerGroupProperty? This is for using the subquery result with a contains in parent query.

推荐答案

It's an open issue in NHibernate jira(标准查询):https://nhibernate.jira.com/browse/NH-1426

It's an open issue in NHibernate jira (criteria query): https://nhibernate.jira.com/browse/NH-1426

你可以这样做

var subquery =
    QueryOver.Of<SomeEntity>()
        .Where(_ => _.SomeOtherEntity.Id == someId)
        .Select(
            Projections.ProjectionList()
                .Add(Projections.SqlGroupProjection("max(MaxPerGroupProperty) as maxAlias", "SomeGroupByProperty",
                    new string[] { "maxAlias" }, new IType[] { NHibernate.NHibernateUtil.Int32 })));

var parentQuery = session.QueryOver<SomeEntity2>()
    .WithSubquery.WhereProperty(x => x.MaxPerGroupPropertyReference).In(subquery).List();

不像使用实体属性那么漂亮，但它确实有效.

Not quite as pretty as using the entity properties, but it does work.

这篇关于NHibernate QueryOver group by 不选择 grouped by 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！