NHibernate +Criteria+ DetachedCriteria

Question

我遇到了一个问题，我需要将这个SQL查询转换为条件，但我遇到了一些麻烦，有人能帮上忙吗？

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

Answer 1

假设Ref有一个名为"products“的包/列表属性，它映射到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>();

查询将如下所示

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
        )