CODEWITHSUNDEEP
sqllinqnhibernate
longday
longday

Reputation: 4170

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

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?

Upvotes: 8

Views: 1969

Answers (4)

ShawnFumo
ShawnFumo

Reputation: 2178

The most advanced way I've seen so far is NHibernate.Property.Expression:

From their site:

public class Person
{
    public virtual Guid Id { get; set; }
    public virtual string Name { get; set; }
    public virtual DateTime StartDate { get; set; }
    public virtual DateTime? EndDate { get; set; }
    public virtual Person Manager { get; set; }

    public static Expression<Func<Person, bool>> IsActiveExpression =
        person => person.StartDate <= DateTimeTestable.Today() && (person.EndDate == null || person.EndDate >= DateTimeTestable.Today());

    public static Func<Person, bool> CompiledIsActive = IsActiveExpression.Compile(); 

    public virtual bool IsActive { get { return CompiledIsActive(this); } }
}

Queried with:

session.QueryExtended<Person>().Where(p => p.IsActive)

This is similar to what dvdvorle posted, but is supposed to solve some issues that had (such as needing the parameter name to match exactly in the original expression and in the where clause). More information on why it was created in this article.

Upvotes: 0

dvdvorle
dvdvorle

Reputation: 961

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)

Upvotes: 1

J. Ed
J. Ed

Reputation: 6742

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

Upvotes: 1

zihotki
zihotki

Reputation: 5191

Only a sql expression can be used as a formula, check the documentation - http://www.nhforge.org/doc/nh/en/#mapping-declaration-property . This is because it directly goes into Formula property of a column in the database (a computed column is created with this formula).
Don't make things too complex, just use that sql formula. If you think that expression will be faster - you are wrong.

Upvotes: 0

Related Questions