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

Question

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

Robert

Answer 1

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.

Matte

excellent, thank you. for the alias on the sql expression... i used "this_.Number" is that ok, am I guaranteed that NHibernate will always use this_.? – Robert

because I did alias my criteria with "wi" - session.CreateCriteria<WorkItem>("wi") - but i can't use "wi.Number" in the sql expression because it puts it into the T-SQL literally (if that makes sense). – Robert

and sense "wi" within the regular criteria creation calls actually get converted to "this_" in the T-SQL sent to the server i went ahead and put the alias in the sql expression to "this_.Number" – Robert

@Matte - lol when you put "{alias}" in the sql expression I though you meant "put the alias you created for the criteria here".. I wasn't think that you meant to actually type "{alias}" literally. but this is perfect, i'd give you +10 if I could, this helped a ton. – Robert