Is it possible to do this in NHibernate without using CreateSQLQuery?

Question

Is it possible to do this in NHibernate without using CreateSQLQuery. Preferably with Linq To Nhibernate. The biggest question is how do I do joins not on a primary key?

SELECT DISTINCT calEvent.* From CalendarEvent as calEvent
LEFT JOIN UserChannelInteraction as channelInteraction on channelInteraction.ChannelToFollow_id = calEvent.Channel_id
LEFT JOIN UserCalendarEventInteraction as eventInteraction on eventInteraction.CalendarEvent_id = calEvent.Id
LEFT JOIN UserChannelInteraction as eventInteractionEvent on eventInteractionEvent.UserToFollow_id = eventInteraction.User_id
WHERE (calEvent.Channel_id = @intMainChannelID 
OR channelInteraction.User_id = @intUserID
OR eventInteraction.User_id = @intUserID
OR (eventInteractionEvent.User_id = @intUserID AND eventInteraction.Status = 'Accepted'))
AND calEvent.StartDateTime >= @dtStartDate 
AND calEvent.StartDateTime <= @dtEndDate
ORDER BY calEvent.StartDateTime asc

Answer 1

Hmmm... maybe you need to try to leverage subqueries?

Check this out: http://devlicio.us/blogs/derik%5Fwhittaker/archive/2009/04/06/simple-example-of-using-a-subquery-in-nhibernate-when-using-icriteria.aspx

Answer 2

You can do arbitrary joins by using Theta joins. A theta join is the Cartesian product, so it results in all possible combinations, which then can be filtered.

In NHibernate you can perform a theta style join like this (HQL):

from Book b, Review r where b.Isbn = r.Isbn

You can then add any filtering conditions you want to, order the results and everything else you might want to do.

from Book b, Review r where b.Isbn = r.Isbn where b.Title = 'My Title' or r.Name = 'John Doe' order by b.Author asc

Here is an article about theta joins in Hibernate (not NHibernate, but it's still relevant).

However, since the theta join is a Cartesian product, you might want to think twice and do some performance testing before you use that approach to do a three-join query.