Hi George When I use the fetch function it allows me to pull back an entire hierarchy in one sql call as it uses joins instead of separate sql calls. I'm not sure why your hieracrhy is creating so much sql.
I think the best thing for you to do would be to play with the fetch statement, both in your mapping files and your Criteria statement. Phil On Aug 24, 4:59 pm, George Mauer <[email protected]> wrote: > Update: I tried the following: > > public FolderHierarchyNode Get() > { > using (var s = _sessions.OpenSession()) > return s.CreateQuery("select n from > FolderHierarchyNode n join fetch n._children") > .SetResultTransformer(new > global::NHibernate.Transform.DistinctRootEntityResultTransformer()) > .List<FolderHierarchyNode>() > .Single(n => n.Parent.IsNull()); > } > > This resulted in actually twice as many queries being issued. After > reading up on DistinctRootEntityResultTransformer, I don't see how it > can achieve my desired results since the problem I'm having is that I > get multiple queries, not that I get duplicate rows as a result of a > single query. > > On Aug 24, 9:43 am, philw <[email protected]> wrote: > > > Hi > > > I think this might be of some help to you, ot was for me > > >http://ayende.com/Blog/archive/2009/08/28/nhibernate-tips-amp-tricks-... > > > Phil > > > On Aug 24, 3:38 pm, George Mauer <[email protected]> wrote: > > > > I have the following simple domain model representing a hierarchy: > > > > public class FolderHierarchyNode > > > { > > > public virtual Guid Id { get; private set; } > > > public virtual string Name { get; private set; } > > > public virtual FolderHierarchyNode Parent { get; set; } > > > ICollection<FolderHierarchyNode> _children = new > > > HashSet<FolderHierarchyNode>(); > > > public virtual IEnumerable<FolderHierarchyNode> Children { get > > > { return _children; } } > > > public FolderHierarchyNode() { } > > > } > > > > I only need to track children, not the level in the hierarchy which I > > > should be able to do with this mapping: > > > > <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" > > > assembly="MyApp.Domain" namespace="MyApp.Domain.FolderHierarchy"> > > > <class name="FolderHierarchyNode" table="NODES"> > > > <id name="Id" column="PK_NODES" type="System.Guid" unsaved- > > > value="00000000-0000-0000-0000-000000000000"> > > > <generator class="guid" /> > > > </id> > > > <property name="Name" column="NDELNAME" /> > > > > <many-to-one name="Parent" column="FK_NODES" > > > class="FolderHierarchyNode" not-null="true" /> > > > <set name="_children" access="field" cascade="all-delete-orphan" > > > lazy="false"> > > > <key column="FK_NODES" /> > > > <one-to-many class="FolderHierarchyNode"/> > > > </set> > > > </class> > > > </hibernate-mapping> > > > > I can then query for the parent node simply like this: > > > public FolderHierarchyNode Get() > > > { > > > using (var s = _sessions.OpenSession()) > > > return s.Linq<FolderHierarchyNode>() > > > .ToArray() > > > .Single(n => n.Parent.IsNull()); > > > } > > > > This generates at first the following SQL: > > > SELECT > > > this_.PK_NODES as PK1_4_0_, this_.NDELNAME as NDELNAME4_0_, > > > this_.FK_NODES as FK3_4_0_ > > > FROM NODES this_ > > > > The results of this query should already contain all the information > > > necessary to build up the object tree but then I get a plethora more > > > select statements (I assume to fetch the children of each node): > > > > SELECT children0_.FK_NODES as FK3_1_, children0_.PK_NODES as PK1_1_, > > > children0_.PK_NODES as PK1_4_0_, children0_.NDELNAME as NDELNAME4_0_, > > > children0_.FK_NODES as FK3_4_0_ FROM NODES children0_ WHERE > > > children0_.fk_nod...@p0;@p0 = 106e707a-699c-4c7e-a625-f3add43c0aee > > > SELECT children0_.FK_NODES as FK3_1_, children0_.PK_NODES as PK1_1_, > > > children0_.PK_NODES as PK1_4_0_, children0_.NDELNAME as NDELNAME4_0_, > > > children0_.FK_NODES as FK3_4_0_ FROM NODES children0_ WHERE > > > children0_.fk_nod...@p0;@p0 = 89e18057-eabe-466f-b3e6-e1ce02c90bfd > > > .... > > > etc. > > > > What's the best way to get rid of these un-necessary sql calls? My > > > best guess is that I can drop the children from the mapping and in my > > > repository wire them up manually, but I'm hoping there's a more > > > integrated way to do this -- 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.
