One often overlooked feature of NHibernate is the use of formula properties. Formula properties are properties that do not map to columns in the database, but instead are mapped using raw SQL queries.

The formula is mapped in the NHibernate mapping file using the same property element that normal properties use, just replacing the column keyword for the formula keyword.

<property name="FormulaPropertyName" formula="SQL STATEMENT" /> 

The basic idea is that when NHibernate loads your object it will issue your custom SQL at the same time, allowing for complex read-only properties to offload their work to the database.

For example, assume you have an class representing an order on an e-commerce website. An order is typically composed of line items, and a common question to ask the order as a whole is “what is your subtotal?”. Without weighing the pros and cons of such a decision assume that you do not want to store the subtotal as a field on the order table.

A perfectly acceptable solution would be to have a read-only property as such:

public decimal SubTotal{ 
     get{ 
          decimal subTotal = 0; 
          foreach(LineItem li in this.LineItems){ 
               subTotal += li.ExtendedPrice; //i.e., units ordered times unit price. 
          } 
          return subTotal; 
     } 
} 

However, this is post is about NHibernate formula. So for the sake of this post assume that (for whatever reason) you cannot or do not want to iterate over each line item to compute your subtotal. You could then use NHibernate formula to replace the above functionality with the following mapping.

<property name="SubTotal" access="field.camelcase-underscore" 
formula="select sum(Units * Price) from LinesItems where LineItems.OrderNumber=this.OrderNumber" />

The order class can be updated as follows:

private decimal _subTotal;

public decimal SubTotal{
     get{ return _subTotal; }
}

The above example is simplistic and overlooks several real-world concerns, its intent is solely to demonstrate the usages of  NHibernate formula. I find formula to be of limited use, but in certain per-application specific scenarios they can come in handy.