NHibernate: LeftOuterJoin without association

Question

Hi, I have the following structure.

Message (Text)

MessageReading (Message, User)

I'd like to load messages with corresponding MessageReading if it exists. I can achieve that with HQL:

var query = session.CreateSQLQuery( 
@"SELECT {msg.*}, {mr.*} 
  FROM Message msg 
  LEFT OUTER JOIN MessageReading mr ON (mr.Message_Id = msg.Id  AND 
  mr.User_Id = :user)") 
.AddEntity("msg", typeof(Message)) 
.AddEntity("mr", typeof(MessageReading)) 
.SetParameter("user", user.Id);

Is there a way to do the same with Criteria API? (I need it because I use Rhino Security to add authorization filtering to my queries)

Answer 1

Firstly, in your example you are performing a SQL query using the underlying dialect of your RDBMS, not an HQL query. This is why you are able to do the join without an association mapping in your NHibernate configuration. To perform an HQL query you would use the session.CreateQuery method.

To perform this query using HQL or Criteria API you need a one-to-many <set name="Readings" ...> mapping from the Message class to the MessageReading class. The Message class should have a Readings property of type ISet<MessageReading>. With this mapping in place you will be able to use the Criteria API like so:

var criteria = session.CreateCriteria<Message>);
criteria.CreateAlias("Readings", "mr", JoinType.LeftOuterJoin);
criteria.Add(Restrictions.Eq("User", user));
criteria.SetResultTransformer(Transformers.DistinctRootEntity);

var results = criteria.List<Message>();

This will return a list of Message objects and populate the Readings property on each object. The DistinctRootEntity transformer ensures that only one copy of each Message object is returned even if there are multiple MessageReadings.