Executing Query with SUM in HQL?

Question

Executing Query with SUM in HQL?

Navigation

#1 by (1 votes)

1

I'm trying to use SUM to add an attribute of an entity using HQL of NHibernate , but every time I run query returns empty.

I'm following the example here but I still could not do it. How to do this?

I'm trying like this.

public IList<Conta> findAllContasReceber() 
{
    ISession _session = getSession();
    String SQL = "SELECT c.cliente, c.historico, c.dtLancamento, c.dtVencimento, SUM(c.valorPagar), " +
                 "c.valorAcrescimo, c.valorFinal, c.dtPagamento, c.tipoConta, c.planoConta, c.status, c.venda " +
                 "FROM Conta c WHERE (c.tipoConta = 1) AND (c.status = 0) GROUP BY c.dtVencimento, c.cliente ORDER BY c.dtVencimento";

    IList<Conta> list = _session.CreateQuery(SQL).List<Conta>();

    return list;
}

Entity

[Serializable]
public class Conta 
{    
     public virtual long id                      { set; get; }        
     public virtual Cliente cliente              { set; get; }
     public virtual String historico             { set; get; }
     public virtual DateTime dtLancamento        { set; get; }
     public virtual DateTime dtVencimento        { set; get; }
     public virtual decimal valorPagar           { set; get; } //total vendas
     public virtual decimal valorAcrescimo       { set; get; } //total acrescimo
     public virtual decimal valorFinal           { set; get; } //total pagar

     public virtual DateTime dtPagamento         { set; get; }
     public virtual int tipoConta                { set; get; }  //1 receber, 2 pagar
     public virtual PlanoDeConta planoConta      { set; get; }
     public virtual int status                   { set; get; } //0 ativa, 1 fechada, 2 cancelada, 3 aguardando pagamento
     public virtual Venda venda                  { set; get; }


     public Conta() 
     {                
     }   


}

c# nhibernate hql

asked by anonymous 05.10.2016 / 12:41

1 answer

How much to use @ Html.ActionLink or Javascript What is the difference between freetexttable and containtable

score 1 · Accepted Answer

Solved. After much research on a way to do this and the reasons for the exception I found a way to do it. Anyway, it worked 100%.

I did so.

public IList<Conta> findAllContasReceber() {
            ISession _session = getSession();
            String SQL = "SELECT new Conta(c.cliente, c.historico, c.dtLancamento, c.dtVencimento, SUM(c.valorPagar), SUM(c.valorAcrescimo), SUM(c.valorFinal), c.status) " + 
                         "FROM Conta c WHERE (c.tipoConta = 1) AND (c.status = 0) " + 
                         "GROUP BY c.cliente, c.dtVencimento " + 
                         "ORDER BY c.dtVencimento ";
            IList<Conta> list = _session.CreateQuery(SQL).List<Conta>();
            return list;
        }

And I created a constructor in the entity

[Serializable]
    public class Conta {

        public virtual long id                      { set; get; }        
        public virtual Cliente cliente              { set; get; }
        public virtual String historico             { set; get; }
        public virtual DateTime dtLancamento        { set; get; }
        public virtual DateTime dtVencimento        { set; get; }
        public virtual decimal valorPagar           { set; get; } //total vendas
        public virtual decimal valorAcrescimo       { set; get; } //total acrescimo
        public virtual decimal valorFinal           { set; get; } //total pagar
        public virtual DateTime dtPagamento         { set; get; }
        public virtual int tipoConta                { set; get; }  //1 receber, 2 pagar
        public virtual PlanoDeConta planoConta      { set; get; }
        public virtual int status                   { set; get; } //0 ativa, 1 fechada, 2 cancelada, 3 aguardando pagamento
        public virtual Venda venda                  { set; get; }


        public Conta() {
        }

        public Conta(Cliente cliente, String historico, DateTime dtLancamento, DateTime dtVencimento, 
                    decimal valorPagar, decimal valorAcrescimo, decimal valorFinal, int status){

                        this.cliente = cliente;
                        this.historico = historico;
                        this.dtLancamento = dtLancamento;
                        this.dtVencimento = dtVencimento;
                        this.valorPagar = valorPagar;
                        this.valorAcrescimo = valorAcrescimo;
                        this.valorFinal = valorFinal;
                        this.status = status;
        }

    }