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:
- 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;
- 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))");
);
- 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.Subtract(
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.
Hendry Luk -- Sheep in Fence 