NHibernate computed column

Question

I have an entity ("A") that I'm persisting via NHibernate. This entity might have several children of type ("B") in the database, and when I retrieve entity A, I want to have a property that indicates a count of entity B that belong to A. I don't want to use a collection here because I don't have to have to retrieve all B entities just to count them.

Psuedo SQL to do what I want might look like:

select a.*, count(b.*) from a left join b on b.aid = a.id

Is this possible with NHibernate, and specifcally the LINQ provider?

Answer 1

If you specify in your collection lazy="extra", when you access the count property on the collection nhibernate will just get the count of the children rather than all of the children.

See here (2nd heading): http://blog.idm.fr/2010/02/improving-performance-with-nhibernate.html

Answer 2

Can you bind your nHibernate to a view instead?

CREATE VIEW A_augmented
AS
SELECT A.*
       ,(SELECT COUNT(*) FROM B WHERE B.aid = a.id) AS ChildCount
FROM A

(I know this SQL is relatively naive, but it is about as simple a way of expressing it in valid SQL)

Answer 3

You could use Formula to map a property to an SQL expression. There is an example of the very problem on Ayendes site. Nice to use, but may have side-effects you need to be avare of. The calculated column is of course not up to date. It is a snapshot when the entity is loaded.

You could also use HQL to get the size with a simple syntax:

select a, size(a.Children)
from a

Of course this also returns a snapshot, but your business logic is aware of that and therefore it is not a side-effect.