Join a Subquery with NHibernate

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,
 开发者_StackOverflowC.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

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)

Thanks

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.

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()

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.

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

My 50 cents -> You need to change your object model. So that a customer contains sales.

Your query when then look like the following, which is far more terse and better object orientated. I dont know how to do the query above though but it defeats the purpose of using an ORM tool.

from Customer c
inner join fetch c.Sales