NHibernate ICriteria Connection Status

Question

I am trying to use ICriteria to create a query with join state. The SQL I'm trying to create 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 criteria such as

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

It will generate sql, below which I do not want.

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

Is there a way to do this using ICriteria or HQL if ICriteria is not possible?

Edit: I found that nHibernate 2.1 (which I use) now resolves this . However, I'm not sure about ICriteria, these are my preferences.

Answer 1

I found the only way to do this with NHibernate 3, and earlier through filters like this example

In NHibernate 3.1, this is now better supported, https://nhibernate.jira.com/browse/NH-2190

Answer 2

I would not do that. The left outer connection allows NH load load clients anyway, the E-Mail contact filter will only load E-Mail contacts ... until it initializes the contact collection and loads anyway.

If it downloads only E-Mail contacts, this will lead to incomplete objects in memory. This is usually not a good idea, especially when you are also changing data in a single transaction.

In your situation, I would try to directly download EMail contacts and switch from contact to client.

 session.CreateCriteria(typeof(Contact)) .Add(Restrictions.Eq("c.ContactType", ContactType.Email)); 

Answer 3

If you don’t find a way to form the correct join , you can do a simple trick: just make your limit WHERE t.ContactType = 'Email' OR t.ClientID IS NULL - I hope this is possible with NHibernate.

Answer 4

You can use IQuery in conjunction with ISQLQuery. This is not a criteria mechanism, but it can help you.

Answer 5

 from s in Session.Linq<Client> left join cc in c.Contacts on new { c.ClientID , cc.ClientID } equals new { cc.ContactType, "Email" } select c.ClientId; 

Hope this works if you use Nhibernate 2