NHibernate + Criterias + DetachedCriteria
I got this a problem, I need translate t开发者_开发百科his 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)
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
)
精彩评论