NHibernate 2.1:在带有别名的子查询上左连接(ICriteria)

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

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

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

SomeTable 1:n 另一个表

SomeTable 1:n AnotherTable

SomeTable 具有列:PrimaryKey、NonAggregateColumnAnotherTable 包含以下列:PrimaryKey、ForeignKey、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

很明显，使用投影子查询效率不高，因为 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.

推荐答案

很遗憾，Criteria 有点受限.

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.

它应该产生这样的东西:

It should produce something like this:

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 和过滤器制作笛卡尔积.(这只适用于 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.