CODEWITHSUNDEEP
sqlnhibernatecriterianhibernate-criteria
Habibul Morsalin
Habibul Morsalin

Reputation: 180

NHibernate Criteria: Subquery After 'From' Clause

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

Upvotes: 2

Views: 572

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

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

Upvotes: 1

Related Questions