NHibernate correlated subquery using ICriteria

I've been doing some work evaluating NHibernate for an upcoming project and am working through some use cases to see how it performs. I haven't yet been able to find a way to express the following query using the Criteri API.

Two fairly basic tables (cut down for the purpose of this example)

CREATE TABLE Person
(
    PersonNo  INT,
    BirthDate DATETIME
)

CREATE TABLE PersonDetails
(
    PersonNo  INT,
    FirstName VARCHAR(30),
    Surname   VARCHAR(30)
)

And the query...

SELECT P.PersonNo, P.FirstName, P.Surname
FROM Persons P
JOIN PersonDetails PD
  ON PD.PersonNo = P.PersonNo
 AND EffDate =
    (
        SELECT MAX(EffDate)
        FROM PersonDetails
        WHERE PersonNo = PD.PersonNo
    )
WHERE P.PersonNo = 1

Basically, I am just trying to flatten the person master record and the latest person revision record into one object. I was able to do this easily enough using HQL but cannot get the correlated subquery to work.

Here's my attempt.

var pdSub = DetachedCriteria.For<PersonRevision>("pdSub")
    .SetProjection(
        Projections.ProjectionList()
            .Add(Pro开发者_运维百科jections.Max("EffDate").As("MaxEffDate"))
            .Add(Projections.Property("Person.PersonNo").As("PersonNo")) 
            .Add(Projections.GroupProperty("Person.PersonNo")))
    .Add(Expression.EqProperty("pdSub.Person.PersonNo", "p.PersonNo"));    

var p =
    session.CreateCriteria<Person>("p")                                                
        .Add(Restrictions.Eq("p.PersonNo", 1))
        .Add(Subqueries.Eq("p.PersonNo", pdSub))
        .List();  

The subquery pdSub already defines the relationship (by PersonNo) but the Subqueries class requires another relationship to be defined (e.g. Eq)?

Any help would be appreciated.

Thanks, John

---

For the purposes of a demo I've added EffDate to both tables. Hopefully that matches your model and demonstrates this kind of join appropriately.

DetachedCriteria subQuery = DetachedCriteria
    .For<PersonDetails>("pd")
    .SetProjection(Projections.Max("pd.EffDate"))
    .Add(Restrictions.EqProperty("pd.PersonId", "p.PersonId"));

IList results = Session
    .CreateCriteria(typeof(Person), "p")
    .SetProjection(Projections.ProjectionList()
        .Add(Projections.Property("p.PersonId").As("PersonId"))
        .Add(Projections.Property("p.EffDate").As("MaxEffDate")))
    .Add(Subqueries.PropertyEq("p.EffDate", subQuery))
    .List();

The SQL NHibernate is throwing at the server looks like this...

SELECT this_.PersonId as y0_, this_.EffDate as y1_ FROM Person this_ WHERE this_.EffDate = (SELECT max(this_0_.EffDate) as y0_ FROM PersonDetails this_0_ WHERE this_0_.PersonId = this_.PersonId)