Efficently returning parent and child records with NHiberate

Question

I am trying to efficiently work with parent & child (post & tags) entities. A sample of the code can be seen here: http://gist.github.com/297464

Using the following results in less than 10 distinct Post entities if any of Post contains more than 1 tag.

var posts = session
    .CreateCriteria<Post>()
    .SetMaxResults(10)
    .SetFetchMode("Tags", FetchMode.Eager)
    .List<Post>();

If I remove the .SetFetchMode line above, I get the 10 records I am looking for, but all of the Tag entities are queried and then filtered in memory.

It seems like I should be able to instruct NHiberate to either pass in a list of PostIds or do a join.

I am pretty new to NHiberate so it is also entirely possible I am going at this completely wrong.

Thanks,
Scott

Answer 1

The problem is that SetMaxResults is not applied to the number of root entities returned from the query, it is translated into a T-SQL TOP (in the case of SqlServer), which is applied to the result from a join query. Since there is one row in the result set for each child of a root entity the TOP will not have the desired effect.

To achieve a limit on the number of root entities you could use a join query with a subquery that contains the SetMaxResults limit.

// Select the ids of the first ten posts
var subquery = DetachedCriteria.For<Post>()
    .SetMaxResults(10)
    .SetProjection(Projections.Property("Id"));

// Do a join query with posts and tags, for the posts who's ids are
// in the result of the subquery
var posts = session.CreateCriteria<Post>()
    .SetFetchMode("Tags", FetchMode.Join)
    .Add(Subqueries.PropertyIn("Id", subquery))
    .SetResultTransformer(Transformers.DistinctRootEntity)
    .List<Post>();