NHibernate Group按和求和

问题描述

我正在开始研究NHibernate，但有一个我无法解决的问题，我想知道是否有人可以帮助我.

I am starting a study on NHibernate, and I have a problem I'm not able to solve, I wonder if someone could help me.

映射在正确"地工作，但是当我尝试进行分组和求和时，应用程序返回以下错误:

The mapping is working "correctly" but when I try to do the grouping and the sum, the application returns the following error:

var criteria = session.CreateCriteria(typeof(RequestDetail))
.SetProjection(
    Projections.ProjectionList()
    .Add(Projections.RowCount(), "RowCount")
    .Add(Projections.Sum("Course.Price"), "Price")
    .Add(Projections.GroupProperty("Request"), "RequestId")
)
.AddOrder(Order.Asc("RequestId"))
.SetResultTransformer(Transformers.AliasToEntityMap)
.List();

注意1:当我输入代码.Add(Projections.Sum ("Course.Price"), "Price")时，应用程序将结果正确返回给我.

Note 1: When I take code .Add(Projections.Sum ("Course.Price"), "Price") the application returns me the result up correctly.

注意2:我唯一可以做的方法是运行以下代码:

Note 2: The only way I could do was run the code below:

query.Length = 0;
query.AppendLine("select");
query.AppendLine("  s.Id,");
query.AppendLine("  s.Identification,");
query.AppendLine("  sum(c.Price) as Total");
query.AppendLine("from");
query.AppendLine("  Student s");
query.AppendLine("inner join");
query.AppendLine("  Request r on r.StudentId = s.Id");
query.AppendLine("inner join ");
query.AppendLine("  Requestdetail rq on rq.RequestId = r.Id");
query.AppendLine("inner join");
query.AppendLine("  Course c on c.Id = rq.CourseId");
query.AppendLine("Group by");
query.AppendLine("   s.Id, s.Identification");
query.AppendLine("Order by");
query.AppendLine("s.Identification");
IQuery criteria = session.CreateSQLQuery(query.ToString())
    .SetResultTransformer(Transformers.AliasToBean<Teste>());

IList<Teste> teste = criteria.List<Teste>();

有人遇到过这个问题吗?

Has anyone experienced this problem?

推荐答案

我将为结果映射引入一些DTO

I would introduce some DTO for a result mapping

public class MyDTO
{
    public virtual int RowCount { get; set; }
    public virtual decimal Price { get; set; } // type depends on SUM result
    public virtual int RequestId { get; set; }
}

然后我们只需要添加JOIN(以避免出现异常消息)

And then we just have to add the JOIN (to avoid the exception message)

var criteria = session.CreateCriteria(typeof(RequestDetail))
    // the Course.Price comes from some collection
    // we have to JOIN it
    .CreateAlias("Course", "Course")// the first is property name, the second is alias
    .SetProjection(
        Projections.ProjectionList()
        .Add(Projections.RowCount(), "RowCount")
        .Add(Projections.Sum("Course.Price"), "Price")
        .Add(Projections.GroupProperty("RequestId"), "RequestId")
    )
    .AddOrder(Order.Asc("RequestId"))
    .SetResultTransformer(Transformers.AliasToBean<MyDTO>())
    ;
var list = criteria.List<MyDTO>();

猜测为JOIN，它可以是不同的实体/属性名称，但本质应该清楚.我们需要这样做JOIN.借助DTO，我们可以轻松地将结果转换为已知类型的列表

The JOIN is guessed, it could be different entity/property name, but the essence should be clear. We need to do that JOIN. With a DTO we then easily convert result to a list of known types

这篇关于NHibernate Group按和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！