NHibernate QueryOver without selection grouped by column

Question

Having a query such as:

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 selects both SomeGroupByProperty and the maximum of MaxPerGroupProperty . Is it possible to group it by SomeGroupByProperty , but only select the maximum of MaxPerGroupProperty ? This is used to use the result of the subquery with the content in the parent query.

Answer 1

This is an open issue in NHibernate jira (query criteria): 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 as pretty as using the properties of an object, but it works.

Answer 2

I know that I am showing an alternative without giving an answer. But in case we need to use such a subquery in another query, we can:

• filter the internal request with some tuning from the external request
• use Exists (which can return as many columns as we want)

So, we need to first define Alias (an external request) and do some filtering: Having a request like:

 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 moreover, in the external (root) request we can use it as follows:

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

This is a simplified solution with a WHERE clause. Most likely, we will need to apply this filter in the HAVING clause. Here is a detailed example of how:

Request link HasMany

So, the subquery will 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) )) 

Full example here

Answer 3

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

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