NHibernate Criteria using Projections for Substring with in clause

[Dinesh]

I had a scenario in Oracle where i neeed 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 included 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 paramaters 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 dont think that is the right approach since the
formula will be executed always even when i dont 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. Is the Criteria i used correct.

[Nieve]

Hello,
I've been having the same problem and didn't manage to figure out what
am I doing wrong.
The following code-
ICriteria criteria = session.CreateCriteria(typeof (Employee));

IProjection postCodeSubstring = Projections.SqlFunction
("substring", NHibernateUtil.String, Projections.Property
("FirstName"),
Projections.Constant(1), Projections.Constant(2));

List<string> deptsIds = new List<string> {"one", "two", "three",
"four", "five"};
criteria.Add(Restrictions.In(postCodeSubstring, deptsIds));

Provides this SQL:
SELECT this_.Id as Id0_0_, this_.FirstName as FirstName0_0_,
this_.LastName as LastName0_0_, this_.Context as Context0_0_,
this_.Store_id as Store5_0_0_ FROM [Employee] this_ WHERE substring
(this_.FirstName, @p0, @p1) in (@p2, @p3, @p4, @p5, @p6);@p0 = 1, @p1
= 2, @p2 = 1, @p3 = 2, @p4 = 'one', @p5 = 'two', @p6 = 'three', @p7 =
'four', @p8 = 'five'

What am I doing wrong here?
Any ideas would be highly appreciated :)

On Nov 14 2009, 2:20 pm, Dinesh <dinesh...@gmail.com> wrote:
> I had a scenario in Oracle where i neeed to match asubstringpart of

> column with a list of values. i was using sqlfunction projection for

> applying thesubstringon the required column , and included that

> Any suggestions on whetherprojectionswork fine when used with the