使用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屋！