NHibernate and the missing OperatorProjection

It's been a long while, unfortunately i can't seem to find time to tend to my blog :/

I've been using NHibernate for some years now and although i consider it to be great there are some things that bug me. I've been looking at some questions over at SO (like for example http://stackoverflow.com/questions/4243890/nhibernate-restriction-with-sum-of-2-columns and http://stackoverflow.com/questions/2936700/nhibernate-criteria-how-to-filter-on-combination-of-properties) and it seems that i am not the only one that has this problem.

I consider it odd that not all standard SQL-92 operators -mathematical and maybe bitwise / unary ?- are supported in an intuitive way (there, i said it). In fact there is specific implementation for each of the supported conditional operators via Restrictions (Eq, Lt, Le etc).

I consider the Criteria API the strongest query engine for NHibernate and if you sweeten it with some LINQ candy just to get compilation support you can get the most out of it.

So, i looked about to find out how hard it would be to make an OperatorProjection.

Apparently it wasn't that hard :-)

The solution was to create my own Projection that is responsible to generate the necessary SQL, which derives from NHibernate's own SimpleProjection.

Lets say, for the sake of argument, that i want to return a given property with an added number.

This is pretty easy in hql, its like writing sql:

select t.Id, t.Duration, t.Duration + 5 from WorkSpecification

In vanilla Criteria API its not that simple and it hides a couple of traps:

crit.SetProjection(Projections.Id(),
    Projections.Property("Duration"),
    Projections.SqlProjection("{alias}.DaysDuration + 5",
                              new[] {"DaysDuration"},
                              new[]
                                  {
                                      NHibernateUtil.Int32
                                  }));

Its not simple because you have to write SQL and that means that the "DaysDuration" reference inside the SqlProjection means directly to a Column named "DaysDuration" and that point the ORM looses its power and flexibility.

Secondly the {alias} part commands the Criteria engine to inject at that point the alias of the root entity. That distinctively destroys any hope to make joins and apply the desired projection to the joined row (you can make subqueries via DetachedCriteria but that's not the same).

With OperatorProjection it can be written as

crit.SetProjection(Projections.Id(),
               Projections.Property("Duration"),
               new ArithmeticOperatorProjection("+",
                                                NHibernateUtil.Int32,
                                                Projections.Property("Duration"), Projections.Constant(5))
);

which generates this piece of SQL

SELECT this_.Id as y0_, this_.DaysDuration as y1_, (this_.DaysDuration + 5) as y2_ FROM WorkSpecification

The ugly part is that you pass the operator symbol as a string (but we may be able to do something about it as you will see in part 2) but then again now you don't need to rely to SQL. The only thing required is knowledge of the domain.

And since the Restrictions API also includes IProjections as parameters now this is also feasable:

zcrit.Add(
    Restrictions.Eq(new ArithmeticOperatorProjection("+",
                                    NHibernateUtil.Int32,
                                    Projections.Property("Duration"), Projections.Constant(5)
                                    )
                                    , 6
         );

which generates this piece of SQL

SELECT this_.(…) FROM WorkSpecification this_ WHERE (this_.DaysDuration + @p0) = @p1; @p0 = 5, @p1 = 6

Interestingly and as a side benefit Microsoft's SQL Server also uses the + operator to concatenate strings. And the result of the experiment was...

zcrit.Add(
        Restrictions.Eq(new ArithmeticOperatorProjection("+",
                            NHibernateUtil.String,
                            Projections.Property("FirstName"), Projections.Constant(" "), Projections.Property("LastName")
                            )
                        , “foo bar”
                        )
    );

which generates this piece of SQL

SELECT this_.(…) FROM PersonSpecification this_ WHERE (this_.FirstName + @p0 + this_.LastName) = @p1;
@p0 = ' ', @p1 = 'foo bar'

So what makes all this happen? Check it out below:

public abstract class OperatorProjection : SimpleProjection
    {
        private readonly IProjection[] args;
        private readonly IType returnType;
        
        private string op;
        private string Op { get { return op; }
            set
            {
                var trimmed = value.Trim();
                if (System.Array.IndexOf(AllowedOperators, trimmed) == -1)
                    throw new ArgumentOutOfRangeException("value", trimmed, "Not allowed operator");
                op = " " + trimmed + " ";
            }
        }

        public abstract string[] AllowedOperators { get; }

        protected OperatorProjection(string op, IType returnType, params IProjection[] args)
        {
            this.Op = op;    
            this.returnType = returnType;
            this.args = args;
        }

        public override SqlString ToSqlString(ICriteria criteria, int position, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
        {
            SqlStringBuilder sb = new SqlStringBuilder();
            sb.Add("(");

            for (int i = 0; i < args.Length; i++)
            {
                int loc = (position + 1) * 1000 + i;
                SqlString projectArg = GetProjectionArgument(criteriaQuery, criteria, args[i], loc, enabledFilters);
                sb.Add(projectArg);

                if (i < args.Length - 1)
                    sb.Add(Op);
            }
            sb.Add(")");
            sb.Add(" as ");
            sb.Add(GetColumnAliases(position)[0]);
            return sb.ToSqlString();
        }

        private static SqlString GetProjectionArgument(ICriteriaQuery criteriaQuery, ICriteria criteria,
                                                       IProjection projection, int loc,
                                                       IDictionary<string, IFilter> enabledFilters)
        {
            SqlString sql = projection.ToSqlString(criteria, loc, criteriaQuery, enabledFilters);
            return StringHelper.RemoveAsAliasesFromSql(sql);
        }

        public override IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
        {
            return new IType[] { returnType };
        }

        public override bool IsAggregate
        {
            get { return false; }
        }

        public override bool IsGrouped
        {
            get
            {
                foreach (IProjection projection in args)
                {
                    if (projection.IsGrouped)
                    {
                        return true;
                    }
                }
                return false;
            }
        }

        public override SqlString ToGroupSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
        {
            SqlStringBuilder buf = new SqlStringBuilder();
            foreach (IProjection projection in args)
            {
                if (projection.IsGrouped)
                {
                    buf.Add(projection.ToGroupSqlString(criteria, criteriaQuery, enabledFilters)).Add(", ");
                }
            }
            if (buf.Count >= 2)
            {
                buf.RemoveAt(buf.Count - 1);
            }
            return buf.ToSqlString();
        }
    }

Which is not really complicated and in fact is modified code from NHibernate's own Projections.SqlFunction(), and is a handy-dandy abstract class in case some one wants to implement some other operator. Note that the above class expects pairs of projections so it is not suitable for Unary Operators, although the only thing that has to change is the ToSqlString() method.

So the actual usable implementations look like this:

public class ArithmeticOperatorProjection : OperatorProjection
    {
        public ArithmeticOperatorProjection(string op, IType returnType, params IProjection[] args)
            : base(op, returnType, args)
        {
            if (args.Length < 2)
                throw new ArgumentOutOfRangeException("args", args.Length, "Requires at least 2 projections");
        }

        public override string[] AllowedOperators
        {
            get { return new[] { "+", "-", "*", "/", "%" }; }
        }
    }

    public class BitwiseOperatorProjection : OperatorProjection
    {
        public BitwiseOperatorProjection(string op, IType returnType, params IProjection[] args)
            : base(op, returnType, args)
        {
            if (args.Length < 2)
                throw new ArgumentOutOfRangeException("args", args.Length, "Requires at least 2 projections");
        }

        public override string[] AllowedOperators
        {
            get { return new[] { "&", "|", "^" }; }
        }
    }

You might notice that during sql generation i explicitly place the generated sql inside parenthesis () because, well they don't really hurt :-P.
Jokes aside, i haven't tested this in enough complicated scenarios to see if it behaves properly without parenthesis. Besides that, in the case of using multiple operators side by side, there is operator precedence that has to be taken into account, and at least for now there is no code to detect and/or fix that.

So it all boils down to the modular nature of NHibernate and the ability to write your own tools without having to necessarily modify the source. Can you say "win" ?

Enabling Filters on mapped entities

I was trying to figure out if I could persuade NHibernate to enable filters on my <many-to-one /> entities and i fell on this discussion http://bit.ly/6W7WIA on the nhibernate-development google group. In short, <many-to-one /> filters should not work because filters where designed to filter data, not modify the domain.A valid viewpoint but i needed that functionality.


I have to think that filters should work out-of-the box and the developer should be responsible of enabling or disabling them. The reason is that i have a setup, that the represented domain can only reflected using filters. That is, applying WHERE clauses with dynamic parameters that are generated from the UI and that parameters remain the same for the length of the Request. As such the WHERE attribute on the class


Digging up the NH 2.1.2 source code i've found the single piece of code that is responsible for the behaviour.
Open up the NHibernate.Loader.Entity.EntityJoinWalker class and at approximately line 24 exists this piece of code:

SqlStringBuilder whereCondition = WhereString(Alias, uniqueKey, batchSize)
      //include the discriminator and class-level where, but not filters
      .Add(persister.FilterFragment(Alias, new CollectionHelper.EmptyMapClass<string, IFilter>()));

if you change it to

SqlStringBuilder whereCondition = WhereString(Alias, uniqueKey, batchSize)
       .Add(persister.FilterFragment(Alias, enabledFilters));

any enabled filters will be applied to your <many-to-one /> mapped entities as long as the filter exists on the entity definition mapping.

This little modification additionally enables filters for ISession.Get() and it works normally with lazy loading. If you are going to use ISession.Get() note that the cache is hit first before going to the datastore. In my setup, because the filter parameter defines a different entity instance for a given entity-id the second-level cache is not (and must not) be used, so take that into consideration before applying the modification to your solution.


I am thinking that if we could pass a parameter on the WHERE attribute on the class mapping (and ensure that this where fragment is used on ANSI-joins) none of this would have a reason to exist

Many-to-one and Composite-id mapping with NHibernate

Nhibernate's reference documentation sometimes leaves something to be desired. I was looking for the correct syntax to map a which has a and the documentation did not discuss it and a google search revealed nothing. While poking around the 'nhibernate-mapping.xsd' i found out that the element supports having elements underneath it so as to have the following syntax

<many-to-one name="Foo">
  <column name="FK_Column1">
  <column name="FK_Column2">
</column>

where the Foo class is mapped as

<class name="Foo" table="Foo">
  <composite-id class="FooCompId">
    <key-many-to-one name="SomeEntityA" column="PK_Column1">
    <key-many-to-one name="SomeEntityB" column="PK_Column2">
  <composite-id>
  ....
</composite-id>

note here that the Foo class uses 2 properly mapped classes (SomeEntityA and SomeEntityB) hence the <key-many-to-one/>
interestingly i used this as part of a <subclass /> element and it worked normally.

And to map a <bag /> of "Foo" from an other entity this syntax worked

<bag name="Foos" inverse"true">
  <key>
    <column name="FK_Column1">
    <column name="FK_Column2">
  </key>
  <one-to-many class="Foo" />
</bag>

the only requirement is that the order of the columns declared must match the order of the <key-many-to-one/> declared in the composite-id