从不同的表中选择的NHibernate标准 [英] nhibernate criteria for selecting from different tables

问题描述

我有以下表模型：

我想下面的SQL命令，NHibernate的标准：

I want following SQL command as nhibernate criteria:

SELECT * FROM所在单位OID IN（SELECT OID FROM订单WHERE
采购单号码<> 0 ORDER BY采购单号码LIMIT 5）

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

- >换句话说：过去的5订单

-> in other words: the last 5 orders

编辑：

我的映射

Unit.hbm.xml

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

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

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：

下面我查询命令

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

Thx

推荐答案

如果你想在过去五年的订单我会用一个稍微不同的查询处理这个问题。

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

在NHibernate的会是这样的（未测试）

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);

更新

您也可以只加载订单实体。 NHibernate的会自动加载所有单位行，根据你的映射信息（懒/预先加载或连接）。

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);

这篇关于从不同的表中选择的NHibernate标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！