NHibernate 标准使用带有 in 子句的子字符串投影

发布于 2024-08-10 22:39:47 字数 1075 浏览 7 评论 0原文

我在 Oracle 中有一个场景，我需要将列的子字符串部分与值列表进行匹配。我使用 sqlfunction 投影将子字符串应用于所需的列，并将该投影添加为子句限制的一部分。以下是我为此编写的简化标准。

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));

我遇到的问题是生成的 SQL 是错误的，为语句注册的参数数量比语句实际使用的参数数量多，并且某些参数即使没有使用也会重复。这会导致语句失败并显示消息 - ORA-01036：非法变量名/编号。生成的查询

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 和 p3 再次生成，并且是 p0、p1 的重复项，因此整个查询失败。

我能够通过用公式映射新属性来临时解决此问题，但我认为这不是正确的方法，因为即使我不需要评估子字符串，公式也会始终执行。

关于投影与 In 子句组合使用时是否正常工作的任何建议，当我使用相等限制而不是 In 时，相同的投影可以正常工作。

原文

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.

分享到QQ

分享到微博