Join a Subquery with NHibernate

Question

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

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

Answer 1

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

Answer 2

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.