NHibernate internal master-detail join on non-id column

Question

Suppose the following display elements:

<class name = "Client" table = "client">

<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 the properties defined in the above mapping

Client PC is uintCustomerId

PK order is uintOrderId

uidCustomerId is a unique key in the Customer table and fk for the customer in the Order table uidCustomerId is binary (16)

The database cannot be changed.

Given the foregoing. How can we request the NHibernate API to bring Customer and related Orders that are after a given date. NOTE. We need to join uidCustomerId, which is not a primary key.

Is this something that can be done using the criteria API? Is this something that can be done using HQL?

Thanks Thassos

Answer 1

I have tried things like what you need. Here is the code that I mixed up (without testing).

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

Here is a good link for NH CreateAlias