Hibernate Restrictions Based on a Collection Field

This took me a little while to work out so I thought I would post it in case it’s useful and also to remind myself when it inevitably comes up again in a year or so and I have completely forgotten it. This is actually NHibernate in .NET (C#) but it’s basically the same for Java and hibernate. The problem I had was using the Criteria API in NHibernate to query for a collection on the entity. My particular application has a Case with a collection of DataSet entities. What I want is a list of cases that have a reference to a specified dataset object. In SQL, it would be something along the lines of:

SELECT * FROM cases c
INNER JOIN data_set ds ON ds.case_id = c.id
WHERE ds.id = <specified id>

DataSets.ID is no good because obviously DataSets is a List and doesn’t have an ID, we are looking to query the entities that are contained by our List. Using the criteria API, this is very simple once you get your head around using an alias, the code is as follows:

ICriteria criteria= session.CreateCriteria<Case>();
criteria.CreateAlias(“DataSets”, “ds”);
criteria.Add(Restrictions.Eq(“ds.ID”, <specified id>));

First, we create an alias called ‘ds’ which gives us direct access to the DataSets collection, we can then look for the ID easily using the alias.