Using NHibernate criteria to summarize multiple properties / columns

Question

Does anyone know how to express the following SQL statement using NHibernate criteria?

SELECT SUM(Val1 + Val2) FROM SomeTable

It seems simple, but AFAIK I cannot find a way to do this without returning an array of values, summing Val1 + Val2 separately, and then summing from the array, which I want to avoid.

Answer 1

Well, after reading the question for the nth time with this exact problem, I decided to write an implementation that does not include an SQL record.

You can check the implementation at http://savale.blogspot.com/2011/04/nhibernate-and-missing.html , with which you can write:

criteria.SetProjection(
                 new ArithmeticOperatorProjection("+",
                                 NHibernateUtil.Int32,
                                 Projections.Property("Prop1"), Projections.Property("Prop2")
                                                  )
                );

Answer 2

, @Jaguar NHibernate OperatorProjection :

session.CreateCriteria().SetProjection(
    Projections.Property<SomeType>(val => val.Id),
    Projections.Property<SomeType>(val => val.Duration),
    Projections.SqlFunction(
        new VarArgsSQLFunction("(", "+", ")"),
        NHibernateUtil.Double,
        Projections.Property<SomeType>(val => val.Duration),
        Projections.Constant(1)
    )
).List();

Tomek.

:

Projections.Sum(
    Projections.SqlFunction(
        new VarArgsSQLFunction("(", "+", ")"),
        NHibernateUtil.Int32,
        Projections.Property<SomeTableType>(val => val.Val1),
        Projections.Property<SomeTableType>(val => val.Val2)
    )
)

VarArgsSQLFunction.

Answer 3

, Val1 Val2 . NHibernate.Criterion.ICriterion. ToSqlString.

Answer 4

or you can use HQL to achieve the same. it allows you to work with your .net objects and then requests the server accordingly.

you can create your request as follows

var hqlQuery= string.format(" select a.prop1 + a.prop2 from ClassA as a where a.Id={0}",val);

return session.CreateQuery(hqlQuery).UniqueResult<double>();