Running Query with SUM in HQL?

Asked

Viewed 145 times

1

I’m trying to use the SUM to sum an attribute of an entity using HQL of NHibernate, but whenever I execute the query returns empty.

I am following the example here but I still can’t 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

  • I believe, because this SQL will not return the data of this query, because, the class would need a Join by aggregation fields like Cliente and PlanoDeConta. Take a test don’t come back a guy Conta returns the pure list to see if it returns any table value, type _session.CreateQuery(SQL).List() have this option if you have check if it returns values?

  • @Virgilionovic I believe that C# does not allow returning a pure list as vc flw, it is necessary to inform the type of the list for example: List<String> or List<Object> something like that. I’m just trying to return List and asks for an argument.

  • 1

    let me take a look here has a ready example on my pc and I’ll tell you, ok?

  • it returns a method without parsing also ie, return type of List type-less session.CreateQuery("").List()

  • @Virgilionovic yes, but in the signature of the method I can not put this return public List findAllContasReceber().

  • because the return is System.Collections.IList SqlQuery(string sql); the correct signature! , first I want to see if it returns something, public System.Collections.IList findAllContasReceber()

  • yes, but the IList tbm needs to be typed, like IList<String> or IList<Object or as in my case IList<Conta>.

  • Let’s go continue this discussion in chat.

  • Ilist this is the correct type, even this is just a test to see if it returns something.!

  • 1

    @Virgilionovic problem solved, posted the solution. Hug.

Show 5 more comments

1 answer

1


Solved. After much researching a way to do this and the reasons of 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 created a builder 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;
        }

    }

Browser other questions tagged c# nhibernate hql

You are not signed in. Login or sign up in order to post.