16874

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

Answer1:

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 <a href="http://devlicio.us/blogs/billy_mccafferty/archive/2008/06/16/performing-join-queries-with-nhibernate-createalias.aspx" rel="nofollow">NH CreateAlias</a>

Recommend

  • Intellisense cannot infer type from extention method
  • Meteor throws throwIfSelectorIsNotId exception
  • how to post with curl to REST/JSON service?
  • Max of several columns
  • How to enable mapping the private property of the entity
  • Exception creating JSON with LINQ
  • Doctrine2 bulk import try to work with another entity
  • Syntax error on tokens, AnnotationName expected instead - error on query
  • Regarding starting the threads on a condition
  • Unable to send e-mail through Java
  • NUnit 3.0 TestCase const custom object arguments
  • Undefined references when compiling gSOAP client
  • how does System.Web.HttpRequest::PathInfo work?
  • IE11 throwing “SCRIPT1014: invalid character” where all other browsers work
  • Reading a file into a multidimensional array
  • How to remove a SwiftyJSON element?
  • Graphics.CopyFromScreen [Web application] + The handle is invalid
  • RxJava debounce by arbitrary value
  • pyodbc doesn't report sql server error
  • NSScanner Loop Question
  • Display java JPanel in a JFrame
  • how to adjust image in a panel in Java swing?
  • Encrypt data by using a public key in c# and decrypt data by using a private key in php
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • How to set my toolbar fixed while scrolling android
  • Timeout for blocking function call, i.e., how to stop waiting for user input after X seconds?
  • AT Commands to Send SMS not working in Windows 8.1
  • Linker errors when using intrinsic function via function pointer
  • Windows forms listbox.selecteditem displaying “System.Data.DataRowView” instead of actual value
  • Proper folder structure for lots of source files
  • How to set the response of a form post action to a iframe source?
  • Change div Background jquery
  • How does Linux kernel interrupt the application?
  • Qt: Run a script BEFORE make
  • LevelDB C iterator
  • apache spark aggregate function using min value
  • Sorting a 2D array using the second column C++
  • reshape alternating columns in less time and using less memory
  • How can i traverse a binary tree from right to left in java?
  • How do I use LINQ to get all the Items that have a particular SubItem?