Search code examples
nhibernatenhibernate-criteria

NHibernate - easiest way to do a LIKE search against an integer column with Criteria API?

I'm trying to do a like search against an integer column, what I need to do is actually cast the column to a varchar and then do the like search. Is this possible? what's the easiest way to do this using the Criteria API?

var search = "123";
criteria.Add(Restrictions.Like("Number", "%" + search + "%"))
Solution

  • If Number were a string, then it would be easy :

    .Add(Restrictions.Like("Number", "some_value",MatchMode.Anywhere))

    Since you have a number, NHibernate will check the type of Number and if you give it a string it will throw an exception.

    Not sure why the NH team didn't provide an overload with object as parameter and a MatchMode parameters ....

    Anyhow, you can still do it like this : 

    .Add(Expression.Sql("{alias}.Number like ?", "%2%", NHibernateUtil.String))

    Edit

    About the alias :

    (i can't find where the documentation talks about this but here's my understanding of it )

    {alias} returns the alias used inside by NH for the most recent CreateCriteria. So if you had :

    session.CreateCriteria<User>("firstAlias")
       .CreateCriteria("firstAlias.Document", "doc")
       .Add(Expression.Sql("{alias}.Number like ?", "%2%",  
                           NHibernateUtil.String)).List<User>();

    {alias} in this case would be 'doc' - so you would end up with : doc.Number .

    So, always use {alias} after the CreateCriteria whose alias you need to use.