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.
