Incorrect Syntax near "?" : Nhibernate Generated Query

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?

It's nice to know I'm not the only one who's experienced this. — Ian McLaird, Jun 12 '12 at 13:46

score 0 · Answer 1 · answered May 22 '12 at 08:52

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

score 0 · Answer 2 · answered Jun 12 '12 at 13:45

0

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.

answered Jun 12 '12 at 13:45

Ian McLaird

5,507
2
22
31

score 0 · Answer 3 · edited Oct 05 '12 at 07:24

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

Incorrect Syntax near "?" : Nhibernate Generated Query

3 Answers3

Linked