R – NHibernate Criteria Query – Select Distinct with Joined Entity

criteriaicriterianhibernate

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?

Best Answer

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)

Related Solutions

C# – Does NHibernate Criteria API support projections on collection properties

Look into having 'Query-only' properties in here http://ayende.com/Blog/archive/2009/06/10/nhibernate-ndash-query-only-properties.aspx

This would allow you to make the link look bidirectional for your queries.

R – Selecting a joined entity with an ICriteria in NHibernate

You can join by creating sub-criteria and select scalar results with Projections. Your Criteria query might look something like:

session.CreateCriteria(typeof(Person))
    .Add(Restrictions.Eq("Id", roleOwnerId))
    .SetProjection(Projections.Property("TargetPerson"))
    .CreateCriteria("RolesOnPeople", JoinType.InnerJoin) // Or LeftOuterJoin, etc.
    .List();

If you need multiple projections, use a ProjectionList like:

.SetProjection(Projections.ProjectionList()
    .Add(Projections.Property("...", ...))
    .Add(...)
    ...
)

I'm not sure what your domain looks like or what you're trying to get out of the query so the above may not be exactly correct. However, it should be what you need to get started.

Best Answer

Related Solutions

C# – Does NHibernate Criteria API support projections on collection properties

R – Selecting a joined entity with an ICriteria in NHibernate

Related Topic