NHibernate ICriteria subqueries with ISet<T> properties: Is this correct?

Question

Given a class A related to another class B in a one-to-many relationship, how can you create an NHibernate query (using the Criteria API--no HQL) to find all objects A who "have none" of the related objects B? Specifics follow, but I wanted to get the question out there first.

I have two model classes, Sample and SampleDiagnosis. Sample has a Diagnoses property which is an ISet<SampleDiagnosis>. SampleDiagnosis has an Owner property which links back to the sample. It is a simple one-to-many relationship.

The relevant section of my Sample NHibernate mapping file looks like this:

<set name="Diagnoses" table="sample_diagnoses" cascade="all-delete-orphan" inverse="true">
  <key column="sample_id" />
  <one-to-many class="SampleDiagnosis" />
</set>

The relevant piece of the SampleDiagnosis mapping file looks like this:

<many-to-one name="Owner" class="Sample" column="sample_id" />

I am trying to create an NHibernate ICriteria query that will match Samples that do not have any items in their Diagnoses list. Here is the code I am using to build my test query:

var dc = DetachedCriteria.For<Sample>();

var subcriteria = dc.CreateCriteria("Diagnoses").SetProjection(Projections.RowCount());

dc.Add(Subqueries.Eq(0, subcriteria));

In my mind, the SQL generated for this would look something like the following:

SELECT * FROM samples WHERE (select count(*) from sample_diagnoses where sample_diagnoses.sample_id = samples.sample_id) = 0

However, when actually doing a GetExecutableCriteria() and List()ing the results, my code crashes. Hard. No exceptions are thrown, but whatever happened to be running the code (be it NUnit or w3p.exe) dies a horrible death. I've tried it on multiple machines, all with the same result. I have a feeling I am missing something crucial about how subqueries work with sets in NHibernate.

Answer 1

This would be SO much easier via HQL - just sayin' :)

Did you try using size? Something like:

var dc = DetachedCriteria.For<Sample>();
dc.Add(Expression.sizeEq("Diagnoses", 0) );

Answer 2

Turns out, what I was looking for was Expression.IsEmpty("Diagnoses").