How to create summation with "sum" with multiple queryover columns

Question

People need to create a query with queryOver(Nhibernate) C#, to add more than one column. Example in pure sql:

SELECT SUM(coluna1 + coluna2 + coluna3 + coluna4)
FROM tabela

First I did so:

Tabela tabela = null;
Session.QueryOver<Tabela>(() => tabela)
           .Select(Projections.Sum<Tabela>(t => t.coluna1))
           .Select(Projections.Sum<Tabela>(t => t.coluna2))
           .Select(Projections.Sum<Tabela>(t => t.coluna3))
           .Select(Projections.Sum<Tabela>(t => t.coluna4))

But this way we sum each column and generates 4 columns, I would like to sum all and generate only one column.

Answer 1

Solution based in this OS response.

You can use Projections.SqlFunction, to apply a projection customized in Projections.Sum, using the NHibernate.Dialect.Function.VarArgsSQLFunction to inform the SQL bind template.

Something similar to the following example:

Tabela itemAlias = null;

var query = Session.QueryOver<Tabela>(() => itemAlias)
    .Select(Projections.Sum(
        Projections.SqlFunction(
            new NHibernate.Dialect.Function.VarArgsSQLFunction("(", " + ", ")"),
            NHibernateUtil.Double,
            Projections.Property(() => itemAlias.coluna1),
            Projections.Property(() => itemAlias.coluna2),
            Projections.Property(() => itemAlias.coluna3),
            Projections.Property(() => itemAlias.coluna4)
        )
    )
);

var result = query.SingleOrDefault<double>();

The SQL generated by this query will be something simulate to this (depending on the database):

select sum((THIS_.COLUNA1 + THIS_.COLUNA2 + THIS_.COLUNA3 + THIS_.COLUNA4)) as Y0_
from TABELA THIS_ 

Answer 2

I can’t think of a "Nhibernate" way to do this. The solution would be to execute a pure query

var query = "select C.prop1 + C.prop2 from Classe as C";

var sum = session.CreateQuery(query).UniqueResult<double>();

Edit:

Maybe you can try this way

Session.QueryOver<Table>(() => tabela)
   .Select(Projections.Sum<Table>(t => t.Prop1 + t.Prop2 + t.Prop3 + t.Prop4));