title: Using NHibernate's HQL to make a query with multiple inner joins
body: <p>The problem here consists of translating a statement written in LINQ to SQL syntax into the equivalent for NHibernate. The LINQ to SQL code looks like so:</p>
  
  <pre><code>var whatevervar = from threads in context.THREADs
                            join threadposts in context.THREADPOSTs
                              on threads.thread_id equals threadposts.thread_id
                            join posts1 in context.POSTs
                              on threadposts.post_id equals posts1.post_id
                            join users in context.USERs
                              on posts1.user_id equals users.user_id
                            orderby posts1.post_time
                            where threads.thread_id == int.Parse(id)
                            select new
                            {
                                threads.thread_topic,
                                posts1.post_time,
                                users.user_display_name,
                                users.user_signature,
                                users.user_avatar,
                                posts1.post_body,
                                posts1.post_topic
                            };
  </code></pre>
  
  <p>It's essentially trying to grab a list of posts within a given forum thread. The best I've been able to come up with (with the help of the helpful users of this site) for NHibernate is: </p>
  
  <pre><code>var whatevervar = session.CreateQuery("select t.Thread_topic, p.Post_time, " +
                                                "u.User_display_name, u.User_signature, " +
                                                "u.User_avatar, p.Post_body, p.Post_topic " +
                                                "from THREADPOST tp " +
                                                "inner join tp.Thread_ as t " +
                                                "inner join tp.Post_ as p " +
                                                "inner join p.User_ as u " +
                                                "where tp.Thread_ = :what")
                                                .SetParameter&lt;THREAD&gt;("what", threadid)
                                                .SetResultTransformer(Transformers.AliasToBean(typeof(MyDTO)))
                                                .List&lt;MyDTO&gt;();
  </code></pre>
  
  <p>But that doesn't parse well, complaining that the aliases for the joined tables are null references. MyDTO is a custom type for the output:</p>
  
  <pre><code>public class MyDTO
  {
      public string thread_topic { get; set; }
      public DateTime post_time { get; set; }
      public string user_display_name { get; set; }
      public string user_signature { get; set; }
      public string user_avatar { get; set; }
      public string post_topic { get; set; }
      public string post_body { get; set; }
  }
  </code></pre>
  
  <p>I'm out of ideas, and while doing this by direct SQL query is possible, I'd like to do it properly, without defeating the purpose of using an ORM.</p>
  
  <p>Thanks in advance!</p>
  
  <p>EDIT:</p>
  
  <p>The database looks like this: <a href="http://i41.tinypic.com/5agciu.jpg" rel="nofollow noreferrer">http://i41.tinypic.com/5agciu.jpg</a> (Can't post images yet.)</p>