I found what I needed here:
http://stackoverflow.com/questions/2300762/how-to-map-count-of-collection-to-entity-with-fluent-nhibernate[
^]
I wanted this information to be declared in the mapping, and I'm using FluentNHibernate.
It revolves around using something called .FormulaIs() (which has been changed to .Formula() in NHibernate 3.x) to get a count and make it a sub-query of the entity mapping.
Basically, a Formula can take a custom SQL query append it (or make it a sub-query) as part of the basic mapping for an entity. For example, in my Firm object, I wanted a .HasUsers property to return a boolean as to whether the Firm I retrieve has 1 or more users.
so my property stayed the same (with the exception of making it virtual so NHibernate could proxy it), but here is what my mapping directive looks like now:
Map(x => x.HasUsers).Formula("case when (select count(*) from [user] where firm_id = Id) > 0 then 1 else 0 end").Not.Insert().Not.Update();
Note that I get the count of all users for a given firm, then feed that through a SQL case statement to return a boolean value.
You can choose to explicitly lazy-load the Formula (default is eager-load) by putting .LazyLoad()at the end of the Fluent statement. If you lazy-load it, you'll get two separate SQL queries, one for the regular session.Get<firm> query (assuming it's not taking it from the built-in Identity Map of NHibernate), and a second query when you go to access the property.
Also note the addition of the .Not().Insert().Not().Update()... just in case some process decides it wants to try to change that property. It should be read-only.
Hope this helps!
Submit an article or tip