NHibernate Criteria Query - Select Distinct with Joined Entity

Question

I have a Person entity. Every person has a country, I want to select all the distinct countries that have people in them. This Criteria Query returns all the distinct CountryID's

criteria.SetProjection(Projections.Distinct(Projections.Property("Country")));

How do I alter it to join and fetch the Country entity, not just the ID?

Courtyard

Answer 1

Any easy way would be to use a subquery. That is, you could select the whole country on the outer query where the country ID matches the inner query.

Subqueries.PropertyIn(
  "Country",
  innerDetachedCriteriaWhichFindsCountriesWithPeopleAndProjectsCountryId)

Cornhusk

This would work but I don't have a detached Criteria, so I either need a way to convert an ICriteria into a DetachedCriteria or I can't use this. This constructor looks promising, but I have no idea what to pass in for CriteriaImpl DetachedCriteria(NHibernate.Impl.CriteriaImpl, NHibernate.ICriteria) – Courtyard

You can go the other way, actually. Build your criteria using only DetachedCriteria instances (DetachedCriteria.For<T>()), then use GetExecutableCriteria(session) to convert it to a real, executable criteria. – Cornhusk

This worked, but I had to spend hours changing all my ICriteria objects to DetachedCriteria objects, and then attach them to a session when I need them. Any other way would have introduced duplication and I've been stung before by bugs where I updated one thing and not the other. Let me know if anyone knows how to do it with an ICriteria rather than a DetachedCriteria – Courtyard