使用 NHibernate 加入子查询 [英] Join a Subquery with NHibernate

问题描述

是否可以在 Criteria 或 QueryOver (NHibernate 3.1) 中执行以下查询?

Is it possible to perform the following query in Criteria or QueryOver (NHibernate 3.1)?

SELECT
 C.CustomerID, C.CustomerName,
 C.CustomerType, C.Address1, C.City,
 C.State, S.TotalSales
FROM
 Customers C
INNER JOIN
 (SELECT
    CustomerID, SUM(Sales) as TotalSales
  FROM
    Sales
  GROUP BY
    CustomerID) S
ON
 C.CustomerID = S.CustomerID

有一个类似的问题，但它已经很老了，从未得到回答.也许随着 NH 团队最近的重大更新，这可以得到解答！NHibernate 2.1:在带有别名的子查询上左连接 (ICriteria)

There was a similar question but it's quite old and was never answered. Maybe with the recent major updates from the NH team this can be answered! NHibernate 2.1: LEFT JOIN on SubQuery with Alias (ICriteria)

谢谢

推荐答案

如果对象模型中 Customer 和 Sales 之间没有关系，那么您无法使用我能想到的 NH2.1 中的任何查询方法将这两个对象连接在一起的.

If there is no relationship between Customer and Sales in the object model then you cannot join the two object together using any query methods in NH2.1 that I can think of.

你也不能像你的例子那样加入不相关实体的子查询.

Also you cannot join subqueries of unrelated entities, like in your example.

但是，您可以在 NH2.1 中执行此操作，这将得到类似的结果.

You can however do this in NH2.1 which will give you similar results.

var customers = session.CreateCriteria<Customer>().Future<Customer>() //Get all Customers
var salesTotals = session.CreateCriteria<Sales>()
     .SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("CustomerId"), "CustomerID")
        .Add(Projections.Sum("Sales"),"SalesTotal")
      )
   .SetResultTransformer(
        new AliasToBeanResultTransformer(typeof(SalesByCustomerDTO))
   ).Future<SalesByCustomerDTO>().List()

这将对服务器进行一次往返，发出两个查询，一个针对所有客户，另一个针对带有 customerid 的销售聚合.

This will do one round trip to the server issuing two queries, one for all customers and one for a aggregate of sales with the customerid.

然后您可以使用 LINQ 将两个结果集连接到内存中.

Then you can join the two result sets in memory using LINQ.

这篇关于使用 NHibernate 加入子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！