NHibernate : Count childrens' children

Question

I have an entity type A. Which has many B's. The B entity has many C's.

I need to count how many C's does an A entity have. How can this be done using NHibernate Criteria API?

---

Using LINQ to NHibernate I was unable to get results since it throws an exception (see this question)

Answer 1

This query becomes simpler if you use C as starting point of the query, rather than A. This is possible since you use bidirectional mappings, according to the mapping you show in your other question.

The way I would do this is to find all Cs which have a B which has a given A, and then count the found Cs.

To Add a constraint on the B of a C, you can add an alias and then add restrictions to that alias. To perform a count query rather than returning the found Cs you can use the SetProjection method and specify a Count projection. Since the count projection returns a single integer value, use UniqueResult to get the count.

using (ISession session = SessionFactorySingleton.OpenSession())
{
    int numberOfCsForA1 = session.CreateCriteria<C>()
        .SetProjection(Projections.Count("Id"))
        .CreateAlias("B", "b")
        .Add(Restrictions.Eq("b.A.Id", a1.Id))
        .UniqueResult<int>();

    // ...
}

The SQL generated by this query looks like this:

SELECT count(this_.Id) as y0_
    FROM [C] this_
    inner join [B] b1_ 
    on this_.IdB=b1_.Id
    WHERE b1_.IdA = @p0;@p0 = 12

As you can see, it is a two-way join, since NHibernate is smart enough to realize that it does not need to join with the A table to get the id of a Bs A. Instead it simply looks at the IdA value of the B.