Grouping and Transforming Using NHibernate
Okay, I’ve got to be doing this wrong.
Call it premature optimization, but I forsee an old LINQ method being a performance bottleneck when we hit a few hundred thousands records—especially for the ASP.NET charting control to render in any useful time period.
So, what do I do? I figure pushing that computation back down to the database would be a good first step.
Unfortunately, grouping, sorting, and such are a serious pain in the ass. Unless, as I said, I’m doing it wrong.
Original Code – Grouping and Counting ala LINQ
private IList GetIncidentsGroupedByIncidentCode()
{
using (var repository = new IncidentRepository())
{
var allIncidents =
repository.GetAllBySchoolId(SessionManager.CurrentSchoolId);
var incidentsByCode = from i in allIncidents
group i by i.IncidentCodeId
into grouping
orderby grouping.Count()
select new
{
IncidentCodeId = grouping.Key,
Count = grouping.Count(),
Description =
GetIncidentCodeDescription(grouping.Key)
};
return incidentsByCode.ToList();
}
}
Grab all incidents (using NHibernate repository) and use LINQ to transform them into a new handy anonymous type that consisted of the IncidentCodeId, a Count (by IncidentCodeId), and the Description which uses the IncidentCodeId to grab the description (the incident code description is coming from an entirely different system/database, hence the method to go fetch it).
I can simply return an IList rather than specifying the type (since it’s anonymous) and get away with loading up my Chart Control—not a problem.
Newish Code – Grouping and Counting ala NHibernate
public IList GetCountByIncidentCode(int schoolId)
{
using (var tx = Session.BeginTransaction())
{
var criteria = Session.CreateCriteria(typeof (Incident));
// Only get those matching the requested SchoolId
criteria.Add(RestrictionsHelper<Incident>.Eq(x => x.SchoolId, schoolId));
// Setup our projections.
// IncidentCodeId is what we’re using as an Identifier.
// Id is what we’re counting, so the results of the “GroupedResult” go into Result
// and we’re grouping by IncidentCodeId
criteria.SetProjection(Projections.ProjectionList()
.Add(Projections.Property(“IncidentCodeId”), “Identifier”)
.Add(Projections.Count(“Id”), “Result”)
.Add(Projections.GroupProperty(“IncidentCodeId”)));
// Order THAT mess by Result
criteria.AddOrder(Order.Asc(“Result”));
// Now, since we can’t use anonymous objects (??), we have to use a funky Java
// method to transform it into a typed result.
criteria.SetResultTransformer(Transformers.AliasToBean(typeof (GroupedResult)));
// Convert this all to a list.
var result = criteria.List<GroupedResult>() as List<GroupedResult>;
// Commit… or get committed.
tx.Commit();
if (result != null)
{
// We can’t do this inline (??), so go back into the list and iterate through… grabbing
// descriptions.
result.ForEach(x =>
{
var description =
GetIncidentCodeDescription(x.Identifier.ConvertTo<int>());
x.Description = description;
});
}
// Holy crap, we’re done!
return result;
}
}
What… the… heck?
Amazingly enough, that works (changing the chart’s column names, of course). And it’s relatively quick… But woah, what a mess.
It also adds annoying little ‘result’ objects into the mix.
public class GroupedResult
{
public int Identifier { get; set; }
public string Description { get; set; }
public int Result { get; set; }
}
Strongly typed is stellar and I’m pretty sure I could have some generic objects. [Identifier/Description/Result] could work for counts, averages, most anything that is grouped up, but that just twitches me out a bit to have random classes sitting around for data transformations.
So, good readers—how is this REALLY supposed to work? All that to generate the guts of:
SELECT COUNT(IncidentCodeId) as Result, IncidentCodeId
FROM Incidents
WHERE SchoolId = :schoolId
GROUP BY IncidentCodeId
ORDER BY Result
