title: NHibernate correlated subquery using ICriteria
body: <p>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.</p>
  
  <p>Two fairly basic tables (cut down for the purpose of this example)</p>
  
  <pre><code>CREATE TABLE Person
  (
      PersonNo  INT,
      BirthDate DATETIME
  )
  
  CREATE TABLE PersonDetails
  (
      PersonNo  INT,
      FirstName VARCHAR(30),
      Surname   VARCHAR(30)
  )
  </code></pre>
  
  <p>And the query... </p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>Here's my attempt.</p>
  
  <pre><code>var pdSub = DetachedCriteria.For&lt;PersonRevision&gt;("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&lt;Person&gt;("p")                                                
          .Add(Restrictions.Eq("p.PersonNo", 1))
          .Add(Subqueries.Eq("p.PersonNo", pdSub))
          .List();  
  </code></pre>
  
  <p>The subquery pdSub already defines the relationship (by PersonNo) but the Subqueries class requires another relationship to be defined (e.g. Eq)?      </p>
  
  <p>Any help would be appreciated.</p>
  
  <p>Thanks,
  John        </p>