NHibernate 2.1:对具有别名(ICriteria)的子查询的LEFT JOIN

本文介绍了NHibernate 2.1:对具有别名(ICriteria)的子查询的LEFT JOIN的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我基本上是在尝试使用NHibernate ICriteria接口创建此查询:

I am basically trying to create this query with NHibernate ICriteria interface:

SomeTable 1:n AnotherTable

SomeTable 1:n AnotherTable

SomeTable 具有列:PrimaryKey，NonAggregateColumn AnotherTable 具有以下列:主键，外键，AnotherNonAggregate，YetAnotherNonAggregate

SomeTable has columns: PrimaryKey, NonAggregateColumn AnotherTable has columns: PrimaryKey, ForeignKey, AnotherNonAggregate, YetAnotherNonAggregate

SELECT table1.NonAggregateColumn, subquery.SubQueryAggregate1, subquery.SubQueryAggregate2 FROM SomeTable AS table1 LEFT JOIN ( SELECT table2.ForeignKey, COUNT(table2.AnotherNonAggregate) AS SubQueryAggregate1, AVG(table2.YetAnotherNonAggregate) AS SubQueryAggregate2 FROM AnotherTable AS table2 GROUP BY (table2.ForeignKey) ) AS subquery ON subquery.ForeignKey = table1.PrimaryKey

很明显，使用Projection子查询不是很有效，因为SQL必须扫描该表两次(每个聚合一个投影子查询).

It is clear that using Projection subquery is not very efficient, since SQL has to scan the table twice (one projection subquery per aggregate).

使用多个GROUP BY也不是很有效.

Using multiple GROUP BYs is not efficient as well.

有解决方案吗?到目前为止，我一直在使用原始SQL，但是对于复杂的报表，这已经变得笨拙了.

Is there a solution for this ? So far I've been resorting to using raw SQL but this is getting unwieldy for complex reports.

推荐答案

不幸的是，条件有所限制.

Unfortunately, Criteria is a bit restricted.

尝试一下:

session.CreateCriteria(typeof(SomeTable), "st") .SetProjection( Projections.ProjectionList() .Add(Projections.GroupProperty("st.id")) .Add(Projections.GroupProperty("st.NonAggregateColumn")) .Add(Projections.RowCount(), "rowcount") .Add(Projections.Avg("at.YetAnotherNonAggregate"), "avg")); .CreateCriteria( "st.OtherTables", "at", JoinType.InnerJoin) .List<object[]>();

您可能需要玩一些，这更多的是猜测.这样也可能是不可能的.

You probably need to play around a bit, it's more of a guess. It also might be impossible this way.

它应该产生如下内容:

select st.id, st.NonAggregateColumn, count() as "rowcount", avg(at.YetAnotherNonAggregate) as "avg" from SomeTable st inner join AnotherTable at on ... group by st.id, st.NonAggregateColumn

通常:

• 您可以使用DetachedCriteria进行子查询.参见文档以获取更多详细信息.
• 您不能在where子句中使用Criteria和filter来制作笛卡尔乘积. (仅适用于HQL.)
• 不能将子查询添加到from子句中(因为这将导致笛卡尔积).您只能将它们放在where子句中(in，exists等)
• 您可能以AnotherTable开头并导航到SomeTable.这可能是替代解决方案.

• You can make subqueries using DetachedCriteria. See the docs for more details.
• You can't make a cartesian product with Criteria and filter in the where clause. (This only works with HQL).
• Subqueries can not be added to the from clause (because that would result in a cartesian product). You can only put them to the where clause (in, exists etc.)
• You could probably start with AnotherTable and navigate to SomeTable. This might be a alternative solution.