c# - nhibernate criteria for selecting from different tables -

i have following table model:

i want following sql command nhibernate criteria:

select * units oid in (select oid orders ponumber <> 0 order ponumber limit 5)

-> in other words: last 5 orders

edit:

my mappings

unit.hbm.xml

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"                   assembly="dcgordersystem"                   namespace="dcgordersystem.model">    <class name="unit" table="units">     <id name="id">       <column name="uid" />       <generator class="native" />     </id>      <property name="division" />     <property name="date" />     <property name="itemordernr" />     <property name="description" />     <property name="amount" />     <property name="price" />     <property name="costcenter" />     <property name="location" />     <property name="devicegroup" />     <property name="comment" />     <property name="distributor" />      <!-- many-to-one mapping: employee -->     <many-to-one name="employeeref" column="eid" cascade="all" />      <!-- many-to-one mapping: order -->     <many-to-one name="orderref" column="oid" cascade="all" />   </class>  </hibernate-mapping> 

order.hbm.xml

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"                   assembly="dcgordersystem"                   namespace="dcgordersystem.model">    <class name="order" table="orders">     <id name="id">       <column name="oid" />       <generator class="native" />     </id>      <property name="locked" />     <property name="ponumber" />      <!-- one-to-many mapping: units -->     <bag name="units" cascade="all" lazy="true">       <key column="oid" />       <one-to-many class="unit" />     </bag>   </class>  </hibernate-mapping> 

employee.hbm.xml

<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"                   assembly="dcgordersystem"                   namespace="dcgordersystem.model">    <class name="employee" table="employees">     <id name="id">       <column name="eid" />       <generator class="native" />     </id>      <property name="name" />      </class>  </hibernate-mapping> 

edit2:

here query command:

var query = m_hibernatesession.queryover<model.order>(() => orderalias)                     .joinalias(() => orderalias.id, () => unitalias, jointype.innerjoin)                     .transformusing(transformers.distinctrootentity)                     .orderby(x => x.ponumber).desc.take(5); 

thx

if want last 5 orders approach problem different query

select *  order o join units u on o.oid = u.oid order o.ponumber limit 5 

in nhibernate (not tested)

order orderalias = null; unit unitalias = null; var query = session.queryover<order>(() => orderalias)    .joinalias(() => orderalias.units, () => unitalias, jointype.innerjoin)    //.transformusing(transformers.distinctrootentity) if have duplicates    .orderby(x => x.ponumber).desc.take(5); 

update

you load order entity. nhibernate load automatically units rows, based on mapping info (lazy/eager loading or joins).

var query = session.queryover<order>().orderby(x => x.ponumber).desc.take(5);