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-to-map-a-tree-in-nhibernate.aspx
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.
