Nhibernate Criteria Group By clause and select other data that is not grouped
- by Peter R
Hi,
I have a parent child relationship, let's say class and children. Each child belongs to a class and has a grade. I need to select the children (or the ids of the children) with the lowest grade per class.
session.CreateCriteria(typeof(Classs))
.CreateAlias("Children", "children")
.SetProjection(Projections.ProjectionList()
.Add(Projections.Min("children.Grade"))
.Add(Projections.GroupProperty("Id"))
)
.List<Object[]>();
This query returns me the lowest grade per class, but I don't know which child got the grade. When I add the children's Id to the group, the group is wrong and every child gets returned.
I was hoping we could just select get the id's of those childs without grouping them. If this is not possible, then maybe there is a way to solve this with subqueries?
