NHibernate entity projection

Question

I'm using NHibernate with QueryOver API to query my domain entities. The problem is getting duplicate results. For example when querying following domain:

I use code like:

  var list = Session.QueryOver<Post>()
                    .JoinQueryOver<Comment>(x => x.Comments)
                    .Where(c => c.Name == "Name")
                    .Take(5)
                    .List();

Generated SQL will be like following:

SELECT Top(5) * FROM Posts p left outer join Comments c on p.Id = c.PostId

The problem here is that after left join is done resulting record set has more then 5 rows. And then TOP function applies and cuts results. So for example if first post has 5 comments, I will get this post 5 times and won't get others.

I know why it happens and found a nice post about it. But Is there a projection that will tell nhibernate to select only columns from the Post entity and not add columns from Comment entity in select? Maybe some other method (not JoinQueryOver) should be used?

Answer 1

First, you'll need a reference back from Comment to Post. I'll assume it's called "Post".

Then use a subquery to filter out the comments, and subsequent posts.

//the alias for post
Post post = null; 

var list = Session.QueryOver(() => post)
            .WithSubquery.WhereProperty(() => post.Id)
                .In(NHibernate.Criterion.QueryOver.Of<Comment>()
                    .Where(c => c.Name == "Name")
                    .Select(c => c.Post.Id))
            .Take(5)
            .List();

The generated SQL should be something in the likes of:

SELECT Top(5) * FROM Posts p where p.Id in (select PostID from Comments c where c.Name = 'Name')