NHibernate Expression.Sql with multiple parameters

Question

I want to use Expression.Sql in a NH Criteria but the overload does not work and there is no documentation I can find.

Where do I enter the parameters?

I have an NH DetachedCriteria but I have to use SQL expression because it is impossible to do a LIKE in 2 directions.

So I add this

            criteria.Add(Expression.Sql(@"(
                    UPPER(RIGHT(RTRIM(LTRIM('?')), ?)) like '%'+UPPER(RIGHT(RTRIM(LTRIM(?)), ?)) 
                    OR UPPER(RIGHT(RTRIM(LTRIM(?)), ?)) like '%'+UPPER(RIGHT(RTRIM(LTRIM('?')), ?))  
                )  and len('?') >= ?",
                            new Object[]{
                                "aaa",
                                "aaa",
                                "aaa",
                                "aaa",
                                "aaa",
                                "aaa",
                                "aaa",
                                "aaa",
                                "aaa",
                                "aaa",
                            }
                            , new NHibernate.Type.IType[]{
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String,
                                NHibernateUtil.String
                            }
                ));

I keep getting index out of bounds exception

I guarantee the parameters match up. Even the error shows me a query with 110 '?''s and 10 parameters.

How could I possibly do this?

Answer 1

Several of the question marks have quotes around them: '?'

These would not be interpreted as parameter placeholders, but as string literals containing a single question mark. So you really only have 7 parameters, not 10, but you are passing in 10 values.