1

I've got a query builder that builds a parameterised HQL query for doing OData filtering. When building a query against a property being NULL, the where claluse of the HQL looks like this...

... where $entity.Property is ?

...where the ? gets replaced by a parameter who's value is NULL. However, when I call session.CreateQuery(hql) with the above query, the SQL executed is not an is null query, but becomes a =@p1 query with @p1 filled in to be NULL. This obviously doesn't do what I want.

If I make the HQL explicitly $entity.Propery is null then everything works fine, but I feel like I'm missing something or doing something stupid.

What is going on here?

GoatInTheMachine
  • 3,583
  • 3
  • 25
  • 35

1 Answers1

1

According to this answer IS is not an operator.

Notice something important, there. There is no such thing as the "IS" operator in T-SQL. There is specifically the IS [NOT] NULL operator, which compares a single expression to NULL.

So if you want to use IS NULL maybe you will need to create an dynamic query, or maybe using your parameter like ? IS NULL.

Najera
  • 2,869
  • 3
  • 28
  • 52
  • I guess in that case NHibernate should really throw an exception if you pass it a parameterised HQL query with `x IS ?` as it doesn't make sense. – GoatInTheMachine Nov 22 '17 at 10:23