“?"附近的语法不正确:Nhibernate生成的查询 [英] Incorrect Syntax near "?" : Nhibernate Generated Query

问题描述

在Nhbernate中使用位置参数时遇到问题.

I am getting problem while working with positional parameters in Nhbernate.

条件GroupProperty发出带有命名变量和位置变量的sql.

Criteria GroupProperty is emitting sql with both named and positional variables.

此声明:

session.CreateCriteria(typeof(MatchStageFrom))
                .SetProjection(Projections.GroupProperty(
                    Projections.SqlFunction("substring", NHibernateUtil.String, Projections.Property("LastName"), Projections.Constant(0), Projections.Constant(1))
                    )
                );

正在生成此SQL:

SELECT substring(this_.LAST_NAME, @p0, @p1) as y0_ FROM MATCH_STAGING_FROM this_ GROUP BY substring(this_.LAST_NAME, ?, ?)

这会导致SQL错误:

Incorrect syntax near '?'.

could not execute query
[ SELECT substring(this_.LAST_NAME, @p0, @p1) as y0_ FROM MATCH_STAGING_FROM this_ GROUP BY substring(this_.LAST_NAME, ?, ?) ]
Positional parameters: #0>0 #1>1 #2>0 #3>1
[SQL: SELECT substring(this_.LAST_NAME, @p0, @p1) as y0_ FROM MATCH_STAGING_FROM this_ GROUP BY substring(this_.LAST_NAME, ?, ?)]

该如何解决?

推荐答案

解决方法

使用group by和SqlFunction参数时，NHibernate中存在一个错误. 如果应用Projections.GroupProperty(customProjection)，则投影中的参数仅发送一次(用于SELECT子句)，而GROUP BY子句中的参数在查询中处于位置且缺失的位置." (请参阅)

There is a bug in NHibernate when using group by and SqlFunction parameters. "If one applies Projections.GroupProperty(customProjection), the parameters in the projection are sent only once (for the SELECT clause), while the parameters in the GROUP BY clause are positional and missing in the query..." (see)

遇到相同的错误，并通过在运行时向NHibernate添加自定义SQL函数来解决该错误， (见)

Ran into the same bug and solved it by adding Custom SQL Functions to NHibernate at Runtime, (see)

解决方法将常量参数从Projections.SqlFunction调用中移出，并移入自定义函数的定义("year_week").

The workaround moves constant parameters out of the Projections.SqlFunction call and into the definition of the custom function ("year_week").

老失败:

Projections.GroupProperty(
   Projections.Cast(NHibernateUtil.AnsiString,
      Projections.SqlFunction("to_char", NHibernateUtil.AnsiChar,
         Projections.Property(() => myAlias.Date),
         Projections.Constant("IYYYIW") // Turns into "?" in group by
      )
   )
)

解决方法:

Projections.GroupProperty(
   Projections.Cast(NHibernateUtil.AnsiString,
      Projections.SqlFunction("year_week", NHibernateUtil.AnsiChar,
         Projections.Property(() => myAlias.Date)
         // constant moved to function definition
      )
    )
)

函数"year_week"的定义如下:

Function "year_week" defined like this:

DialectExtensions.RegisterFunction(sessionFactory, "year_week", new SQLFunctionTemplate(NHibernateUtil.String, "TO_CHAR(?1,'IYYYIW')"));

这篇关于“?"附近的语法不正确:Nhibernate生成的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！