Incorrect Syntax near “?” : Nhibernate Generated Q

Question

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

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

This statement:

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

is producing this SQL:

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

which causes SQL to error with:

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, ?, ?)]

What can I do to fix it?

Answer 1

Workaround

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)

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

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

Old failing:

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

Work around:

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

Function "year_week" defined like this:

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

Answer 2

I ran into a similar issue with Projections.SqlFunction("concat" ...). In the end, I worked around it by using Projections.SqlProjection(...) instead. I don't like this answer, since I think it's probably less portable, but it did work for me.

Answer 3

It looks like a bug in NHibernate, but if you just want those results you can get the same result by using Projections.Distinct instead of Projections.GroupProperty, ie:

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

Alternatively if you're selecting more than just the first char of the name, then you could probably get it to work using a subselect