nhibernate criteria for selecting from different tables

I have following table model:

I want following SQL command as nhibernate criteria:

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

-> in other words: the 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 my 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

Answers


If you want the last five orders I would approach the problem with a slightly different query

Select * 
From order o join Units U on O.OID = U.OID
Order by O.PONumber limit 5

in nhibernate would be something like that (not tested)

Order orderAlias = null;
Unit unitAlias = null;
var query = session.QueryOver<Order>(() => orderAlias)
   .JoinAlias(() => orderAlias.Units, () => unitAlias, JoinType.InnerJoin)
   //.TransformUsing(Transformers.DistinctRootEntity) if you have duplicates
   .OrderBy(x => x.PONumber).Desc.Take(5);

UPDATE

You could also just load Order entity. NHibernate will load automatically all units rows, based on your mapping info (lazy/eager loading or joins).

var query = session.QueryOver<Order>().OrderBy(x => x.PONumber).Desc.Take(5);

Need Your Help

DJango: redirect form data, or pass form data to another view

python django forms django-forms

I have a form that needs to process some data in my django app, before the form data is passed to another app.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.