nHibernate ICriteria Join Condition

Question

I am trying to use ICriteria to create a query that has a join condition. The SQL I am trying to generate should look like this

SELECT c.ClientID
FROM Client c
LEFT OUTER JOIN ClientContact t on c.ClientID = t.ClientID AND
t.ContactType = 'Email'

If I use a criteria like

m_ClientRepository.QueryAlias("client")
     .CreateCriteria("client.Contacts", "c", JoinType.LeftOuterJoin)
     .Add(Restrictions.Eq("c.ContactType", ContactType.Email));

It will generate the sql below which I don't want.

SELECT c.ClientID
FROM Client c
LEFT OUTER JOIN ClientContact t on c.ClientID = t.ClientID
WHERE t.ContactType = 'Email'

Is there anyway to do this with ICriteria or HQL if ICriteria is not possible ?

Edit: I have discovered nHibernate 2.1 (which I am using) does now allow this. Not sure about ICriteria though, this is my preference.

Answer 1

You could use IQuery in conjunction with ISQLQuery. That's not a Criteria mechanism, but it might help you.