Ich habe folgendes Tischmodell:

Ich möchte folgenden SQL-Befehl als Hauptkriterium verwenden:

SELECT * FROM Units WHERE OID IN (SELECT OID FROM Orders WHERE PONumber <> 0 ORDER BY PONumber LIMIT 5)

-> mit anderen Worten: die letzten 5 Bestellungen

BEARBEITEN:

meine Abbildungen

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:

Hier mein Abfragebefehl:

var query = m_hibernateSession.QueryOver<Model.Order>(() => orderAlias)
                    .JoinAlias(() => orderAlias.ID, () => unitAlias, JoinType.InnerJoin)
                    .TransformUsing(Transformers.DistinctRootEntity)
                    .OrderBy(x => x.PONumber).Desc.Take(5);

Vielen Dank