Partially Populate Child Collection with NHibernate

Question

hi all, been struggling with this for a while, and cant seam to figure it out...

i've got a BlogPost class, which has a collection of Comments, and each of the comments has a DatePosted field.

what i need to do is query for a BlogPost and return it with a partially loaded Comments collection, say all comments posted on the 1 Aug 2009.

i've got this query:

        BlogPost post = session.CreateCriteria<BlogPost>()
            .Add(Restrictions.Eq("Id", 1))
            .CreateAlias("Comments", "c")
            .Add(Restrictions.Eq("c.DatePosted", new DateTime(2009, 8, 1)))
            .UniqueResult<BlogPost>();

when i run this query and check out the sql generated, it first runs a query against the BlogPost table, joining to the Comment table with the correct date restriction in, then runs a second query just on the Comment table that returns everything.

result is the Comments collection of the BlogPost class totally filled up!

what am i doing wrong?

i've got code samples if anyone needs more info...!

Answer 1

You're not really doing anything wrong - hibernate just doesn't work that way.

If you navigate from the BlogPost to the Comments, Hibernate will populate the comments based on the association mapping that you've specified, not the query you used to retrieve the BlogPost. Presumably your mapping is just doing a join on a key column. You can use a filter to get the effect you're looking for. But I think that will still fetch all the comments and then do a post-filter.

More simply, just query for what you want:

List<Comments> comments = session.CreateCriteria<BlogPost>()
            .Add(Restrictions.Eq("Id", 1))
            .CreateAlias("Comments", "c")
            .Add(Restrictions.Eq("c.DatePosted", new DateTime(2009, 8, 1)))
            .list();

This will in fact return only the comments from the specified date. if it makes you feel better, you can then set them like this:

post.setComments(comments); //having already retreived the post elsewhere

I was also surprised by this behaviour when I first encountered it. It seems like a bug, but I've been told its by design.

Answer 2

thanks for the response, i guess i kinda understand why its by design, but i would have thought that there would be a built in method to enable this, your solution works, but feels like a bit of a hack!

my problem is that the child collection is HUGE if not filtered (the example i gave of posts and comments was to protect the names of the innocent!) and there is now way i can be pulling all the data back every time.

i've run Sql Profiler on this and its still pulling all the data back. when i run the following code the first query does what you expect, just the one post comes back, but as soon as the second query is executed, two queries go to the database, the first to retrieve the filtered comments (bingo!), and then a second to populate the post.Comments property with all the comments, just what i'm trying to avoid!

        var post = session.CreateCriteria<BlogPost>()
            .Add(Restrictions.Eq("Id", 1))
            .UniqueResult<BlogPost>();

        var comments = session.CreateCriteria<Comment>()
            .Add(Restrictions.Eq("BlogPostId", 1))
            .Add(Restrictions.Eq("DatePosted", new DateTime(2009, 8, 1)))
            .List<Comment>();

        post.Comments = comments;

this is very strange, its not like i'm enumerating over the post.Comments list, so why is it populating it?! here are my classes and maps:

public class BlogPostMap : ClassMap<BlogPost>
{
    public BlogPostMap()
    {
        Id(b => b.Id);
        Map(b => b.Title);
        Map(b => b.Body);
        HasMany(b => b.Comments).KeyColumnNames.Add("BlogPostId");
    }
}
public class CommentMap : ClassMap<Comment>
{
    public CommentMap()
    {
        Id(c => c.Id);
        Map(c => c.BlogPostId);
        Map(c => c.Text);
        Map(c => c.DatePosted);
    }
}

public class BlogPost
{
    public virtual int Id { get; set; }
    public virtual string Title { get; set; }
    public virtual string Body { get; set; }
    public virtual IList<Comment> Comments { get; set; }
}
public class Comment
{
    public virtual int Id { get; set; }
    public virtual int BlogPostId { get; set; }
    public virtual string Text { get; set; }
    public virtual DateTime DatePosted { get; set; }
}

any ideas?

Answer 3

I agree it feels like a hack to manually populate the collection.

You can use a custom loader instead. Something like this:

<query name="loadComments">
<return alias="comments" class="Comment"/>
<load-collection alias="comments" role="Post.comments"/>
from Comments c where c.Id = ? and c.DatePosted = SYSDATE
</query>

Also, you can use sql-query if you want more control. I've occasionally stooped to writing custom loaders when I couldn't get hibernate to generate the query I wanted. Anyway, don't know why I didn't think of that in the first place.

Answer 4

There is a result transformer for this, see the documentation.

Quote:

Note that the kittens collections held by the Cat instances returned by the previous two queries are not pre-filtered by the criteria! If you wish to retrieve just the kittens that match the criteria, you must use SetResultTransformer(CriteriaUtil.AliasToEntityMap).

IList cats =
sess.CreateCriteria(typeof(Cat))
    .CreateCriteria("Kittens", "kt")
        .Add( Expression.Eq("Name", "F%") )
    .SetResultTransformer(CriteriaUtil.AliasToEntityMap)
    .List();

You could also use filters that get activated using session.EnableFilter(name).

There is a similar question here.

Answer 5

Make the Comments Collection lazy, so that hibernate doesn't fetch it when you're getting the BlogPost. Then use a filter on Comments collection.

comments = session.CreateFilter(blogPost.Comments, ... ).List();