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 =

            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

About these ads
Categories: .net 3.5, c#, NHibernate, SQL
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: