I had a scenario in Oracle where i need to match a substring part of column with a list of values. i was using sqlfunction projection for applying the substring on the required column, and added that projection as part of an In Clause Restriction. Below is the simplified criteria i wrote for that.
ICriteria criteriaQuery = session.CreateCriteria<Meeting>()
.Add(Restrictions.In(
Projections.SqlFunction(
"substring",
NHibernateUtil.String,
Projections.Property("Code"),
Projections.Constant(1),
Projections.Constant(3)),
new string[] { "D01", "D02" }))
.Add(Restrictions.In("TypeId", meetingTypes));
The problem that i had with this was that the generated SQL was wrong, where the number of parameters registered for the statement are more than what the statement actually uses and some parameters are repeated even though they are not used. This causes the statement to fail with the message - ORA-01036: illegal variable name/number. Generated Query
SELECT this_.Meeting_id as Meeting1_0_2_, .....
WHERE substr(this_.Mcs_Main, :p0, :p1) in (:p2, :p3)
and this_.Meeting_Type_Id in (:p4, :p5);
:p0 = 1, :p1 = 3, :p2 = 1, :p3 = 3, :p4 = D02 , :p5 = D03 , :p6 = 101, :p7 = 102
p2 and p3 are generated again and are duplicates of p0, p1 because of which the entire query is failing.
I was able to temporarily resolve this by mapping a a new property with a formula, but i don t think that is the right approach since the formula will be executed always even when i don t need the substring to be evaluated.
Any suggestions on whether projections work fine when used with the combination of In clause, the same projection works fine when i use Equal Restriction and not In.