Search code examples
nhibernatecollectionsfluent-nhibernate

How to map count of collection to entity with fluent-nhibernate


With employees and subordinates - I want to load an employee with the count of subordinates in one query.

public class Employee
{
    public Name {get;set;}
    public int NumberOfSubordinates {get;set;}
}

Resulting SQL should look like :

select e.name, (select count(*) from subordinate s where s.employee_id = e.id) NumberOfSubordinates
from employee e 
group by e.name
order by NumberOfSubordinates desc

Solution

  • You could map this column as a Formula.

    Map(x => x.NumberOfSubordinates)
        .FormulaIs(@"select count(*) from subordinate where subordinate.employee_id = id");
    

    A different approach is to map Subordinates as an inverse bag and use lazy="extra". In this case Subordinates.Count will perform the SQL count(*), though not as part of the initial load. This approach may not yet be available in Fluent.