CODEWITHSUNDEEP
sql-server-2008nhibernate
kshahar
kshahar

Reputation: 10513

How to group by SqlFunction with VarArgsSQLFunction in NHibernate

I'm trying to implement the following SQL query with QueryOver:

SELECT [Time]/1000
FROM TableName
GROUP BY [Time]/1000

Here's my current attempt:

var result = session
    .QueryOver<TableName>
    .Select(Projections.GroupProperty(
        Projections.SqlFunction(
            new VarArgsSQLFunction("(", "/", ")"),
            NHibernateUtil.Int64,
            Projections.Property("Time")
            Projections.Constant(1000))
    ))
    .List<object>();

Unfortunately I get the following exception (GenericADOException):

could execute query
[ SELECT (this_.Time/@p0) as y0_ FROM [TableName] this_ GROUP BY (this_.Time/?) ]

And the inner exception:

Incorrect syntax near ?.

I can replace the "GroupProperty" with a "Sum" and it works. Any idea what's missing?

Update: Apparently it's a bug in NHibernate. See also this question.

Upvotes: 0

Views: 602

Answers (1)

Martin Ernst
Martin Ernst

Reputation: 5679

Why don't you just use Projections.SqlGroupProjection:

var result = session
    .QueryOver<TableName>
    .Select(Projections.SqlGroupProjection(
                        Time/1000 AS TimeValue", 
                        "Time/1000", 
                        new[]{"TimeValue"}, 
                        new[]{NHibernateUtil.Int32}))
    .List<object>();

Upvotes: 1

Related Questions