Nhibernate query condition within sum

Question

I am trying the following code but nhibernate is throwing the following exception:

Expression type 'NhSumExpression' is not supported by this SelectClauseVisitor.

var data =
                (
                    from a in session.Query<Activity>()
                    where a.Date.Date >= dateFrom.Date && a.Date.Date <= dateTo.Date
                    group a by new { Date = a.Date.Date, UserId = a.RegisteredUser.ExternalId } into grp
                    select new ActivityData()
                    {
                        UserID = grp.Key.UserId,
                        Date = grp.Key.Date,
                        Bet = grp.Sum(a => a.Amount < 0 ? (a.Amount * -1) : 0),
                        Won = grp.Sum(a => a.Amount > 0 ? (a.Amount) : 0)
                    }
                ).ToArray();

I've been looking around and found this answer

But I am not sure what I should use in place of the Projections.Constant being used in that example, and how I should create a group by clause consisting of multiple fields.

What NHibernate version? – Oskar Berggren Aug 21 '13 at 11:58 — Oskar Berggren, Aug 21 '13 at 11:58

score 1 · Answer 1 · answered Aug 21 '13 at 15:07

It looks like your grouping over multiple columns is correct.

This issue reported in the NHibernate bug tracker is similar: NH-2865 - "Expression type 'NhSumExpression' is not supported by this SelectClauseVisitor."

Problem is that apart from the less-than-helpful error message, it's not really a bug as such. What happens in NH-2865 is that the Sum expression contains something which NHibernate doesn't know how to convert into SQL, which result in this exception being thrown by a later part of the query processing.

So the question is, what does you sum expression contains that NHibernate cannot convert? The thing that jumps to mind is the use of the ternary operator. I believe the NHibernate LINQ provider has support for the ternary operator, but maybe there is something in this particular combination that is problematic.

However, I think your expressions can be written like this instead:

Bet = grp.Sum(a => Math.Min(a.Amount, 0) * -1),  // Or Math.Abs() instead of multiplication.
Won = grp.Sum(a => Math.Max(a.Amount, 0))

If that doesn't work, try to use a real simple expression instead, like the following. If that works, we at least know the grouping itself work as expected.

Won = grp.Sum(a => a.Amount)

The Math.Min/Max threw the same exception. The grouping in my snippet works though. I've went another route for the time being as needed a quick release, but I would still like to know if there's a way to get this done with one query (had to split into two queries and do some left joins). — Matthew Grima, Aug 21 '13 at 15:25
@MatthewGrima That's unfortunate. You can add support for Math.Min() and Math.Max() by extending NHibernate (or as part of NH itself to fix issue https://nhibernate.jira.com/browse/NH-3514). Then again, I'm not entirely sure why the ternary operator was a problem in this case - it seems like that should also be fixable. — Oskar Berggren, Aug 21 '13 at 16:18
Will leave this question open for future users and my own benefit, maybe there's a solution for it. Thanks for the help — Matthew Grima, Aug 22 '13 at 07:28

Nhibernate query condition within sum

1 Answers1