开发者

NHibernate Criteria using Projections for Substring with in clause

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.


This bug is fixed in 3.0.0.GA version.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜