tisdag 4 augusti 2009

Sorting in nHibernate using an aggregate function

I was recently porting the data layer in an application from LINQ to SQL to nHibernate.
While doing this I found some parts that turned out to be somewhat more tricky in nHib then in plain LINQ.

First, lets imaging we have the following bi-directional objects:

class Item
{
public int ID {get;set;}
public int Type {get;set;}
public List Reviews {get;set;}
}

class ItemReview
{
public int ID {get;set;}
public Item Item {get;set;}
public int Grade {get;set;}
}

class ItemProjection
{
public Item Item {get;set;}
public int GradeCount {get;set;}
}

Consider the following LINQ query:
var items =
db.Items
.Where(i=>i.Type==5)
.Select(i=> {
return new ItemProjection()
{
Item = i,
Grade = i.ItemReviews.Sum(x=>x.Grade)
};
})
.OrderByDescending(x=>x.Grade).Take(5).ToList();

Basically what we are doing is that we select the 5 items which has the highest summary of review grades and project those into a new object. LINQ to SQL does a fantastic job to translate this into one single SQL query that works perfectly.

Now over to nHibernate. First I thought that "LINQ to nHibernate" would be smart enough to translate the query into a valid SQL statement. Well, as you can imagine, it did not work.

So, I turned to Google and the nHibernate documentation and started to look a "Projection" and "aliases". By using "Projections" you can create aggregate functions in the query that can be used as an order statement.

This is what I came up with:
var criteria = session.CreateCriteria("itemReview")
.CreateAlias("itemReview.Item", "item")
.SetProjection(
Projections.ProjectionList()
.Add(Projections.Sum("Grade"), "gradeSum")
.Add(Projections.GroupProperty("Item"))
)
.AddOrder(Order.Desc("gradeSum"))
.Add(NHibernate.Criterion.Expression.Eq("item.TypeID", typeID));
criteria.SetMaxResults(top);
var list = criteria.List();
return list.Select(o =>return new ItemProjection() {Item=(Item)o[1], Grade=(int)o[0]).ToList();


We start to create a "Criteria" for the "ItemReview" and then we add a few aliases followed by a "ProjectionList" that includes one "Sum" and on "GroupProperty" projection. The Summary is then used as an Order criteria. At last we "List<>" the criteria using the "object[]" type and projects the list into the "ItemProjection" type (I know, I know, the uggly cast between the object array and the properties isn't very nice...).