Hi guys,

I have two tables, teams and workers with a many to one association between them (one team --> many workers).
I have two classes Respectively, Team and Worker, where Team has a collection of Workers.

My client wants two way access to the data:
1) Get teams with each team's workers details [select * from workers...].
2) Get teams with only number of workers [select count(*) from workers where team_id = ..."

This is easily done with sql but I don't know how to do this in NHibernate.
I could count the Workers in the collection but it's lazy loaded and should remain like this due to the amount of data (it's not efficient to do it like this anyway)

basically I want to add a NumOfWorkers field to my Team class that its value is the result of "select count * from..".
This should be automatically executed when NHibernate retrieves the Team from the DB.

How can I accomplish this? Is there a mapping technique that I can use or an HQL/SQL to add somewhere?

Thanks in advance,
Shaul

Define a formula property, where you can put SQL code.

Thanks!
I Tried it and it worked.
Shaul