Fluent NHibernate HasManyToMany with a discriminator column specifying relationship type

Question

I'm using Fluent NHibernate and I'm trying to map a many-to-many property that simply bypasses the join table. The tricky thing is, the join table has a column that determines what type of relationship it is.

For the purpose of this question, let's say I have a person table and a relation table.

PersonTable (PersonId, Name, etc)
RelationTable (RelationType, PersonIdA, PersonIdB)

I want to introduce a collection property in Person class for each type of relationship e.g. Sons, Daughters, etc.

HasManyToMany<Person>(x => x.Sons)
    .Table("RelationTable")
    .ParentKeyColumn("PersonIdA")
    .ChildKeyColumn("PersonIdB")
    .Where("RelationType='A_IS_FATHER_OF_B_BOY'");

HasManyToMany<Person>(x => x.Daughters)
    .Table("RelationTable")
    .ParentKeyColumn("PersonIdA")
    .ChildKeyColumn("PersonIdB")
    .Where("RelationType='A_IS_FATHER_OF_B_GIRL'");

The above mappings are working for reading from the database but not for inserting, for example:

Person john = PersonDAO.GetByName("John"); // the Sons and Daughters are loaded fine based on mappings above

john.Sons.Add(new Person("Jack"));  // let's add a new son
PersonDAO.Save(john);               // this fails because RelationType is null

Basically when saving Jack as John's new son in the RelationTable I need to have RelationType populated with "A_IS_FATHER_OF_B_BOY", which is not currently happening. The directive .Where("RelationType='A_IS_FATHER_OF_B_BOY'") is only effective for loading but not for saving.

Any ideas? I think this is somewhat similar to the Discriminator attribute for subclasses.

Any help appreciated. Thanks.

Answer 1

I would say, exactly as you pointed out in your comment:

I actually do have the RelationTable mapped with 2 many-to-one references to Person (as PersonA and PersonB). Using one-to-many (HasMany), how then do you suggest I map Sons and Daughters (both List<Person>) in the Person class taking into account the discriminator values as above

So the pairing object in my eyes would be

public class PersonRelation
{
    // the pairing table must contain the key column, surrogated ID
    public virtual int Id { get; protected set; } // this is a must. 

    public virtual Person Parent { get; set; }
    public virtual Person Child { get; set; }
    public virtual string RelationType { get; set; }
}

There MUST be key for this table. Just inject some IDENTITY column with SQL Server.. but have some with surrogated (business domain independent) key.

Here is our Person entity

public class Person
{
    IList<PersonRelation> _sons;
    IList<PersonRelation> _daughters;
    ..,
    public virtual IList<PersonRelation> Sons 
    {
        get { return _sons ?? (_sons = new List<PersonRelation>()); }
        set { _sons = value; }
    }
    public virtual IList<PersonRelation> Daughters
    {
        get { return _daughters?? (_daughters= new List<PersonRelation>()); }
        set { _daughters= value; }
    }
}

The mapping for Sons (Daughters would be same):

HasMany<PersonRelation>(x => x.Sons)
    // .Table("RelationTable") // not needed - part of PersonRleation mapping
    .KeyColumn("PersonIdA")
    .Where("RelationType='A_IS_FATHER_OF_B_BOY'")
    .Inverse()
    .Cascade.AllDeleteOrphan() 
;

That would work, if we will always assure, that when adding Son, we also properly set RelationType

var parent = ...; // new, loaded
var child  = ...; // new, loaded

var relation = new PersonRelation
{
    Parent = parent;
    Child  = child;
    RelationType = "A_IS_FATHER_OF_B_BOY";
};

parnet.Sons.Add(relation);

This must be either part of the Businese layer AddSon() or it could be POCO public method...

NOTE: we can also map reverse end of that relation ... even without relationType filtering:

public class Person
{
    ... as above
    public virtual IList<PersonRelation> Parents { get; set; }


HasMany<PersonRelation>(x => x.Parents)
    // instead of this
    // .KeyColumn("PersonIdA")
    // we need this column
    .KeyColumn("PersonIdB")
;