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)