Thanks very much Jason, I'll have a play with these ideas and post back what I find.
On Dec 3, 1:31 pm, Jason Meckley <[email protected]> wrote: > 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.
