I am using Nhibernate 188.8.131.5201 with SybaseSQLAnywhere12Dialect and trying to use a simple calculation within a sum linq function.
I would like to use Linq rather than HQL to achieve this and would be happy to extend Nhibernate with a default linq to hql generator.
Using the following Linq query raises the exception<blockquote>
System.NotSupportedException: Expression type 'NhSumExpression' is not supported by this SelectClauseVisitor</blockquote>
var query = (from c in session.Query<Entity>() select c.FieldValue / 100M).Sum()
<strong>Expected SQL Statement</strong>
SELECT SUM(FieldValue / 100.0) FROM TableName
If the same query is run without the Sum() function it performs the calculation correctly, as such am confused why the Sum() wouldn't work.Answer1:
You may try to rewrite your LINQ query a bit, just like this one:
var result = ((from c in session.Query<Entity>() select c.FieldValue).Sum()) / 100;
Resulting query could also be optimized by moving division operator out of the sum:
SELECT SUM(FieldValue) / 100 FROM TableNameAnswer2:
There is an open issue when using constants within Linq group function (Max, Min, Sum) functions. If another field is used it will work correctly.
Found bug within NHibernate 3.3.3<br /><a href="https://nhibernate.jira.com/browse/NH-3376" rel="nofollow">https://nhibernate.jira.com/browse/NH-3376</a>