9

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
W3Max
  • 3,328
  • 5
  • 35
  • 61
  • Just an observation with the SQL, but it seems that this is a bad way to get the data you require, shouldn't the sub select be replaced with a JOIN on the subordinate table, then a count(*) on it would result in the same value? It seems like you're forcing the query planner to take an inefficient route by doing the above. No hugely relevant to you're question, but it's what I'm looking for at the moment. – Martin Nov 28 '12 at 21:29

1 Answers1

15

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.

Ruben Bartelink
  • 59,778
  • 26
  • 187
  • 249
Lachlan Roche
  • 25,678
  • 5
  • 79
  • 77
  • please let use know when it is available in Fluent. – Petrus Theron Apr 24 '11 at 21:13
  • While this works, I don't think it's efficient since you're running a subquery in your select clause. Is there a way to left join the child table with a group by in the main query? – Alex Apr 12 '12 at 16:57
  • I had to add () around the formula to get this to work for me, thanks for pointing me in the right direction – Haroon Nov 06 '12 at 16:13
  • Extra Lazy Load is available in Fluent now... not sure when it was added, but just came across this question and thought I'd chip in. – Martin Nov 28 '12 at 21:24