NHibernate linq - Use lambda expression in place of formula attribute on mapping

Question

NHibernate has an attribute on the property element in the mapping config named "formula" that allows the injections of sql to "calculate" a property. The issue I have is the formula using sql syntax directly. Is there a way to have nhibernate.linq to use a lambda expression instead of using the formula property.

I have the following:

public class Invoice
{
    public virtual int Id { get; protected set; }
    public virtual decimal Amount { get; set; }
    public virtual decimal Paid { get; set; }
    public virtual decimal Balance
    {
        get { return BalanceExpression.Expression.Compile().Invoke(this); }
    }
}

public class BalanceExpression
{
    public static Expression<Func<Invoice, decimal>> Expression
    {
        get { return i => i.Amount - i.Paid; }
    }
}

<class name="Invoice"> 
  <id name="Id"> 
    <generator class="hilo"/> 
  </id> 
  <property name="Amount"/> 
  <property name="Paid"/> 
  <property name="Balance" formula="Amount - Paid" access="readonly"/> 
</class>

I want nhibernate to use the balanceexpression.expression instead of having to put sql syntax in the formula attribute so I can remove the formula attribute from my mapping config and write queries as follows:

from i in session.linq() where i.balance > 0 select i;

How do I inject the balanceexpression.expression into the linq query?

Answer 1

Since you already have the Amount and Paid properties on your object, you can just define the Balance property as a regular property:

public int Balance { get { return Amount - Paid; } }

If you don't set the Amount / Paid properties to 'Lazy', I think this is the best, most readable and most maintainable solution.
If either of these properties IS lazy-loaded, you can still use this method, just note that it would have side effects. For example- a statement such as this

invoices.Where(i => i.Balance > 5)

would cause a DB access for each Invoice in the collection.
This might still be acceptable for you, just be aware of that..
Cheers Jhonny

Answer 2

I'm pretty sure http://hendryluk.wordpress.com/2011/09/06/nhibernate-linq-ing-calculated-properties/ is what you're looking for. (The "Better Way?" part)