Does NHibernate Criteria API support projections on collection properties?

Question

I need to replicate the following working HQL query using criteria API.

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.

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:

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:

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?

Answer 1

It looks like you only want the children back so you need to change your criteria to get the type of the child objects then use the parent id as the selection.

session.CreateCriteria(typeof (Child))
.Add(Restrictions.Eq("Id", 33))
.CreateCriteria("Parent")
.Add(Restrictions.Eq("Id", 9))
.SetProjection(Projections.Property("Children"))
.SetMaxResults(3)
.List();

Answer 2

Look into having 'Query-only' properties in here http://ayende.com/Blog/archive/2009/06/10/nhibernate-ndash-query-only-properties.aspx

This would allow you to make the link look bidirectional for your queries.