how to map SampleCount property using NHibernate.

Question

Dear ladies and sirs.

I have two tables - Run and Sample, where Sample.RunId is a foreign key linking samples to their runs.

I would like to have a SampleCount property in Run, which is the number of Sample objects associated with the particular Run.

Is it possible to map such a property in NHibernate mapping of the Run type?

Thanks.

Answer 1

While you can do that using a <join>, it's not a good idea.

Instead, declare your Run.Samples collection as lazy="extra", and accessing Run.Samples.Count will do a query for the count instead of loading the entire collection.

Answer 2

Although the collection solution by Diego Mijelshon is perfectly valid it does mean an extra query after fetching the Run entity. If you haven't mapped the collection and you do not want to or you do not want the additional query, consider a "computed" property as such

in the Run class mapping

<property name="SamplesCount" type="long" formula="(select count(s.Id) from Samples s where s.RunId = Id)" />

and in the class Run just add

long SamplesCount {get; set;}

Note that in the query, for the part "s.RunId = Id" NHibernate will insert the proper alias for the root table. Also, don't forget the parenthesis, it makes it easier for the parser and in some cases is required.

This approach has the benefit of applying a subquery on the select (which may or may not be good depending on your case). The property can also be lazily-loaded (i think a NH 2++ feature) if this property is something you will only rarely need.