I think I may have found a bug in NH 2.1.0.
I need to replicate the following working HQL query using criteria API.
Code:
session.CreateQuery(
"select c " +
"from Parent p " +
"inner join p.Children c " +
"where p.Id = 9 " +
"and c.Id = 33")
.SetMaxResults(3)
.List();
The query selects all the children that satisfy a certain criteria that belong to parents that satisfy another criteria.
In my example both criterion are simple Id equalities but they could be anything.
For some reason the equivalent criteria API query returns a list with the right number of items but those items are all null.
Code:
session.CreateCriteria(typeof (Parent))
.Add(Restrictions.Eq("Id", 9))
.CreateCriteria("Children")
.Add(Restrictions.Eq("Id", 33))
.SetProjection(Projections.Property("Children"))
.SetMaxResults(3)
.List();
Why don't these two queries return the same results?
Here is the generated SQL from the HQL query:
Code:
SELECT TOP 3 childid7_,
name7_
FROM (SELECT children1_.childid AS childid7_,
children1_.name AS name7_,
Row_number()
OVER(ORDER BY current_timestamp) AS __hibernate_sort_row
FROM dbo.parent parent0_
LEFT OUTER JOIN dbo.child children1_
ON parent0_.parentid = children1_.parentid
WHERE (parent0_.parentid = 9)
AND (children1_.childid = 33)) AS QUERY
WHERE QUERY.__hibernate_sort_row > 0
ORDER BY QUERY.__hibernate_sort_row
And here is the SQL from the Criteria API query:
Code:
SELECT TOP 3 y0_
FROM (SELECT this_.parentid AS y0_,
Row_number()
OVER(ORDER BY current_timestamp) AS __hibernate_sort_row
FROM dbo.parent this_
INNER JOIN dbo.child child1_
ON this_.parentid = child1_.parentid
WHERE this_.parentid = @p0
AND child1_.childid = @p1) AS QUERY
WHERE QUERY.__hibernate_sort_row > 0
ORDER BY QUERY.__hibernate_sort_row
Note that the join between parent and child is unidirectional. The child entity does not have a reference property pointing to its parent.
Can anybody suggest an alternative that would allow me to work around this limitation?
Cheers,
Nathan
Login
FAQ
Search
