How to inner join a derived table with Hibernate/NHibernate?

(First i'd like to apologize if this is a duplicate, I can't find any good solution for it even though it must be simple)

I'm creating reports from sales-data and need to output some aggregated values along with some non-aggregated/non-grouped ones, like Name.

This is what I'd like to output:

Total profit per employee with User.Name and SUM(Order.Profit) like this:

Name         Profit [+ more aggregated columns]
-------------------
John Doe    | $250
James Smith | $130

This is fairly simple with plain SQL like:

SELECT 
u.Name,
x.TotalProfit
FROM dbo.Users u
INNER JOIN (
    SELECT  o.开发者_运维技巧UserID as UserID, 
    SUM(o.Profit) AS TotalProfit,
    --[..] more aggregated columns
    FROM Orders o
    GROUP BY o.UserID
) x  ON u.ID = x.ProfitToID

I don't want to group by u.Name since it will affect performance (Read more about why at sqlteam.com)

How do I do this with NHibernate?

---

In Hibernate you define the join paths in the mappings. So classes not related in Java with another classes cannot join themselves. Hibernate is an ORM it make sense.

Try creating a SQLquery. Works pretty much in the same way that ordinary HQL queries.

Then you would retrieve an array with the data. Parse it or create a DTO with a constructor that fit.

select a.b.MyDTO(u.Name, x.TotalProfit) from...

class MyDTO{
    String name;
    int totalProfit;
    public MyDTO(String name, int totalProfit) {
        // ...
    }
}

Udo.

---

If you're using fancy SQL like that, just use a native query:

entityManager.createNativeQuery("your query here", SomeDTOClass.class);