NHibernate Criteria: Subquery After 'From' Clause

Question

How can I write the following SQL using Criteria:

select b.Name as Batch, b.Capacity as Capecity, 
       a.tStudent as Admit, (b.Capacity-a.tStudent) as Availabe 
from ( 
 SELECT count(Id) as tStudent, BatchId FROM [dbo].[Student] group by BatchId) as a 
left join [dbo].[Batch] as b on a.BatchId = b.Id 

Answer 1

To use NHibernate, to produce query like this:

SELECT ... 
FROM 
( 
 SELECT ...
) AS a 
..

We have to options:

1. Map the sub-select as an Entity.
2. create raw SQL query

The first option would mean to create some view, and map it as an Entity. If we do not like view (or cannot create it) we can use the power of NHibernate mapping, element <subselect>:

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

The second option is about using NHibernate API to create native/raw SQL:

session.CreateSQLQuery("SELECT ")

It is not profiting from mapping, but we can still apply parameters, and profit from transformation...

9.3.5. Queries in native SQL