3

I use the fallowing HQL-Query with NHibernate:

from Contact a where 
a.Id in (select x.Person.Id from PersonCompany x 
            inner join x.Company y 
            inner join y.Addresses z 
         where isnull(z.Street,'')+isnull(z.PostalCode,'')  Like :val)

In this query, NHibernate tries to convert :val (which is a string) to a double. z.Street and z.PostalCode are string-fields which can bee null. It looks like NHibernate has a problem with the first isnull() in the where-clause. when I use z.Street+isnull(z.PostalCode,'') it is working. I have also tried cast(isnull(z.Street,'')+isnull(z.PostalCode,'') as string) but this is also not working, because NHibernate has a Problem with the cast-function (it generates more then two parameters). Can someone help me, how I can solve this with NHibernate? - Perhaps there is another way to write the where-condition?

I use NHibernate 3.2

BennoDual
  • 5,865
  • 15
  • 67
  • 153

1 Answers1

3

Try concat(coalesce(z.Street,''), coalesce(z.PostalCode,''))

Diego Mijelshon
  • 52,548
  • 16
  • 116
  • 154
  • this genereates the fallowing where: (isnull(addresses3_.Street+'')+isnull(addresses3_.PostalCode+'')) which throws an SqlException with message The isnull function requires 2 argument(s) – BennoDual Mar 05 '12 at 19:44
  • Try the new version. `isnull` is not registered for most dialects; `coalesce` is the standard. It might work when used on its own because NHibernate passes it through. – Diego Mijelshon Mar 05 '12 at 19:56