Home > .net 3.5, c#, NHibernate, SQL > Grouping and Transforming Using NHibernate

Grouping and Transforming Using NHibernate

June 11, 2009

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 =



        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 =



        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


                                   .Add(Projections.Property(“IncidentCodeId”), “Identifier”)

                                   .Add(Projections.Count(“Id”), “Result”)


        // Order THAT mess by 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.


        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 =


                                    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

Categories: .net 3.5, c#, NHibernate, SQL
%d bloggers like this: