I think Not.LazyLoad() is the problem. NH will always execute the select for children because you told it never to lazy load and fetching is defaulted to select (not join).
On Mar 4, 3:52 am, DannyT <[email protected]> wrote: > I'm trying to load an entire self referencing hierachy in a single database > call using the following: > > var sql = @"with Hierachy(Id, Value,ParentMessage_Id, Customer_id, Level) > as > ( > select Id, Value, ParentMessage_Id, > Customer_id, 0 as Level > from [Message] m > where m.Customer_id = :custId > and m.ParentMessage_Id is null > union all > select m.Id, m.Value, m.ParentMessage_Id, > m.Customer_id, mh.Level +1 as Level > from [Message] m > inner join Hierachy mh > on m.ParentMessage_Id = mh.Id > ) > select Id, Value, ParentMessage_Id, Customer_id > from Hierachy "; > var list = Session.CreateSQLQuery(sql) > .AddEntity(typeof(Message)) > .SetInt32("custId", customer.Id) > .List<Message>(); > return list; > > Derived from the technique shown > at:http://blogs.hibernatingrhinos.com/nhibernate/archive/2008/05/14/how-... > > The query returns all of the messages in my tree however when viewing in > NHProf it's executing the above query then proceeding to individually load > the children again for each message. > > My Message Class is: > > public class Message > { > public Message() > { > this.ChildrenMessages = new List<Message>(); > } > > public virtual int Id { get; set; } > > public virtual Customer Customer { get; set; } > public virtual Message ParentMessage { get; set; } > public virtual string Value { get; set; } > > public virtual IList<Message> ChildrenMessages { get; set; } > } > > And my mapping is handled by Fluent with the following override: > > public void Override(FluentNHibernate.Automapping.AutoMapping<Message> > mapping) > { > mapping.References(x => x.ParentMessage).Cascade.SaveUpdate(); > mapping.HasMany(x => x.ChildrenMessages) > .Inverse() > .KeyColumn("ParentMessage_id") > .Not.LazyLoad(); > } > > Other approaches I have tried which result in pretty much the same thing > happening: > > base.Session.CreateCriteria(typeof(Message)) > .SetFetchMode("ChildrenMessages", FetchMode.Eager) > .Add(Expression.IsNull("ParentMessage")) > .Add(Expression.Eq("Customer", customer)) > .SetResultTransformer(new > DistinctRootEntityResultTransformer()) > .List<Message>(); > > and > > Session.CreateQuery( > "select m from Message m join fetch m.ChildrenMessages > where m.Customer.id = :custId" > ) > .SetInt32("custId", customer.Id) > .SetResultTransformer(new > DistinctRootEntityResultTransformer()) > .List<Message>(); > > Is there someway I can tell NH that it already has everything it needs and > not to re-load all the child collections individually? Or am I missing > something more obvious? > > Any help greatly appreciated :) > > Dan > > --http://danny-t.co.uk -- 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.
