21 August 2008

Excluding particular derived types in NHibernate queries

Today I came across an NHibernate problem where I needed to select every instance of a particular base type and all its derived types from a database, apart from one particular derived type. Here is a trivial example:

public class Mammal {}
public class Dog : Mammal {}
public class Cat : Mammal {}
public class DomesticCat : Cat {}

In this case the problem was equivalent to selecting every mammal that isn't a domestic cat.

We're using the table per class hierarchy inheritance model in NHibernate which uses values in a discriminator column to determine which type is held in a particular row of the table.

Selecting the whole hierarchy is done like this:

IQuery q = sess.CreateQuery("from Mammal");
IList mammals = q.List();
In Criteria:
ICriteria crit = sess.CreateCriteria(typeof(Mammal));
List mammals = crit.List();

I then needed to be able to effectively add a WHERE discriminator <> 'DomesticCat' to the end of the query. I had a quick search for this special discriminator property and for a Criteria Expression for excluding a particular type but couldn't find either.

The Solution

I finally found the solution on the WHERE clause page of the HQL chapter in the NHibernate reference. There is a special property called class which you can test against a type name in HQL or an actual type in Criteria queries e.g.

IQuery q = sess.CreateQuery("from Mammal m where m.class != 'DomesticCat'");
IList mammals = q.List();
In Criteria:
ICriteria crit = sess.CreateCriteria(typeof(Mammal));
crit.Add( Expression.Not( Expression.Eq("class", typeof(DomesticCat)) ) );
List mammals = crit.List();


Anonymous said...

nice, i was just looking for the Crtieria equivalent of HQL class keyword.

Moi said...

Thank you for this code, this is exactly what I was looking for..

Anonymous said...

I need exactly this but for nhibernate QueryOver. I was not able to get it working with QueryOver. maybe you can help me out and email me?

c.hohmann(at)sycat . com

Derek Fowler said...

I don't have NH 3.0 to test this but after a quick look at the documentation and source my best guess would be:

.WhereNot(Restrictions.Eq("class", typeof(DomesticCat)))

Paul T Davies said...

This helped me.