get total number of comments for entity using query over

Question

I am trying to get the total comments for an entity yet I am getting back incorrect results.

How would I get a Task and its comments counts (eagerly loaded)

        var tasks = _session.QueryOver<Task>(() => taskAlias)
                                 .Where(x => x.OrganizationID == null)
                                 .Fetch(x => x.AssignedUser).Eager
                                 .Fetch(x => x.Owner).Eager
                                 .Fetch(x => x.Comments).Eager
                                 .List();

This returns:

Task.ID 1 Comments 3 Task.ID 1 Comments 3 Task.ID 1 Comments 3

Task.ID 2 Comments 2 Task.ID 2 Comments 2

Task.ID 3 Comments 1

I want:

Task.ID 1 Comments 3

Task.ID 2 Comments 2

Task.ID 3 Comments 1

My fluent mappings are:

HasMany(x => x.Comments).Table("tComments").ForeignKeyConstraintName("fT_Task_ID").KeyColumn("fC_Resource_ID").Where("fC_Type = 'Task'").ReadOnly();

So your saying your root entity is repeating? if so use: .TransformUsing(new DistinctRootEntityResultTransformer()) — rie819, Nov 05 '12 at 15:58
I just want to know why the root is repeating - looking at my mappings that should not happen right? — Haroon, Nov 05 '12 at 17:00
It's happening because your query isn't identifying that the root should be unique. In the back end your doing a join on multiple tables which will result in a cross product coming back (SQL). The DistinctRootEntityResultTransformer tells Nhibernate that the root element needs to be unique. — rie819, Nov 05 '12 at 18:03

rie819 · Accepted Answer · 2012-11-05T18:58:32.793

2

If I understand your issue correctly, the problem is that your getting the correct data back, it's just duplicating at the root level. If this is the case, change your query to:

var tasks = _session.QueryOver<Task>(() => taskAlias)
                                 .Where(x => x.OrganizationID == null)
                                 .Fetch(x => x.AssignedUser).Eager
                                 .Fetch(x => x.Owner).Eager
                                 .Fetch(x => x.Comments).Eager
                                 .TransformUsing(new DistinctRootEntityResultTransformer())
                                 .List();

When you have an entity and it contains a collection of entities and you tell NHibernate to fetch the collections eagerly the SQL that is generated will essentially return as:

TASK 1 - COMMENTS 1A
TASK 1 - COMMENTS 1B
TASK 1 - COMMENTS 1C
TASK 2 - COMMENTS 2A
TASK 2 - COMMENTS 2B
...

The DistinctRootEntityResultTransformer is what we use to tell NHibernate that the root element should be unique. As your original code didn't have the transformer your root elements are duplicated.

edited Nov 05 '12 at 18:58

answered Nov 05 '12 at 16:01

rie819

1,249
12
19

thanks your answer fixed my issue, I was just concerned that should not happen for a 1 to m mapping. – Haroon Nov 06 '12 at 09:28
The problem with this solution is: Skip & Take do not work as expected, so I have unchecked this answer as it does not resolve my problem. Have you tried skip and take with your answer? – Haroon Nov 06 '12 at 13:50
Check out this article. http://kuhnel.wordpress.com/2011/02/13/paging-eager-fetched-nhibernate-collections-efficiently/ – rie819 Nov 06 '12 at 14:16
Or this page gets to the root of the issue without all the explanation: http://programminghacks.net/2012/07/15/nhibernate-paging-by-root-entity-with-eager-join-fetch-of-a-child-collection/ – rie819 Nov 06 '12 at 14:18

get total number of comments for entity using query over

This returns:

1 Answers1