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

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. — MartinDotNet
– MartinDotNet, Commented Nov 28, 2012 at 21:29

Ruben Bartelink · Accepted Answer · 2016-04-06 09:04:49Z

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.

edited Apr 6, 2016 at 9:04

Ruben Bartelink

62.2k32 gold badges196 silver badges266 bronze badges

answered Feb 20, 2010 at 3:31

Lachlan Roche

26k5 gold badges82 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Petrus Theron Over a year ago

please let use know when it is available in Fluent.

Alex Over a year ago

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?

Haroon Over a year ago

I had to add () around the formula to get this to work for me, thanks for pointing me in the right direction

MartinDotNet Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related