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

Question

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

Answer 1

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.