NHibernate条件:"From"子句后的子查询 [英] NHibernate Criteria: Subquery After 'From' Clause

问题描述

如何使用Criteria编写以下SQL:

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 

推荐答案

要使用NHibernate，产生如下查询:

To use NHibernate, to produce query like this:

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

我们必须选择:

1. 将子选择映射为实体.
2. 创建原始SQL查询

第一个选项意味着创建一些view，并将其映射为实体.如果我们不喜欢(或无法创建)视图，则可以使用NHibernate映射功能，即元素 <subselect> :

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>
   ...

第二个选项是关于使用NHibernate API创建本机/原始SQL:

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.原生SQL中的查询

这篇关于NHibernate条件:"From"子句后的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！