NHibernate QueryOver 分组依据，而不选择分组依据列

本文关键字：选择 QueryOver NHibernate | 更新日期: 2023-09-27 17:56:59

具有如下所示的查询：

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？这用于将子查询结果与父查询中的包含一起使用。

NHibernate QueryOver 分组依据，而不选择分组依据列

这是 NHibernate jira（条件查询）中的一个未解决的问题：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();

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

你可以用你自己的投影来做到这一点，这与NHibernate ProjectionList的实现略有不同，ToSqlString、GetTypes和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);
    }
}
我知道

，我正在展示替代方案，而不是给出答案。但是，如果我们需要在另一个查询中使用这样的子查询，我们可以：

  • 使用外部查询中的某些设置筛选内部查询
  • 使用 Exists（它可以返回任意数量的列）

因此，我们需要首先定义Alias（外部查询）并进行一些过滤：具有如下所示的查询：

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

稍后，在外部（根）查询中，我们可以像这样使用它：

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

这是带有 WHERE 子句的超级简化解决方案。我们很可能需要在 HAVING 子句中应用该过滤器。下面是如何操作的详细示例：

查询多引用

因此，子查询将如下所示：

// 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)
))

这里的完整示例

相关文章：