Distinct over one column with value comparing on another column ICriteria NHibernate

Question

I have table and object called Person. I have problem to create a distinct (over column "lastname") criteria. I want to get only the oldest Person with distinct lastnames. For example i have (properties: firstname, lastname, age):

• John Smith, 52
• Jessica Smith, 45
• Ann Pit, 21
• Brad Pit, 30

Can anybody help me to create criteria which result i get Person object with John Smith and Brad Pit?

Answer 1

Probably the best approach here is to use EXISTS to filter the result set, first a SQL example to get the logic correct:

DECLARE @Person TABLE (
   Id INT,
   Firstname VARCHAR(20),
   Lastname VARCHAR(20),
   Age INT 
)

INSERT INTO @Person VALUES (1, 'Brad', 'Pitt', 42)
INSERT INTO @Person VALUES (2, 'Angelina', 'Pitt', 45)
INSERT INTO @Person VALUES (3, 'John', 'Smith', 50)
INSERT INTO @Person VALUES (4, 'Jane', 'Smith', 55)

SELECT P.* FROM @Person P
WHERE EXISTS(
    SELECT SUB.LastName, MAX(SUB.Age) as Age FROM @Person SUB
    GROUP BY SUB.LastName
    HAVING SUB.LastName = P.LastName AND MAX(SUB.Age) = P.Age)

This yields the following results which is as expected:

Id  Firstname   Lastname    Age
-------------------------------
2   Angelina    Pitt        45
4   Jane        Smith       55

Now to convert to nHibernate, this effectively builds the same query as above:

var subQuery = DetachedCriteria.For<Person>("SUB")
    .SetProjection(Projections.ProjectionList()
    .Add(Projections.GroupProperty("LastName"), "Lastname")
    .Add(Projections.Max("Age"), "Age"))
    .Add(Restrictions.EqProperty(Projections.Max("Age"), "P.Age")
    .Add(Restrictions.EqProperty("LastName", "P.LastName"));

return session.CreateCriteria<Person>("P")
    .Add(Subqueries.Exists(subQuery))
    .List<Person>();