NHibernate + join to derived table

Question

In a table that stores multiple rows per employee, I want to pull one row per employee that represents the most recent entry for each employee. Here's where I am with hand-written SQL:

SELECT [all the selected columns here]
FROM   Nominations t
 inner join 
 (select max(NominationId) mostRecentNominationId, 
        EmployeeId from Nominations group by EmployeeId) n
  on n.mostRecentNominationId = t_.NominationId

From source data like this:

nomination_id       employee_id
-------------------------------
1                   5
2                   5
4                   10
7                   10

That'll give me something like this:

nomination_id       employee_id
-------------------------------
2                   5
7                   10

I haven't been able to figure out how to accomplish that type of query via NHibernate ICriteria. Any thoughts?

Answer 1

Here is what you need to do:

DetachedCriteria dCriteria = DetachedCriteria.For<Nomination>("nomination")
     .SetProjection(Projections.Max("nomination.Id"))
     .Add(Restrictions.EqProperty("nomination.EmployeeId", "employee.Id"));

var nominations = Session.CreateCriteria<Nomination>("nom")
      .CreateCriteria("Employee", "employee")
      .Add(Subqueries.PropertyEq("nom.Id", dCriteria)).List<Nomination>();

This is not equilevant to the SQL query providfed in the question but it does exactly the same thing.

The SQL query that is generated by the above criteria query is:

SELECT * 
FROM Nomination nom 
inner join Employee employee on nom.EmployeeId=employee.EmployeeId 
WHERE nom.NominationId = 
(SELECT max(nomination.NominationId) as maxID 
   FROM Nomination nomination  
   WHERE nomination.EmployeeId = employee.EmployeeId)