Use SQL query to populate property in nHibernate mapping file

Question

I have an object which contains a property that is the result of an SQL statement. How do I add the SQL statement to my nHibernate mapping file?

Example Object:

public class Library{
    public int BookCount
    {
        get;
        set;
    }
}

Example Mapping File:

<hibernate-mapping>
    <class name="Library" table="Libraries">
        <property name="BookCount" type="int">  <- This is where I want the SQL query to populate the value. ->
    </class>
</hibernate-mapping>

Example SQL Query:

SELECT COUNT(*) FROM BOOKS WHERE BOOKS.LIBRARY_ID = LIBRARIES.ID

Answer 1

Use the formula attribute on your property.

See here for an example: http://stackoverflow.com/questions/887474/nhibernate-property-formula-filter

Answer 2

This is discussed in Ayende's blog post:

formula is a way to specify any arbitrary SQL that we want to associate with a property. Obviously, this is a read only value, and it is something that we would use on fairly rare occasions. Nevertheless, it can be pretty useful at times.

Example:

<property name="BookCount" type="int" formula="(SELECT COUNT(*) FROM BOOKS WHERE BOOKS.LIBRARY_ID = ID)" />