NHibernate inner join master-detail on a column other than the id

Question

Assume the following mapping entries:

< class name="Customer" table="customer">

<id name="InternalId" column="uintCustomerId" type="long">
  <generator class="identity" />
</id>

<!-- The BinaryBlob below is a 16 byte array - Think Guid.NewGuid().ToByteArray -->
<property name="CustomerUid" column="uidCustomerId" type="BinaryBlob" not-null="true" /> 
<property name="Name" column="strFullName" type="String" not-null="true" />
<property name="Age" column="intAge" type="System.Int32" not-null="true" />

<set name="Orders" table="order" generic="true" inverse="false" lazy="false" cascade="all">
  <key column="uidCustomerId" />
  <one-to-many class="Order" />
</set>

< class name="Order" table="order">

<id name="InternalId" column="uintOrderId" type="long">
  <generator class="identity" />
</id>

<!-- This BinaryBlob is a 16 byte array - Think Guid.NewGuid().ToByteArray -->
<property name="CustomerUid" column="uidCustomerId" type="BinaryBlob" not-null="true" /> 

<property name="OrderDate" column="datOrderDate" type="System.DateTime" not-null="true" />

So there are 2 classes: Customer - Order with properties as defined in the above mapping

The Customer PK is uintCustomerId

The Order PK is uintOrderId

uidCustomerId is a unique key on the Customer table and the fk to Customer on the Order table uidCustomerId is binary(16)

The database cannot change.

Given the above. How can we query using the NHibernate Criteria API to bring a Customer and his related Orders that are after a given date. NOTE: We need to join on uidCustomerId which is not the primary key.

Is this something that can be done with the Criteria API? Is this something that can be done with HQL?

Thanks, Tasos

Answer 1

I've tried similar stuff as what you required. Here's the code that I mashed up (without testing).

IList customers = sess.CreateCriteria(typeof(Customer))
    .CreateAlias("Orders", "order")  
    .CreateAlias("order.OrderDate", "orderdate")
    .Add( Expression.Ge("orderdate", somedate) )
    .List();

Here's a good reference for NH CreateAlias