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, Sep 18 '13 at 13:19
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, Sep 18 '13 at 14:28
I encountered the problem today trying to answer this question http://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 http://stackoverflow.com/a/17663212/1236044 — jbl, Sep 18 '13 at 15:07
@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, Sep 18 '13 at 15:31
@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, Sep 18 '13 at 15:35
I guess not, because of the first line `var allMediaFiles =...`, and its `Future`. Worth a try I would say — jbl, Sep 18 '13 at 15:38
it does not have SELECT N+1 because i fill the session cache with the first query and session.Get() does chekc it first. — Firo, Sep 24 '13 at 12:09
@Firo I see what it's doing now - though its disappointing it has to do two separate queries (though with SQL Server I believe it uses a multi-query). If you add an answer here I can accept it. — Anthony, Oct 08 '13 at 13:21
i already posted an answer which is more appropriate to your question than the linked answer. Future automaticly uses multiquery if supported. — Firo, Oct 08 '13 at 14:23
The posted answer doesn't actually answer my question though, which was specifically how to accomplish such a thing using the `QueryOver` syntax. — Anthony, Oct 08 '13 at 14:50

score 1 · Answer 1 · answered Sep 24 '13 at 12:16

1

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();

answered Sep 24 '13 at 12:16

Firo

30,626
4
55
94

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

1 Answers1