Development Category

NHibernate HQL Parameters check for NULL (Single & Comma Separated Value)

NHibernate as we know or have heard from the “dev next door” is quite a piece of ORM code, and due to my recent interactions with it I decided to share a little something I uncovered while in the cage fighting it.

In most cases a parameter needs to be passed to a query so that it can act as a filter for the results returned. Queries as well as stored procedures accept them and love them passionately.

Now HQL does as well and this is no surprise for anyone who had to deal with the NHibernate, but what is interesting is how does HQL act when a parameter is passed as NULL.

Regardless of the NHibernate mappings you may have if you are to execute a stored HQL query you would have to take into consideration the case where that parameter would have NULL as value.

HQL Example:
select x.Id, x.Name from myMappedBO x where x.Id =:Param1

Now everything is beautiful, simple and clean when Param1 has a value, but if it is equal to NULL the query would return only the rows/BO where x.Id is NULL … which as we can guess wont be a big result set since Id we most likely would want to be never NULL in 99% of the cases and even act as a Primary Key in the actual Table in the Database.

So how do we avoid this problem with HQL?

HQL Check for NULL with single value parameter:
select x.Id, x.Name from myMappedBO x where (:Param1 IS NULL OR x.Id =:Param1)

Now as you can see from the code above the IS NULL OR surrounded with parentheses fixed the NULL issue quite nicely for a parameter which we know will NOT have comma (or any other character for that matter) separated value. Notice the bold NOT.

When it comes to parameter being passed to HQL to be used in a “IN” scenario:
select x.Id, x.Name from myMappedBO x where (:Param1 IS NULL OR x.Id IN (:Param1))

The above HQL fails when translated to SQL. The reason for it is that if you have as a value for the Param1 – 1,2,3,4 the translated SQL would be something like this:

select hql_alias_0.Id, hql_alias_0.Name from SchemaName.TableName hql_alias_0 where (1,2,3,4 IS NULL OR hql_alias_0.Id IN (1,2,3,4))

The underlined SQL will kill the query instantly since the syntax is quite alien to the SQL Server.

So how can we check if Param1 is NULL and still use its multiple values separated by comma?

HQL Check for NULL with comma separated values parameter:
select x.Id, x.Name from myMappedBO x where (COALESCE(:Param1, CAST(NULL AS int)) IS NULL OR x.ID in (:Param1))

SQL comes to the rescue with its handy COALESCE function! The function as stated in the documentation by Microsoft:

Returns the first nonnull expression among its arguments.

Luckly for us the value from Param1 is considered the first “nonnull expression” when we pair the contents of the COALESCE function with a CAST(NULL AS int):

COALESCE(:Param1, CAST(NULL AS int))

When SQL executes this if the value of Param1 is NULL then the result of the function will be NULL, which is exactly what we want. Now if the value of Param1 is not null then the function will return the value which will result as NOT NULL and the OR will be executed.

Quite nifty no?

Born in a small country in the Balkans called Bulgaria (commonly mistaken by the geographically inclined westerners as Siberia), @krion spend most of his childhood being deceived that his existence mattered and that he can change the world one school year at a time.

One Response to “NHibernate HQL Parameters check for NULL (Single & Comma Separated Value)”

  1. Dimitar Reply

    That’s a pretty cool trick. It’s been a while since I have written any HQL. Recently, I have been using EF as a primary ORM. It has come a long way…

Leave a Reply