Can something like this be done in NHibernate?

Question

Not sure how to do this in Fluent NHibernate (preferably) or with Criteria.

SELECT foo.One, foo.Two, bar.One
FROM Bar bar
RIGHT JOIN ( SELECT 
             One, Two 
             WHERE One LIKE '%number%'
             ORDER BY Id ASC
             OFFSET 0 ROWS 
             FETCH NEXT 25 ROWS ONLY 
           ) Foo
ON Bar.Foo_Id = Foo.Id
WHERE Bar.Two IN (...)

Answer 1

NHibernate criteria, represents a language for querying on top of the Entities, i.e. on top of the mapping. That means, that we (users of NHiernate criteria) can effect the final SQL SELECT statement in these sections:

• SELECT (either all mapped columns of the entity, or projections - just a specific columns)
• WHERE (any kind of restriction is available, including custom/DB specific statements)
• GROUP BY
• ORDER BY
• HAVING

The fact, that we do query Entities (i.e. how we mapped <class name="MyEntity" table="MyTable"), we can effect the FROM only inside of the mapping.

About the JOIN: we can also decide what will be joined (which mapped reference) and even the join type (LEFT, INNER...). But the ON statement must contain NHibernate generated part, coming from the mapping.

So, the above was namely stated just to get to final statement:

If we want to have a SELECT as part of a FROM/JOIN ... that SELECT must be mapped as some Entity

<class name="MyEntity"... >
   <subselect>
    SELECT ...
    FROM ...
   </subselect>

Read more here:

NHibernate Criteria: Subquery After 'From' Clause