Hi, This can be formulated as SELECT N+1 issue (see http://ayende.com/Blog/archive/2008/12/01/solving-the-select-n1-problem.aspx) But this link is not a fix for your particular situation, you were stating that you are working with a legacy database model, so i guess adapting the schema a little is out of the question.
You could extend your link query to eagerly fetch the childs, but without schema adaptation there is no real quick fix for this one :( If you can adapt the schema you should use a table from where you could query all the child for 1 parent if you need advice on this look at Ayende's Security model (Rhino Security) he uses such a model for his groups i think. Kind regards and best of luck On 3 feb, 11:38, Boz <[email protected]> wrote: > Anyone? > > On Jan 22, 3:29 pm, Boz <[email protected]> wrote: > > > Whoops, the key columns in the mapping should be NodeId, not ChildId. > > Sorry > > > Boz > > > On Jan 22, 2:44 pm, Boz <[email protected]> wrote: > > > > Hi, > > > > I'm an nHibernate novice at the moment, and have been advised to post > > > here from the Fluent group to see if you guys can suggest an efficient > > > mapping or some SQL for a multi parent relationship. Then the Fluent > > > guys can help me translate any suggestions you have into Fluent code > > > (if appropriate). > > > > The scenario... > > > > I have a legacy database with two tables which allow nodes to map to > > > other nodes in a hierarchy. Simplified, > > > the tables are: > > > > NodesTable (NodeId, Name) > > > ParentsTable (NodeId, ParentId) > > > > This can modelled in a single entity and its map (please excuse the > > > map being Fluent nHibernate code!): > > > public class Node > > > { > > > public virtual decimal NodeId { get; set; } > > > public virtual string Name { get; set; } > > > public virtual IList<Node> Parents { get; set; } > > > public virtual IList<Node> Children { get; set; } > > > } > > > > public class NodeMap : ClassMap<Node> > > > { > > > HasManyToMany(x => x.Parents) > > > .ParentKeyColumn("ChildID") > > > .ChildKeyColumn("ParentID") > > > .Inverse(); > > > HasManyToMany(x => x.Children) > > > .ParentKeyColumn("ParentID") > > > .ChildKeyColumn("ChildID"); > > > } > > > > This maps fine, and the collections all contain the correct parents > > > and children > > > > The application requires that the "downward" (child) relationships for > > > each node are shown in a tree view (and nodes with many parents are > > > indicated as "shared" with other nodes in the tree). I'm retrieving > > > this initial "tree" > > > with: > > > > public IList<Node> GetNodes() > > > { > > > var query = from nodes in Session.Linq<Node>() > > > orderby nodes.Name ascending > > > select nodes; > > > return query.ToList(); > > > } > > > > Now to the problem.... > > > > The tree view shows the nodes correctly, but it is *very* slow even > > > with only 50 or so records in each table. When configuring the maps > > > Parents and Children maps to eagerly load you can see a just how bad > > > the performance is - many queries are executed in both directions > > > (each node's parents and each node's children) > > > > Perhaps this can be solved by enhancing the session query? Or by > > > manipulating each map's FetchType? Being an nHibernate novice, I'd > > > really appreciate any guidance you guys have. > > > > Boz -- 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.
