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.

Keynote

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.

Harmonyharmotome

Made few adjustments and it works. Any idea if it is possible to achieve this in a less stringly-typed way? – Knave

stringly-typed... i like it :) Anyway, if you're using queryover I don't know any other way to do this directly. You could use Linq and get maxAlias that way var maxList = session.Query<SomeEntity>().Where(x => x.SomeOtherEntity.Id == someId).GroupBy(x => x.SomeGroupByProperty).Select(x => x.Max(a => a.MaxPerGroupProperty)).ToList(); and then use session.QueryOver<SomeEntity2>().Where(x => x.MaxPerGroupPropertyReference.IsIn(maxList)).List(); but it means an extra roundtrip to the DB to get maxList first.. not ideal – Harmonyharmotome

Yes, 2 roundtrips is the problem we encountered with LINQ and decided to try to find a solution with QueryOver. Thank you for replies – Knave

When working with joins, is there a way to get the (aliased) table name as a string? I'm not really satisfied with running the query once to get the alias name and then putting it into the string. This seems way too fragile and could break with any version upgrade of nhibernate or another component. – Valentijn

NH-1426 - still unfixed nearly 10 years later /cynical ;-) – Hue

Answer 2

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);
    }
}

Allen

Answer 3

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

Bridgman

If only this answer simply worked. The user has a very specific query that should work but doesn't. Answer should be an equivalent something that works - not a rabbit hole. – Hue