0

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

Anastasiosyal
  • 6,494
  • 6
  • 34
  • 40
  • I don't understand. If you already have the mapping, why don't you just get the Customer like normal, than reference the orders via the simple Customer.Orders property? – Jon Adams Jul 16 '09 at 04:41
  • Thanks for your comment Mufasa How would you do the query if you wanted the customer with Orders after a specific Date though? – Anastasiosyal Jul 17 '09 at 08:47

1 Answers1

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

o.k.w
  • 25,490
  • 6
  • 66
  • 63
  • Thanks for the pointer in the right direction - i was not aware of CreateAlias and more importantly how it allows to do joins on arbitrary columns as i saw in the nhforge doc sample via Expression.EqProperty("kt.Name", "mt.Name") IList cats = sess.CreateCriteria(typeof(Cat)) .CreateAlias("Kittens", "kt") .CreateAlias("Mate", "mt") .Add( Expression.EqProperty("kt.Name", "mt.Name") ) .List(); – Anastasiosyal Jul 17 '09 at 16:57