How to use the SUM aggregation function in a NHIBERNATE query?

Question

How to use the SUM aggregation function in a NHIBERNATE query?

Navigation

#1 by (4 votes)
#2 by (1 votes)

2

My query consists of knowing NumCarroId , TotalGasto and VlrUnit , in Sql I could do this but when I play in HQL it returns the following error:

Applying GROUP BY to SUM to make a ToList, to show me a list already added, but there are a lot of errors

If someone can help, for now I did not find any answer that worked.

Error returning:

string hql="select a.NumCar.Id, sum (aTotalGasto) costs from Supply to WHERE a.DtAbtained Between: initialData AND: dataFinal GROUP BY a.NumCar.Id";

Code

public IList<Abastecimento> Resumo(DateTime dataInicio, DateTime dataFinal)
{
    string hql = "SELECT NumCarroId, DtAbastecido FROM Abastecimento a WHERE a.DtAbastecido Between :dataInicial AND :dataFinal GROUP BY a.NumCarroId";
    IQuery query = session.CreateQuery(hql)
    .SetParameter("dataInicial", dataInicio)
    .SetParameter("dataFinal", dataFinal);
    return query.List<Abastecimento>();
}

Supply table

CREATE TABLE [dbo].[Abastecimento] (
    [Id]            INT             IDENTITY (1, 1) NOT NULL,
    [DtAbastecido]  DATETIME        NULL,
    [Litro]         INT             NULL,
    [VlrUnit]       DECIMAL (18, 2) NULL,
    [TotalGasto]    DECIMAL (18, 2) NULL,
    [AutorId]       INT             NULL,
    [NumCarroId]    INT             NULL,
    [Km]            INT             NULL,
    [NomeProdutoId] INT             NULL,
    [Km_Andado]     INT             NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK84286500787E6DCB] FOREIGN KEY ([AutorId]) REFERENCES [dbo].[Usuario] ([Id]),
    CONSTRAINT [FK842865002F58EAD8] FOREIGN KEY ([NumCarroId]) REFERENCES [dbo].[Veiculo] ([Id]),
    CONSTRAINT [FK8428650023D2F9D9] FOREIGN KEY ([NomeProdutoId]) REFERENCES [dbo].[Compra] ([Id])
);

Model Supply

 public class Abastecimento {
 public virtual int Id {
  get;
  set;
 }
 [Required]
 public virtual int Litro {
  get;
  set;
 }
 public virtual DateTime ? DtAbastecido {
  get;
  set;
 }
 public virtual decimal VlrUnit {
  get;
  set;
 }
 public virtual int Km {
  get;
  set;
 }
 public virtual decimal TotalGasto {
  get;
  set;
 }
 public virtual int Km_Andado {
  get;
  set;
 }
 public virtual Usuario Autor {
  get;
  set;
 }
 public virtual Compra NomeProduto {
  get;
  set;
 }
 public virtual Veiculo NumCarro {
  get;
  set;
 }
}
}

sql c# nhibernate

asked by anonymous 17.01.2018 / 17:25

2 answers

4

For your error, this question: How to resolve No data type for node error in hibernate , says:

A HQL Query should contain the properties of the object and not the fields in the structure of your table.

In SQL:

select numCarroId, sum(valorGasto) gastos from Abastecimento
where dataAbastecimento between '2017-05-21' and '2017-11-13'
group by numCarroId

SQLFiddle

You gave me a chat via Model Supply like this:

public class Abastecimento {
 public virtual int Id {
  get;
  set;
 }
 [Required]
 public virtual int Litro {
  get;
  set;
 }
 public virtual DateTime ? DtAbastecido {
  get;
  set;
 }
 public virtual decimal VlrUnit {
  get;
  set;
 }
 public virtual int Km {
  get;
  set;
 }
 public virtual decimal TotalGasto {
  get;
  set;
 }
 public virtual int Km_Andado {
  get;
  set;
 }
 public virtual Usuario Autor {
  get;
  set;
 }
 public virtual Compra NomeProduto {
  get;
  set;
 }
 public virtual Veiculo NumCarro {
  get;
  set;
 }
}
}

Modify your HQL to:

    public IList < Abastecimento > Resumo(DateTime dataInicio, DateTime dataFinal) {
  string hql = "select a.NumCarro.Id, sum(a.TotalGasto) gastos from Abastecimento a WHERE a.DtAbastecido Between :dataInicial AND :dataFinal GROUP BY a.NumCarro.Id";
  IQuery query = session.CreateQuery(hql)
   .SetParameter("dataInicial", dataInicio)
   .SetParameter("dataFinal", dataFinal);
  return query.List < Abastecimento > ();
 }

17.01.2018 / 18:04

P closing before the / P tag How do I get this effect in a progress bar?

score 1 · Accepted Answer

I put this following line of code in the controller, and I was able to add Group By, but I still can not do SUM

var teste = consulta.Where(i => i.DtAbastecido >= dataInicio && i.DtAbastecido <= dataFinal)
                    .GroupBy(x => new { x.NumCarro.NCarro})
                    .Select(x => x.First())
                    .OrderBy(x => x.NumCarro.NCarro);