IFNULL in NHibernate

Question

Right now I have something like this in NHibernate:

Expression.Like(property, value, MatchMode.Anywhere)

and that generates SQL like:

property LIKE '%value%'

which is fine for that case. In another case, I want the SQL:

IFNULL(property LIKE '%value%', 0)

but I don't see any example in the manual that refers to IFNULL, nor can I even find any plain-ol' API docs.

I found Expression.Sql(), but only the one example in the manual, and it scares me a little both to use something for which I haven't seen any real docs, and to be doing anything with SQL myself when I'm using NHibernate to try to get away from that.

Is there a better way to do IFNULL in NHibernate than Expression.Sql()?

Answer 1

It isnt a answer per se but i suggest you to look for COALESCE(). Here you can see why.

Answer 2

I'm assuming this is in a WHERE clause, at which point IFNULL(property LIKE '%value%', 0) is not valid SQL since LIKE evaluates to true or false, and 0 is neither of those.

I suspect you actually want property LIKE '%value%' OR property IS NULL?

If this is the case:

.Add(
    Expression.Disjunction()
    .Add(Expression.Like(property, value, MatchMode.Anywhere))
    .Add(Expression.IsNull(property))
)