NHibernate GroupBy and Sum

问题:

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:

"could not resolve property: Course.Price of: Persistence.POCO.RequestDetail"

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();

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

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?

回答1:

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; }
}

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>();

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