Execute nHibernate icriteria on result of other query (two distinct queries)

Question

Is there a way to use an ICriteria result as a 'base' for a subsequent criteria query?

For example if I would like to create a query

SELECT department_id, sum(cost) AS total
FROM payment
GROUP BY payment.department_id

storing the result as query0, and then execute the query

SELECT department.name, total
FROM department, query0
JOIN LEFT ON department.id=query0.id
WHERE total > 3

I do not want to have one single huge query executed all at once (which would be the result of creating an ICriteria with subqueries). Note that I have a selection/ restriction on a result of the first query and at the same time including one of its columns in the second query's projection.

The criteria is generated dynamically using strings to identify the classes.

Answer 1

The closest thing I can think of to this is a Common Table Expression (the SQL WITH statement). Unfortunately NHibernate doesn't seem to have any good abstractions for dealing with CTEs, but here is how your query might look in SQL Server:

WITH query0 AS (
    SELECT department_id AS id, sum(cost) AS total
    FROM payment
    GROUP BY payment.department_id
)
SELECT department.name, total
FROM department, query0
WHERE department.id=query0.id
AND total > 3;

SQL Fiddle: http://sqlfiddle.com/#!3/8e6877/7