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 <[email protected]> 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
> 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 thesubstringto
> be evaluated.
>
> Any suggestions on whetherprojectionswork 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.
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
