if Assessors and Assessors.Responses are Sets (not Lists) then the
DistinctRootEntityTransformer will work because of the nature of Set.
If they are lists then you may want to look into sub selecting the
Assessors.Responses (and even the Assessors). one query is not always
the most optimal approach.
I was just spiking the various fetching strategies and found the
following mapping can produce good queries by default.
class Root{IList<Assessor> Accessors;}
class Assessor{IList<Response> Responses;}
<class Root>
...
<bag name="Accessors" fetch="select" batch-size="50">
...
</bag>
</class>
<class Accessor>
...
<bag name="Responses" fetch="select" batch-size="100">
...
</bag>
</class>
the queries
var roots = session.CreateCriteria<Root>().Add(...).List<Root>();
var allTheResponses = roots.SelectMany(root=>root.Accessors.SelectMany
(accessor=>accessor.Responses).ToArray();
will produce the following sql
select [fields] from Root where ...
select [fields] from Accessor where RootId in (select id from Root
where [id's are in list]) //in sets of 50
select [fields] from Request where AccessorId in (select id from
Accessor where [id's are in list]) //in sets of 100
so if you have 99 roots returned from the original query the select
Accessor query will execute twice. (Ids 1-50 and 51-99).
Say there are 150 Accessors related to the Roots. the select Response
query will execute twice as well (Ids 1-100, 101-150).
This will execute 5 queries in total, but that seems acceptable when
traversing a collection of roots down to the grand children.
play around with this and look at the queries produced in NhProf. You
should be able to find an optimal solution.
On Dec 3, 5:57 am, mattcole <[email protected]> wrote:
> Hi,
> I'm trying to prefetch a collection and for each element in that
> collection I want to prefetch a property that is also a collection.
>
> The domain model is along the lines of
>
> Assessment has a collection of Assessors. Each Assessor has a
> collection of Responses. I'm applying a prefetch strategy such as:
>
> return criteria
> .SetFetchMode("Assessors", FetchMode.Eager)
> .SetFetchMode("Assessors.Responses", FetchMode.Eager)
>
> This generates the correct SQL (thanks NHibernate Profiler), but when
> I examine my entity after loading it's loaded duplicate entries. I'm
> testing it on an Assessment that has a single Assessor who has two
> Responses.
>
> The Assessors collection on the Assessment has an Assessor per
> Response (i.e. two) rather than a single assessor with two responses
> as expected.
>
> I've tried the DistinctRootEntityTransformer, but as I expected it
> didn't really work as I'm getting a single distinct root entity (the
> Assessment).
>
> Is it possible to do what I'm trying to do?
>
> Thanks,
> Matt
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
