Complex SQL Query to NHibernate DetachedCriteria or HQL

Question

Hi,

I have the following SQL Query returning the results I need:

SELECT     
Person.FirstName,Person.LastName,OrganisationUnit.Name AS UnitName, RS_SkillsArea.Name AS SkillsArea, Activity.Name AS ActivityName, Activity.CLASS, Activity.StartsOn, Activity.EndsOn, 

SUM(ActivityCost.CostAmount) / 

NULLIF(
 (
  SELECT COUNT(Registration.ActivityId) FROM         
  Registration INNER JOIN AttemptResultsSummary ON Registration.CurrentResultId = AttemptResultsSummary.AttemptResultsSummaryId AND 
  Registration.RegistrationId = AttemptResultsSummary.RegistrationId
  WHERE     (Registration.Status = 1) AND (Registration.ActivityId = Activity.ActivityId) 
  AND (AttemptResultsSummary.AttendanceStatus <> 1)
 ) 
 ,0) 
 AS IndividualCost

 FROM         Registration AS Registration_1 INNER JOIN
                      Activity ON Registration_1.ActivityId = Activity.ActivityId INNER JOIN
                      Person ON Registration_1.PersonId = Person.PersonId INNER JOIN
                      OrganisationUnit ON Person.OrganisationUnitId = OrganisationUnit.OrganisationUnitId INNER JOIN
                      AttemptResultsSummary ON Registration_1.CurrentResultId = AttemptResultsSummary.AttemptResultsSummaryId AND 
                      Registration_1.RegistrationId = AttemptResultsSummary.RegistrationId AND Activity.ActivityId = AttemptResultsSummary.ActivityId AND 
                      Person.PersonId = AttemptResultsSummary.PersonId INNER JOIN 
                      ActivityCost ON Activity.ActivityId = ActivityCost.ActivityId LEFT OUTER JOIN
                          (SELECT     Category.Name, Category.CategoryId
                            FROM          Category INNER JOIN
                                                   CategoryGroup ON Category.[Group] = CategoryGroup.CategoryGroupId
                            WHERE      (CategoryGroup.Name = N'Skills Area')) AS RS_SkillsArea INNER JOIN
                      ActivityInCategory ON RS_SkillsArea.CategoryId = ActivityInCategory.CategoryId ON Activity.ActivityId = ActivityInCategory.ActivityId

AND AttemptResultsSummary.AttendanceStatus <> 1



GROUP BY RS_SkillsArea.Name, Person.FirstName,Person.LastName,Activity.Name, Activity.CLASS, Activity.StartsOn, Activity.EndsOn, Activity.ActivityId, OrganisationUnit.Name, 
                      AttemptResultsSummary.CompletionStatus, AttemptResultsSummary.AttendanceStatus

HAVING AttemptResultsSummary.AttendanceStatus <> 1

Essentially is there any way using either DetachedCriteria or HQL to do the same against the entities rather than direct SQL?

The two challenges are:

1. The query for cost calculation per row.

2. The derived table join (which needs to be an outer join as this value may not exist)

I'd appreciate any pointers. I'd rather not use SQL because of infrastructure changes and the issues with (lack of) refactoring support

Answer 1

Take a look at the official HQL examples @ http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html#queryhql-examples .

In my opinion, the 'derived joins' would be even easier to pull off using HQL.

In the case of performance, my first start would be to catch how much it costs using native SQL using your prefered profiler, and then how much it costs on NHibernate using NHProf.