NHibernate and Collection Counts

Question

I have the following class setup for persistence using NHibernate

public class Person
{
    public string Name { get; set; }
    public IList<Person> Subordinates { get; set; }
}

Now say I have a grid with two columns, "Name" and "Number of Subordinates" what is the best way of doing this in NHibernate whilst retaining the use of domain objects where possible.

Thanks

Answer 1

Assuming you have the HBM correct that Person HasMany Subordinates you can just get person and then call Subordinates.Count

This is a moderately wasteful process if all you need is the count since it will fully populate all of the Subordinates collection to just get you the count. I think you'd be better off creating a method that takes in a Person and returns you a count of their Subordinates and you use HQL to do an actual count function. If you can't do a direct count function you can have it return just a single value per subordinate that matches the Person FK and then call .Length or .Count on the collection it returns. However I assume HQL should be able to count it directly for you though.

Answer 2

You could create a DTO class that you use for reporting / overviews for instance... This class could look like this:

public class PersonView
{
     public string Name{ get;set; }
     public int NumberOfSubordinates{get;set;}     
}

Then, you create a Criteria query, in in that Criteria you define that you want to retrieve all Persons. However, you can specify that NHibernate should not return Person objects, but PersonView objects. In order to be able to do this, you'll need to use a projection and an AliasToBeanTransformer:

ICriteria crit = new Criteria(typeof(Person));

crit.SetProjection (Projections.ProjectionList()
                       .Add (Projections.Property("Name"), "Name")
                       .Add (Projections.Count ("Subordinates"), "NumberOfSubordinates");

crit.SetResultTransformer(Transformers.AliasToBean (typeof(PersonView));

Something like the above. (I didn't test your specific situation). Then, you just have to let NHibernate know of the existence of the PersonView class, by simply 'importing' this class. I have one hbm.xml file, where I import all my DTO classes. This looks like

Then, NHibernate will generate a query for your Criteria that pretty much looks like:

SELECT p.Name, COUNT(p.Subordinates) FROM Person
INNER JOIN Subordinates ON Person.PersonId = Subordinates.PersonID
GROUP BY p.Name