Call sql function with nhibernate?

Question

I want to query like this:

select * from table where concat(',', ServiceCodes, ',') like '%,33,%';
select * from table where  (','||ServiceCodes||',') like '%,33,%';

So, I wrote this code:

ICriteria cri = NHibernateSessionReader.CreateCriteria(typeof(ConfigTemplateList));
cri.Add(Restrictions.Like(Projections.SqlFunction("concat", NHibernateUtil.String, Projections.Property("ServiceCodes")), "%,33,%"));

I get sql like this:

select * from table where  (ServiceCodes) like '%,33,%';

But this is not what I want, how to do it ??? thank!

Answer 1

You were on the right track, but you forgot to add what you want to accomplish.

Try the following:

cri.Add(Restrictions.Like(
            Projections.SqlFunction("concat",
                                    NHibernateUtil.String,
                                    Projections.Constant(","), 
                                    Projections.Property("ServiceCodes"),
                                    Projections.Constant(",")),
        "%,33,%"));