NHibernate + Criterias + DetachedCriteria

Question

I got this a problem, I need translate this SQL Query to Criteria, but I´m getting some troubles, any one can give a hand?

SELECT * FROM Ref INNER JOIN Product Prod on Ref.id = Prod.id_referencia AND Prod.ProdDtAlteracao = (SELECT MAX(Prod2.ProdDtAlteracao) FROM Product Prod2 WHERE Prod.ProdCod = Prod2.ProdCod)

Answer 1

Assuming Ref has a bag/list property called "products" which maps to Product...

session.CreateCriteria<Ref>("r")
    .CreateAlias("products", "p", InnerJoin)
    .Add(Subqueries.PropertyEq("p.ProdDtAlteracao", DetachedCriteria.For<Product>("p2")
     .SetProjection(Projections.Max("p2.ProdDtAlteracao"))
     .Add(Restrictions.EqProperty("p.ProdCod", "p2.ProdCod"))))
    .List<Ref>();

query would look like

SELECT ...
    FROM Ref 
    INNER JOIN Product Prod on Ref.id = Prod.id_referencia 
    WHERE Prod.ProdDtAlteracao = (
         SELECT MAX(Prod2.ProdDtAlteracao) 
         FROM Product Prod2 
         WHERE Prod.ProdCod = Prod2.ProdCod
        )