Finding parents where child collection does not contain an item with a specific property value

Question

I am trying to get a list of parents where the child collection does not contain an item of a specific type. The LINQ equivalent would be something like:

dataset.Where(x => x.Items.FirstOrDefault(y => y.Type.Code == "ABC") == null)

The object model is Parent > Child (Items) > Type > Code

If Parent is my aggregate root, how would I model this in NHibernate criteria/query? Here's my first attempt:

var results = session.CreateCriteria<Parent>()
    .CreateCriteria("Items")
    .CreateCriteria("Type")
    .Add(Restrictions.Not(Restrictions.Eq("Code", "ABC")))
    .SetResultTransformer(Transformers.DistinctRootEntity)
    .List<Parent>();

This doesn't seem to return the right entities - it just returns them all.

Answer 1

It's easier to do this with HQL than with Criteria:

from Parent
where id not in
      (select p.Id
       from Parent p
       join p.Items item
       where item.Type.Code = 'ABC')

You'll get the result you expected, and without the need for DistinctRootEntity.