Lithium

Software made beautiful

NHibernate returning filtered result containg data computation

Recently I needed a query with some computation made in SQL. But the difficulty was that the computation wasn't just an a + b statement. It was supposed to aggregate number of items underlying in my domain object with some extra conditions.

Suppose I have entity Order that looks like:

    public class Order
    {
        private List<OrderItem> items;

        public Order()
        {
            items = new List<OrderItem>();
        }

        public virtual int Id { get; protected set; }

        public virtual DateTime DateCreated { get; set; }

        public virtual IEnumerable<OrderItem> Items
        {
            get { return items; }
        }
    }

Then the OrderItem:

    public class OrderItem
    {
        public virtual int Id { get; protected set; }

        public virtual Article Article { get; set; }

        public virtual int Quantity { get; set; }

        public virtual decimal Price { get; set; }
    }

The Article property and Article entity are not important in this context so no sorce code for now.

What I needed exactly was: orders (OrderDto) created after date 2016-01-01 having at least one item that has the quantity greater than 100.0

The query (QueryOver) I needed looks like:

            var date = new DateTime(2016, 1, 1);
            var session = sessionFactory.GetCurrentSession();

            Order _this = null;
            OrderItem item = null;
            OrderDto dto = null;
            var result = session.QueryOver<Model.Order>(() => _this)
                .Left.JoinAlias(() => _this.Items, () => item,
                    NHibernate.Criterion.Restrictions.Gt(
                        NHibernate.Criterion.Projections.Property(() => item.Quantity),
                        100))
                .SelectList(l => l
                    .Select(() => _this.Id).WithAlias(() => dto.Id)
                    .Select(() => _this.DateCreated).WithAlias(() => dto.DateCreated)
                    .SelectGroup(() => _this.Id)
                    .SelectGroup(() => _this.DateCreated)
                    .SelectCount(() => item.Id).WithAlias(() => dto.ItemCount))
                .Where(
                    NHibernate.Criterion.Restrictions.Gt(
                        NHibernate.Criterion.Projections.Count(
                            NHibernate.Criterion.Projections.Property(() => item.Id)),
                        0))
                .Where(
                    NHibernate.Criterion.Restrictions.Gt(
                        NHibernate.Criterion.Projections.Property(() => _this.DateCreated),
                        date))
                .TransformUsing(
                    Transformers.AliasToBean<OrderDto>())
                .List();

Which resulted with SQL:

    SELECT
        this_.Id as y0_,
        this_.DateCreated as y1_,
        this_.Id as y2_,
        this_.DateCreated as y3_,
        count(item1_.Id) as y4_ 
    FROM
        "Order" this_ 
    left outer join
        "OrderItem" item1_ 
            on this_.Id=item1_.Order_id 
            and (
                item1_.Quantity > @p0 
            ) 
    WHERE
        this_.DateCreated > @p1 
    GROUP BY
        this_.Id,
        this_.DateCreated 
    HAVING
        count(item1_.Id) > @p2;

The SQL above looke pretty much what I needed.

The example shows how to compute some data with non trivial restrictions (conditions) in a query instead of post query computation in C#.

Computed properties in NHibernate

Implementing interface IHqlGeneratorForProperty helps to provide a consistent computed properties defined as LINQ expression in the domain class. Suppose that we have a domain class having property NumberOfApprovedWorkItems in a class:

namespace ComputedProperties.Tests.Domain
{
    using ComputedProperties.Tests.Services;
    using System;
    using System.Linq;
    using System.Collections.Generic;
    using System.Linq.Expressions;

    public class Employee
    {
        // . . .

        public static readonly Expression<Func<Employee, int>> CalculateNumberOfApprovedWorkItemsExpression =
            x => x.Work.Count(w => w.ApprovalDate != null);

        private static readonly Func<Employee, int> CalculateNumberOfApprovedWorkItems =
            CalculateNumberOfApprovedWorkItemsExpression.Compile();

        public virtual int NumberOfApprovedWorkItems
        {
            get { return CalculateNumberOfApprovedWorkItems(this); }
        }

        // . . .
    }
}

Then having following implementations:

namespace ComputedProperties.Tests.CalculatedProperties
{
    using FluentNHibernate.Utils.Reflection;
    using NHibernate.Hql.Ast;
    using NHibernate.Linq.Functions;
    using NHibernate.Linq.Visitors;
    using System;
    using System.Linq.Expressions;
    using System.Reflection;

    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.GetMember(property).MemberInfo,
                new CalculatedPropertyGenerator<T, TResult>(calculationExp));
        }

        public CalculatedPropertyGenerator(Expression<Func<T, TResult>> calculationExp)
        {
            this.calculationExp = calculationExp;
        }

        private readonly Expression<Func<T, TResult>> calculationExp;

        public override HqlTreeNode BuildHql(MemberInfo member, Expression expression,
            HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
        {
            return visitor.Visit(calculationExp);
        }
    }
}

and:

namespace ComputedProperties.Tests.CalculatedProperties
{
    using Domain;
    using NHibernate.Linq.Functions;

    class ComputedPropertyGeneratorRegistry : DefaultLinqToHqlGeneratorsRegistry
    {
        public ComputedPropertyGeneratorRegistry()
        {
            CalculatedPropertyGenerator<Employee, int>.Register(
                this,
                x => x.NumberOfApprovedWorkItems,
                Employee.CalculateNumberOfApprovedWorkItemsExpression);
        }
    }
}

we have computed property query in the form of LINQ expression instead of raw SQL string embeded in the mapping of the entity class. So now we can write a query using that property:

session.Query<Employee>().Where(x => x.NumberOfApprovedWorkItems > 3);

NHibernate knows the formula for computed property - it is defined in expression. So it will issue appropriate SQL for that so we can have it in our Where clause. On the other hand, when we want to use that property in our code - we just use it and the expression would get applied again.

Maybe it is not perfect because in our code the property is going to be computed over and over again what could have some consequences. But it is some way to get it achieved without providing SQL query part string.

Binding Inlines in TextBlock

TextBlock control in WPF has Inlines property that isn't a DependencyProperty. That makes it impossible to bind it directly to the view model. To make it possible, an attached property and some value converters can come to the rescue.

So the attached property implementation looks like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;

namespace WPF_BindingInlinesInTextBlock
{
    public class Attached
    {
        public static IEnumerable<Inline> GetInlines(DependencyObject d)
        {
            return (IEnumerable<Inline>)d.GetValue(InlinesProperty);
        }

        public static void SetInlines(DependencyObject d, IEnumerable<Inline> value)
        {
            d.SetValue(InlinesProperty, value);
        }

        // Using a DependencyProperty as the backing store for Inlines.  This enables animation, styling, binding, etc...
        public static readonly DependencyProperty InlinesProperty =
            DependencyProperty.RegisterAttached("Inlines", typeof(IEnumerable<Inline>), typeof(Attached),
                new FrameworkPropertyMetadata(OnInlinesPropertyChanged));

        private static void OnInlinesPropertyChanged(DependencyObject d, DependencyPropertyChangedEventArgs e)
        {
            var textBlock = d as TextBlock;
            if (textBlock == null)
            {
                return;
            }
            var inlinesCollection = textBlock.Inlines;
            inlinesCollection.Clear();
            inlinesCollection.AddRange((IEnumerable<Inline>)e.NewValue);
        }
    }
}

Considering following view model:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WPF_BindingInlinesInTextBlock
{
    public class ViewModel : INotifyPropertyChanged
    {
        private string text;

        public ViewModel(string text)
        {
            this.text = text;
        }

        public string Text
        {
            get { return text; }
            set
            {
                text = value;
                FirePropertyChanged("Text");
            }
        }

        private void FirePropertyChanged(string propertyName)
        {
            var handler = PropertyChanged;
            if (handler != null)
            {
                handler(this, new PropertyChangedEventArgs(propertyName));
            }
        }

        public event PropertyChangedEventHandler PropertyChanged;
    }
}

We would like to have the value converter as follows:

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Data;
using System.Windows.Documents;

namespace WPF_BindingInlinesInTextBlock
{
    public class StringToInlinesConverter : IValueConverter
    {
        public object Convert(object value, Type targetType, object parameter,
            CultureInfo culture)
        {
            var text = value as string;
            if (text == null)
            {
                return null;
            }
            var words = GetWords(text);
            return GetInlines(words).ToList();
        }

        private static IEnumerable<Inline> GetInlines(IEnumerable<string> words)
        {
            return words.Select(x => new Run(x));
        }

        private static IEnumerable<string> GetWords(string text)
        {
            return text.Split(' ');
        }

        public object ConvertBack(object value, Type targetType, object parameter,
            CultureInfo culture)
        {
            throw new NotImplementedException();
        }
    }
}

Now let's wire it all together in XAML:

<Window x:Class="WPF_BindingInlinesInTextBlock.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:WPF_BindingInlinesInTextBlock"
        mc:Ignorable="d" Title="MainWindow">
    <Grid>
        <Grid.Resources>
            <local:StringToInlinesConverter x:Key="stringToInlinesConverter" />
        </Grid.Resources>
        <TextBlock local:Attached.Inlines="{Binding Text, 
            Converter={StaticResource stringToInlinesConverter}}" />
    </Grid>
</Window>

That's it. Test property is being split into words and converter into a collection of inlines. Then it gets bound to attached property which would set the Inlines property on a TextBlock control.

Injecting dependencies in NHibernate

Injecting dependencies into entities created by NHibernate looks pretty easy. You need to implement interface IInterceptor. NHibernate does it already providing class EmptyInterceptor. So your class inherits form that.

Below there's a simple implementation that looks at the container every time a poco instance is being created by NHibernate when dehydrating data from database.

    class ContainerResolvingEntityInterceptor : EmptyInterceptor
    {
        private ISession _session;
        private IWindsorContainer container;

        public TestEntityInterceptor(IWindsorContainer container)
        {
            this.container = container;
        }

        public override void SetSession(ISession session)
        {
            _session = session;
        }

        public override object Instantiate(string clazz, EntityMode entityMode, object id)
        {
            object instance = null;
            if (entityMode == EntityMode.Poco)
            {
                var type = Type.GetType(clazz);
                if (type != null)
                {
                    instance = container.Resolve(type);
                    var md = _session.SessionFactory.GetClassMetadata(clazz);
                    md.SetIdentifier(instance, id, entityMode);
                }
            }
            else
            {
                instance = base.Instantiate(clazz, entityMode, id);
            }
            return instance;
        }
    }

Now, when configuring a containere, the entities have to go there too. They can have parameterized constructors accepting services required by the class.

Suppose a class Employee depending on an interface ISalaryCalculator used to compute salary:

public class Employee : IEmployee
{
    private readonly ISalaryCalculator salaryCalculator;

    protected Employee() { }

    public Employee(ISalaryCalculator salaryCalculator)
    {
        this.salaryCalculator = salaryCalculator;
    }

    public virtual int Id { get; set; }

    public virtual string FirstName { get; set; }

    public virtual string LastName { get; set; }

    public virtual DateTime BirthDate { get; set; }

    public virtual DateTime EmployedFrom { get; set; }

    public virtual DateTime? EmployedUntil { get; set; }

    public virtual string Address { get; set; }

    decimal IProvidingSalary.CalculateSalary()
    {
        return salaryCalculator.CalculateSalary(this);
    }
}

The container contains registration of the entity as well as the required dependency:

public void InitializeContainer()
{
    container = new WindsorContainer();
    container.Register(
        Component.For<ISalaryCalculator>().ImplementedBy<SalaryCalculator>(),
        Component.For<Employee>().ImplementedBy<Employee>().LifeStyle.Transient,
        Component.For<ISessionFactory>().UsingFactoryMethod((k, ctx) => CreateSessionFactory()));
}

Looks nice. And works very well.