How to efficiently filter a collection of child objects by a property with NHibernate

Question

I've been trying unsuccessfully to filter a collection of child objects for a few hours how and have finally thrown my hands up! I'm new to NHibernate and was hoping for a couple of pointers on this. I've tried various ICriteria etc. with no luck. I'm just not getting it.

I have a parent object 'Post' with a collection of child objects 'Comment'. The collection is mapped as a set with inverse on the Comment side.

What I am trying to do is to return only comments with a status enum value of 'Comment.Approved'

The relevant portions of the entity classes are as follows:

public class Post
{
    public virtual Guid Id { get; protected set; }
    private ICollection<Comment> _comments;
    public virtual ICollection<Comment> Comments
    {
        get { return _comments; }
        protected set { _comments = value; }
    }
}

public class Comment
{
    public virtual Guid Id { get; protected set; }
    public virtual Post Post { get; set; }
    public virtual CommentStatus Status { get; set; }

 }

My retrieval code looks like this at the moment:

var Id = __SomeGuidHere__;
var post = _session
            .CreateCriteria<Post>()
            .Add(Restrictions.Eq("Id", Id))
            .UniqueResult<Post>();

var comments = _session.CreateFilter(post.Comments, "where Status = :status").SetParameter("status", CommentStatus.Approved).List<Comment>();

While this works the SQL doesn't appear to be very efficient, I expected to be able to translate the following SQL into something similar in HQL or an ICriteria of some sort:

SELECT * FROM posts p LEFT JOIN comments c ON p.PostId = c.PostId AND c.Status = 0 WHERE p.PostId = '66a2bf13-1330-4414-ac8a-9d9b00ea0705';

I've had a look at the various answers related to this type of query here and none of them seem to address this specific scenario.

There's probably something very simple I'm missing here but I'm too tired now to see it. Here's hoping someone better with NHibernate can point me in the right direction.

Thanks for your time.

Edit: Still struggling with this, some of the answers here are good in that I'm starting to think that my post entity needs to be re-thought to perform the filtration itself, or that I should implement a ViewModel to filter the comments I want. The question still remains however, even if only from an academic perspective.

I've updated the selection to HQL and tried:

var post = _session
            .CreateQuery("select p from Post as p left join fetch p.Comments as c where p.Id = :id and c.Id in (select ac from p.Comments ac where ac.Status = :status)")
            .SetParameter("id", Id)
            .SetParameter("status", CommentStatus.Approved)
            .UniqueResult<Post>();

This works as long as a post has an approved comment, otherwise I get no post due to the SQL generated using 'AND' in the where clause.

Anyone? I'm stumped now!

Update: Thanks to all who have replied, it has been useful and has forced me to re-evaluate portions of my model. Since the most frequent use of comments as children of a post is in the viewing of a post, only the approved comments should be viewable in this scenario. In most other scenarios that I can think of comments would be accessed directly and filtered by status which is of course straight forward.

I have updated my mappings to filter all post > comment loading to only load approved posts as follows (in FluentNHibernate):

 HasMany(x => x.Comments).Where(x => x.Status == CommentStatus.Approved)
            .AsSet()
            .Inverse()
            .KeyColumn("PostId")
            .ForeignKeyConstraintName("PostComments")
            .OrderBy("CreatedOn")
            .Cascade.AllDeleteOrphan();

I wish I could mark all as the answer since all contributed to me working this thing out, but Peter was the first to point out that I may be thinking about the model incorrectly.

Thanks to all.

Answer 1

According to me the SQL is perfect for what you describe.

The database has its own optimizer and will know what to do to get your data efficiently delivered to your doorstep.

Answer 2

I think this would work fine:

var comments =
_session.CreateCriteria<Comment>
.CreateAlias("Post", "post")
.Add (Restriction.Eq("Status", status))
.Add (Restriction.Eq("post.Id", Id)).List();

I am not shure if you need the .CreateAlias part (if you don't need it, maybe Post.Id would work too - but I am not sure).

Answer 3

I would solve this using an extension method for IEnumerable<Comment>:

public static IEnumerable<Comment> FilterByStatus(this IEnumerable<Comment> comments, CommentStatus status)
{
    return comments.Where(x => x.Status == status);
}

Let NHibernate return the entire collection then filter it as needed.