NHibernate 2.1 : LEFT JOIN on SubQuery with Alias (ICriteria)

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

SomeTable 1:n AnotherTable

SomeTable 具有以下列:PrimaryKey，NonAggregateColumn
AnotherTable 具有以下列: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必须扫描该表两次(每个聚合一个投影子查询)。

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

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

最佳答案

不幸的是，条件有点受限制。

试试这个:

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[]>();

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

它应该产生如下内容:

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进行子查询。有关更多详细信息，请参见the docs。

您不能在where子句中使用Criteria和filter制作笛卡尔乘积。 (这仅适用于HQL)。

不能将

子查询添加到from子句中(因为这将导致笛卡尔积)。您只能将它们放在where子句中(in，exists等)

您可能以AnotherTable开头，然后导航到SomeTable。这可能是替代解决方案。