C# – nHibernate ICriteria Join Condition

cnetnhibernate

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.

Best Answer

I wouldn't do this. The left outer join let's NH load clients anyway, the filter for E-Mail contacts would load only E-Mail contacts ... until it initializes the contact collection and loads everything anyway.

If it would load only E-Mail contacts, it would end up in incomplete objects in memory. This is usually not a good idea, particularly when you are also changing data in the same transaction.

In your situation I would try to load EMail contacts directly and navigate from the contact to the customer.

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

Related Solutions

C# – NHibernate Criteria with multiple results

I would presume that the answer lies in prefetching the associations that you want to. That way you can fetch the needed part of your object graph in a single shot. You would do this in criteria queries like so.

ICriteria query = session.CreateCriteria(typeof (Cat))
.SetFetchMode("Mate", FetchMode.Join)
.SetFetchMode("Kittens", FetchMode.Join);

IList<Cat> results = query.List<Cat>();

This will give you back a list of cats with both the Mate and the Kittens prepopulated. You can then navigate to these properties without incurring an N+1 penalty. If you need a more flattened result I'd do it using linq, perhaps like this.

var results = query.List<Cat>()
  .Select((c) => new {mother = c, mate = c.Mate, offspr = c.Kittens});

This will give you back a flattened list of anonymous types with the given properties. This will work if all you need is prefetching the object graph. However if you need to prefetch things such as counts or sums then you will need to examine the Projections and Alias parts of Criteria queries.

One more thing. If you are trying to exactly duplicate your above query, you can do so like this.

ICriteria query = session.CreateCriteria(typeof (Cat), "mother")
.CreateAlias("Mate", "mate", JoinType.InnerJoin)
.CreateAlias("Kittens", "offspr", JoinType.LeftOuterJoin)
.SetResultTransformer(Transformers.AliasToEntityMap);

This will return you basically the same as your hql query, however instead of using an indexed list it will use a dictionary that maps the alias to the entity.

R – Nhibernate Icriteria Enum flags (BitMask) support

this might be useful.

How to query flags stored as enum in NHibernate

Best Answer

Related Solutions

C# – NHibernate Criteria with multiple results

R – Nhibernate Icriteria Enum flags (BitMask) support

Related Topic