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 + "%"))
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.