Fluent NHibernate: Display many-to-many-with-many sets (with optional columns) and sample selection

Question

I need a Fluent NHibernate mapping that will do the following (if nothing else, I will also take the appropriate NHibernate XML mapping and reconstruct it).

---

DETAILS

I have a many-to-many relationship between two objects: Parent and Child . This is done using an additional table to store the parent and child identifiers. However, I also need to define two additional columns for this mapping, which provide additional information about the relationship.

This is roughly how I defined my types, at least the corresponding parts (where Entity is the base type that provides the Id property and checks for equivalence based on that Id):

 public class Parent : Entity { public virtual IList<ParentChildRelationship> Children { get; protected set; } public virtual void AddChildRelationship(Child child, int customerId) { var relationship = new ParentChildRelationship { CustomerId = customerId, Parent = this, Child = child }; if (Children == null) Children = new List<ParentChildRelationship>(); if (Children.Contains(relationship)) return; relationship.Sequence = Children.Count; Children.Add(relationship); } } public class Child : Entity { // child doesn't care about its relationships } public class ParentChildRelationship { public int CustomerId { get; set; } public Parent Parent { get; set; } public Child Child { get; set; } public int Sequence { get; set; } public override bool Equals(object obj) { if (ReferenceEquals(null, obj)) return false; if (ReferenceEquals(this, obj)) return true; var other = obj as ParentChildRelationship; if (return other == null) return false; return (CustomerId == other.CustomerId && Parent == other.Parent && Child == other.Child); } public override int GetHashCode() { unchecked { int result = CustomerId; result = Parent == null ? 0 : (result*397) ^ Parent.GetHashCode(); result = Child == null ? 0 : (result*397) ^ Child.GetHashCode(); return result; } } } 

The tables in the database look something like this (assume primary / foreign keys and forgiveness syntax):

 create table Parent ( id int identity(1,1) not null ) create table Child ( id int identity(1,1) not null ) create table ParentChildRelationship ( customerId int not null, parent_id int not null, child_id int not null, sequence int not null ) 

I'm fine with Parent.Children - a lazy loaded property. However, ParentChildRelationship should look forward to downloading ParentChildRelationship.Child. In addition, I want to use Join when I want to download.

SQL, when accessing Parent.Children, NHibernate should generate an equivalent query:

SELECT * FROM ParentChildRelationship rel LEFT OUTER JOIN Child ch ON rel.child_id = ch.id WHERE parent_id = ?

OK, so for this I have mappings that look like this:

 ParentMap : ClassMap<Parent> { public ParentMap() { Table("Parent"); Id(c => c.Id).GeneratedBy.Identity(); HasMany(c => c.Children).KeyColumn("parent_id"); } } ChildMap : ClassMap<Child> { public ChildMap() { Table("Child"); Id(c => c.Id).GeneratedBy.Identity(); } } ParentChildRelationshipMap : ClassMap<ParentChildRelationship> { public ParentChildRelationshipMap() { Table("ParentChildRelationship"); CompositeId() .KeyProperty(c => c.CustomerId, "customerId") .KeyReference(c => c.Parent, "parent_id") .KeyReference(c => c.Child, "child_id"); Map(c => c.Sequence).Not.Nullable(); } } 

So, in my test, if I try to get myParentRepo.Get(1).Children , it really gets me all the relationships and when I access them from the relationship, Child objects (for example, I can capture them by doing parent.Children.Select(r => r.Child).ToList() ).

However, the SQL that NHibernate generates is inefficient. When I access parent.Children, NHIbernate does SELECT * FROM ParentChildRelationship WHERE parent_id = 1 , and then a SELECT * FROM Child WHERE id = ? for each child in every respect. I understand why NHibernate does this, but I cannot figure out how to configure the mapping to make the NHibernate request the same as I mentioned above.

Answer 1

I don’t understand why it doesn’t work the way you do it, but I can tell you how I would compare it:

 <class name="Parent"> <id .../> <list name="Children" table="ParentChildRelationship"> <key column="parent_id"/> <index column="Sequence"/> <composite-element> <property name="CustomerId"/> <many-to-one name="Child"/> </composite-element> </list> </class> <class name="Child"> <id .../> <property .../> </class> 

To improve performance, try doing this with a join-to-one connection:

  <many-to-one name="Child" fetch="join" />