invoke sql function using 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 similar :

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

But it is not what I want，how to do it??? thanks!

Answer 1

You were in the right track, but you forgot to add what you wanted to concat.

Try this:

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

Answer 2

thanks,Diego Mijelshon,you are right. the code returns like this: select * from table where (','||ServiceCodes||',') like '%,33,%';

but in mysql，it can't return right data. This is the correct: select * from table where concat(',', ServiceCodes, ',') like '%,33,%';

how to write using nhibernate similar this: select * from table where concat(',', ServiceCodes, ',') like '%,33,%';