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!
