NHibernate correlated subquery using ICriteria

Hi All,

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(Projections.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

ansaurus

tags:

views:

answers:

NHibernate correlated subquery using ICriteria

related questions