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

Question

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

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

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

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

First I did it this way:

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 in this way I am adding each column and generating 4 columns, I would like to add all of them and generate a single column.

sql c# nhibernate query

asked by anonymous 05.07.2016 / 21:39

2 answers

0

I can not think of an "NHibernate" way of doing 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 it this way

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

05.07.2016 / 22:10

Remove duplicate data and return the highest value date field Get names of teams in different tables!

score 2 · Accepted Answer

Solution based on in this SO response .

You can use Projections.SqlFunction , to apply a custom projection in Projections.Sum , using 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 somewhat simulate this (depending on the database):

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