If you’re using ICriteria to write queries in NHibernate and you’re having citeria on items in a one-to-many relation (e.g. retrieve all departments with at least one employee named “John”) then you’re likely to have experienced that too many objects are returned (at least more than expected). In the example, all departments with multiple “Johns” will be returned once per matching employee. This is probably not what’s expected, so here’s how to fix it:
// The 'criteria' variable contains criteria on Department
// Create alias to use for employee conditions
ICriteria aliasCriteria = criteria.CreateAlias("Employees", "pr");
// Add criteria on the alias...
// We only want distinct root objects returned
criteria.SetResultTransformer(Transformers.DistinctRootEntity);
The trick is to use Transformers.DistinctRootEntity (at least in NHibernate 2.1). In earlier versions you may want to try something like this:
criteria.SetResultTransformer(new DistinctRootEntityResultTransformer());
Related to this is also how to write criteria that counts the results without actually retrieving them, which can be very useful for paging the result set (i.e. only read a subset of the results at a time). Normally you would write something like this:
criteria.SetProjection(Projections.Count("Id"));
int numberOfItems = (int)criteria.UniqueResult();
However, this suffers from the same problem as described above. To get the correct result, do this:
criteria.SetProjection(Projections.CountDistinct("Id"));
int numberOfItems = (int)criteria.UniqueResult();
Problem solved!
Credits to my colleague Jon for helping out with these rather obscure problem solutions!
/Emil