Using NHibernate's HQL to make a query with multiple inner joins
- by Abu Dhabi
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:
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
};
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:
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<THREAD>("what", threadid)
.SetResultTransformer(Transformers.AliasToBean(typeof(MyDTO)))
.List<MyDTO>();
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:
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; }
}
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.
Thanks in advance!
EDIT:
The database looks like this: http://i41.tinypic.com/5agciu.jpg (Can't post images yet.)
