NHibernate QueryOver group by without selecting the grouped by column

Question

Having a query like the following:

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

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.

Answer 1

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

You can do it like this though

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.

Answer 2

I know, that I am showing alternative, not giving the answer. But in case we need to use such a sub-query in another query, we can:

• filter inner query with some setting from the outer query
• use Exists (which could return as many columns as we want)

So, we would need to define Alias (outer query) first and do some filtering: Having a query like the following:

SomeOtherEntity otherEntity = null; // alias of the outer query 
var subquery = QueryOver
    .QueryOver.Of()<SomeEntity>()
    .Where(_ => _.SomeOtherEntity.Id == someId)
    .Where(_ => _.SomeOtherEntity.Id == otherEntity.Id) // here we consume outer alias
    .SelectList(list => list
        .SelectGroup(x => x.SomeGroupByProperty)
        .SelectMax(x => x.MaxPerGroupProperty)
    );

And later, in outer (root) query we can use it like this:

var query = session
    .QueryOver(() => otherEntity)
    .WithSubquery
    .WhereExists(subquery);

This is super simplified solution, with WHERE clause. We would most likely need to apply that filter in the HAVING clause. Here is a detailed example how to:

Query on HasMany reference

So, the subquery would look like this:

// this WHERE
// .Where(_ => _.SomeOtherEntity.Id == otherEntity.Id) // here we consume 
// into this HAVING
.Where(Restrictions.EqProperty(
    Projections.Max<SomeOtherEntity >(x => x.MaxPerGroupProperty),
    Projections.Property(() => otherEntity.GroupProperty)
))

The complete example here

Answer 3

You can do it with your own projection, which is similar to NHibernate ProjectionList with slightly different implementation of ToSqlString, GetTypes and GetTypedValues methods.

public static class CustomProjections
{
    public static ProjectionWithGroupByWithoutSelectProjection GroupByWithoutSelect(IProjection projection, params Expression<Func<object>>[] groupByExpressions)
    {
        var projectionRet = new ProjectionWithGroupByWithoutSelectProjection();
        projectionRet.Add(projection);
        foreach (var groupByExpression in groupByExpressions)
        {
            projectionRet.Add(Projections.Group(groupByExpression));
        }

        return projectionRet;
    }
}

public class ProjectionWithGroupByWithoutSelectProjection : IProjection
{
    // because ProjectionList constructor is protected internal
    private class ProjectionListCustom : ProjectionList
    {
    }

    private readonly ProjectionList _projectionList = new ProjectionListCustom();

    protected internal ProjectionWithGroupByWithoutSelectProjection()
    {
    }

    public ProjectionWithGroupByWithoutSelectProjection Add(IProjection proj)
    {
        _projectionList.Add(proj);
        return this;
    }

    public ProjectionWithGroupByWithoutSelectProjection Add(IProjection projection, string alias)
    {
        _projectionList.Add(projection, alias);
        return this;
    }

    public ProjectionWithGroupByWithoutSelectProjection Add<T>(IProjection projection, Expression<Func<T>> alias)
    {
        _projectionList.Add(projection, alias);
        return this;
    }

    public IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return _projectionList[0].GetTypes(criteria, criteriaQuery);
    }

    public SqlString ToSqlString(ICriteria criteria, int loc, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        return _projectionList[0].ToSqlString(criteria, loc, criteriaQuery, enabledFilters);
    }

    public SqlString ToGroupSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        return _projectionList.ToGroupSqlString(criteria, criteriaQuery, enabledFilters);
    }

    public string[] GetColumnAliases(int position, ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return _projectionList.GetColumnAliases(position, criteria, criteriaQuery);
    }

    public string[] GetColumnAliases(string alias, int position, ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return _projectionList.GetColumnAliases(alias, position, criteria, criteriaQuery);
    }

    public IType[] GetTypes(string alias, ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return _projectionList[0].GetTypes(alias, criteria, criteriaQuery);
    }

    public string[] Aliases => _projectionList.Aliases;

    public override string ToString()
    {
        return _projectionList.ToString();
    }

    public bool IsGrouped => _projectionList.IsGrouped;

    public bool IsAggregate => _projectionList.IsAggregate;

    public TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return _projectionList[0].GetTypedValues(criteria, criteriaQuery);
    }
}