How can I select the root entity and child entity count using QueryOver in NHibernate?

Question

I have a pretty simple query I'm trying to convert to NHibernate's QueryOver syntax, but I'm having difficulty. The original SQL query is functionally the same as:

SELECT [Post].*, (
    SELECT Count(*)
    FROM [Comment]
    WHERE [Comment].[PostId] = [Post].[Id]) AS [CommentCount]
FROM [Post]

The problem is I'm having difficulty converting this to QueryOver syntax. I tried defining a summary class containing both the Post and the CommandCount as such:

public class PostSummary
{
    public Post Post { get; set; }
    public CommentCount { get; set; }
}

And then defining the query with a couple of selects:

Post lPostAlias = null;
Comment lCommentAlias = null;

var lCommentSubquery = QueryOver.Of(() => lCommentAlias)
    .Where(() => lCommentAlias.Post.Id == lPostAlias.Id)
    .ToRowCountQuery();

PostSummary lPostSummaryAlias = null;

session.QueryOver(() => lPostAlias)
    .SelectList(list => list
        .Select(x => x).WithAlias(() => lSummary.Post)
        .SelectSubQuery(lCommentSubQuery).WithAlias(() => lSummary.CommentCount)
    .List<PostSummary>();

An exception gets thrown with the error message:

could not resolve property:  of: Project.Models.Post

So it looks like it doesn't like the .Select(x => x) part of the query. I was hoping to find something along the lines of 'Projections.RootEntity()` but alas there is no such thing that I can find.

Can someone explain what I'm doing wrong and guide me to the proper way to do this basic query? I imaging I could select all the properties of Post that I want, but worry that I'll lose the ability to take advantage of the proxy sub-classes NHibernate generates for lazy-loading purposes and is not what I want.

Unfortunately I don't know if there's a way to select out the entity and another property — Andrew Whitaker
I think the error message appears because of this statement: .Where(() => lCommentAlias.Post.Id == lPostAlias.Id). You need to join the Post entity first. Another question: Do you need both counts? Maybe it would be easier to understand when you provide your (simplified) business classes. — core
I encountered the problem today trying to answer this question stackoverflow.com/q/18871722/1236044 As far as I know, (x=>x) won't work. Anyway, here is an answer which seems to address the problem with some efficiency stackoverflow.com/a/17663212/1236044 — jbl
@Rico As long as you're referencing the primary key of the entity, you do not need to join. The Comment table in the DB has a PostId column which doesn't require a join to access. I do this all the time without error. — Anthony
@jbl The query in the answer you posted looks like it will have an the N+1 Select issue because it does a single Select for the query and then another Select (via the Get) for each MediaFile in the original query. To be fair, though, I haven't run the query to find out if this is in-fact true. — Anthony

Firo Firo · Accepted Answer · 2013-09-24T12:16:19

using the LINQ provider you can write

var query = from post in session.Query<Post>()
            select new PostSummary { Post = post, CommentCount = post.Comments.Count };

return query.ToList();

How can I select the root entity and child entity count using QueryOver in NHibernate?

1 Answers