Hendry Luk — Sheep in Fence

September 6, 2011

NHibernate: Linq-ing Calculated Properties

Filed under: Software Development — Hendry Luk @ 11:53 pm

Some properties are mapped onto database columns, while others are calculated. Here’s one simple example:

public class Parcel
   public PackagingType PackagingType {get; set;}
   public ParcelType ParcelType  {get; set; }
   public float Weight {get; set;}
   public decimal Fee
      get {return (Weight - PackagingType.TareWeight) * ParcelType.FeePerWeight;

“Fee” is a calculated property. You cannot use this property as a part of your Linq query. For instance, if you want to query Parcels with Fees above certain values:

var expensiveParcels = from parcel in Session.Query<Parcel> where parcel.Fee > 1000 select parcel;

Obviously that wouldn’t work because NHibernate does not recognize this “Fee” property.

There are 3 (conventional) ways to resolve this, you can choose either one of these:

  1. Change all your queries to avoid “Fee” property, and repeat the calculation logic (every time) instead.
    var expensiveParcels = from parcel in Session.Query<Parcel>
          where (parcel.Weight - parcel.PackagingType.TareWeight) * parcel.ParcelType.FeePerWeight > 1000
          select parcel;
  2. A slightly better way is to map “Fee” property with SQL Formula. It’s better because you do not have to repeat Fee calculation on every Linq query. E.g., in FluentNHibernate:
    Map(x=> x.Fee).Access.Readonly().Formula( // Raw SQL ->
        @"((select Weight - (select TareWeight from PackagingType p where  p.Id = PackagingId))
              * (select FeePerWeight from ParcelType pt where pt.Id = ParcelTypeId))");
  3. Alternatively, you could also write an ILinqToHqlGenerator implementation.
    Since version 3.0, NHibernate has been utilizing ReLinq for its Linq-provider, which greatly improves its extensibility. It allows you to register your own ILinqToHqlGenerator, where you basically take a specific Linq expression and return whatever HqlTree expression you desire. For instance, in this example, we build our Hql expression like so:

    public class ParcelFeeLinqGenerator: BaseHqlGeneratorForProperty
       public override HqlTreeNode BuildHql(MemberInfo member, Expression expression, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
          var parcel = visitor.Visit(expression).AsExpression();
          return treeBuilder.Multiply(
                 treeBuilder.Dot(parcel, treeBuilder.Ident("Weight")),
                 treeBuilder.Dot(treeBuilder.Dot(parcel, treeBuilder.Ident("PackagingType")), treeBuilder.Ident("TareWeight"))
              treeBuilder.Dot(treeBuilder.Dot(parcel, treeBuilder.Ident("ParcelType")), treeBuilder.Ident("FeePerWeight"))

    And you register this generator into NHibernate runtime to handle our Fee property:

    registry.RegisterGenerator(ReflectionHelper.GetProperty<Parcel, decimal>(x=> x.Fee), new ParcelFeeLinqGenerator());

All those 3 approaches do the job just fine, but whichever one you pick you’ll still end up duplicating your Fee calculation logic one way or another. You’ll either be repeating your logic in C# expressions (option#1), in SQL expression (option #2), or in HQL expression (option #3). That seems to violate DRY. It’s easy to forget to change your querying logic whenever your pricing rule changes (e.g. tax).

Better Way?

The ideal solution is to eliminate logic duplication. We want to be able to write the calculation logic only once to be shared both by the property as well as by Linq queries. I’ll be using approach#3 while employing a pattern to avoid duplicating our logic.

First step, I create this generic ILinqToHqlGenerator plumbing that I can reuse for all calculated properties in my projects.

public class CalculatedPropertyGenerator<T, TResult> : BaseHqlGeneratorForProperty
   public static void Register(ILinqToHqlGeneratorsRegistry registry, Expression<Func<T, TResult>> property, Expression<Func<T, TResult>> calculationExp)
      registry.RegisterGenerator(ReflectionHelper.GetProperty(property), new CalculatedPropertyGenerator<T, TResult>{_calculationExp = calculationExp});
   private CalculatedPropertyGenerator(){} // Private constructor

   private readonly Expression<Func<T, TResult>> _calculationExp;
   public override HqlTreeNode BuildHql(MemberInfo member, Expression expression, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
      return visitor.Visit(_calculationExp);

Once we got that plumbing class in place, we’ll then modify my parcel class slightly to look like the following:

public class Parcel
   public PackagingType PackagingType {get; set;}
   public ParcelType ParcelType  {get; set; }
   public float Weight {get; set;}

   /// <summary>
   /// To be reused for (NHibernate) Linq generator
   /// </summary>
   public static readonly Expression<Func<Parcel, decimal>> CalculateFeeExpression = x =>
          (x.Weight - x.PackagingType.TareWeight) / x.ParcelType.FeePerWeight;

   private static readonly Func<Parcel, decimal> CalculateFee = CalculateFeeExpression.Compile();
   public decimal Fee
      get {return CalculateFee(this);

Now you register this FeeCalculationExpression to NHibernate registry so that NHibernate can now translate Fee property using the same fee-calculation expression used by the Parcel class itself.

CalculatedPropertyGenerator<Parcel, double>.Register(registry, x=> x.Fee, Parcel.CalculateFeeExpression);

Now this Linq query works. NHibernate knows how to handle Fee property in the query.

var expensiveParcels = from parcel in Session.Query<Parcel> where parcel.Fee > 1000 select parcel;

We are reusing the same business-rule for calculating Fee property as well as to generate our NHibernate queries. There’s no duplication, and it requires very little setup as well (once you get the CalculatedPropertyGenerator plumbing class in place). This so far has been my favorite approach to map calculated-properties to NHibernate.

About these ads


  1. Hi, this is just brilliant, thank you for sharing this..

    Comment by Marian — February 22, 2012 @ 6:17 pm | Reply

  2. In the context of Fluent NHibernate, where/when do you get the reference to the ILinqToHqlGeneratorsRegistry instance?

    Comment by Brian — February 28, 2012 @ 9:59 pm | Reply

    • On your NHibernate Configuration object (after you initialise it with Configure()), there is an extension-method you can call to register your ILinqToHqlGeneratorsRegistry: NHibernate.Cfg.Loquacious.LinqToHqlGeneratorsRegistry(…)

      Comment by Hendry Luk — February 28, 2012 @ 10:20 pm | Reply

      • Perfect! Thanks for the tip.

        Comment by Brian — February 29, 2012 @ 2:10 pm

  3. Can you add source example for Fluent NHibernate?

    Comment by Key — April 3, 2012 @ 7:00 pm | Reply

    • There’s actually no real difference, in fact I was using FluentNHibernate too, but it’s not really related, since the solution is implemented on the Linq level, and does not concern the actual entity mapping (hbm or fluent-nh).

      Comment by Hendry Luk — July 15, 2013 @ 1:56 am | Reply

  4. I have a class A with properties { class B and string C}. And Class B {string q, string w, string p} has a calculated field p = q + w; I want to select property p in a query: query.select(x=> x.B.p); I have your implementation to resolve the p property, but then executed, the expression instead of receiving a class B, it receives class A,so gives me an error “A doens’t have property p”. How can i fix that?. Thanks

    Comment by Martin Irigaray — July 19, 2013 @ 5:34 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.


Get every new post delivered to your Inbox.

%d bloggers like this: