Fluent NHibernate: mapping complex many-to-many (with additional columns) and setting fetch

Question

I need a Fluent NHibernate mapping that will fulfill the following (if nothing else, I'll also take the appropriate NHibernate XML mapping and reverse engineer it).

---

DETAILS

I have a many-to-many relationship between two entities: Parent and Child. That is accomplished by an additional table to store the identities of the Parent and Child. However, I also need to define two additional columns on that mapping that provide more information about the relationship.

This is roughly how I've defined my types, at least the relevant parts (where Entity is some base type that provides an 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 approximately like (assume primary/foreign keys and forgive 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 OK with Parent.Children being a lazy loaded property. However, the ParentChildRelationship should eager load ParentChildRelationship.Child. Furthermore, I want to use a Join when I eager load.

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

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

OK, so to do that 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 does in fact get me all the relationships and, as I access them from the relationship, the Child objects (for example, I can grab them all by doing parent.Children.Select(r => r.Child).ToList()).

However, the SQL that NHibernate is generating is inefficient. When I access parent.Children, NHIbernate does a SELECT * FROM ParentChildRelationship WHERE parent_id = 1 and then a SELECT * FROM Child WHERE id = ? for each child in each relationship. I understand why NHibernate is doing this, but I can't figure out how to set up the mapping to make NHibernate query the way 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 map 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 enhance performance, try to make it fetch the many-to-one by a join:

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