Click here to Skip to main content
12,998,423 members (89,324 online)
Rate this:
Please Sign up or sign in to vote.
See more:

I have the following scenario. I have a Firm that can have 0 to x Users. I have a Firm entity and a User entity.

I hand-rolled my own repository layer using ADO.NET, and now I'm switching that over to NHibernate, but I'm stuck on one property of my Firm entity.

It's a property called .HasUsers. This property before would return me a boolean based on the count of the amount of users that were assigned to a particular Firm. I did this via straight SQL by nesting a subquery into my FirmRepostiory.

In NHibernate, I realize I could create a Users property on Firm, and then map my entity to load all Users via lazy-loading (which is NHibernate) default.

But I don't care about the actual Users associated with a Firm, I just care if a Firm has more than 0 Users. I'd like to be able to just grab a count on the DB side of Users associated with a given Firm, and NOT have to lazy-load the Users collection just to see if I have more than zero Users.

To do that seems like a huge waste of resources... what if I had hundreds of Users? Then I'd have to new up hundreds of User objects just to take a count off the collection to see if Firm.HasUsers returns true or not?

There has to be a better way. Is it something I need to do in my mapping, or b/c of the way NHibernate works, would I have to put a new column on my Firm table that has a number of Users column, that contained a number? That also too seems like an accident waiting to happen.

Any ideas?

Posted 6-Oct-11 11:08am
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I found this Getting Row Count in NHibernate[^], see if it delivers the performance your want.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

I found what I needed here:[^]

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!
Rate this: bad
Please Sign up or sign in to vote.

Solution 3


Doing a count(0) will do a full table scan on the database.

It is better to use Exists, as it will stop scanning the table when it finds a first match.

Your code would become

Map(x => x.HasUsers).Formula("case when exists (select 0 from [user] where firm_id = Id) then 1 else 0 end").Not.Insert().Not.Update();

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170622.1 | Last Updated 29 Mar 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100