How can I map to a joined subclass with a different column than the id of parent?

Question

I am working with a brownfield database and am trying to configure a subclass map which joins to its subclasses with a column other than that of the specified id. The login table has a primary key column login_sk which I'd like to use as its id. It joins to two tables via a login_cust_id column (to make things more fun the corresponding columns in the adjoining tables are named differently). If I setup login_cust_id as the id of the UserMap it joins to its subclasses as expected. For what I hope are obvious reasons I do not want to use login_cust_id as the id for my User objects.

public class UserMap : ClassMap<IUser>
{
    public UserMap()
    {
        Table("login");
        Id(x => x.Id).Column("login_sk"); // want to setup map like this

        // if used instead this works for subclass joining / mapping
        // Id(x => x.Id).Column("login_cust_id");
        // would prefer to only reference login_cust_id for subclass mapping
    }
}

public class CustomerUserMap : SubclassMap<CustomerUser>
{
    public CustomerUserMap()
    {
        Table("customer");
        Map(c => c.DisplayName, "cust_mail_name");
        Map(c => c.RecordChangeName, "cust_lookup_name");
        KeyColumn("cust_id");
    }
}

public class EntityUserMap : SubclassMap<EntityUser>
{
    public EntityUserMap()
    {
        Table("entity");
        Map(c => c.DisplayName, "entity_name");
        KeyColumn("entity_id");
    }
}

What I'd like to do is only use the login_cust_id column when joining to subclasses. Is there a fluent mapping setting that allows me to specify this? If not a fluent mapping is there a regular NHibernate XML mapping that would work? I'd prefer to not even map the column and only use it for joining if possible. If it helps there is a potential discriminator column login_holder_type which indicates which table to join to.

It did occur to me to setup an IClassConvention but after poking at the passed IClassInstance I could not determine any settings which would help me.

public class UserIdConvention : IClassConvention, IClassConventionAcceptance
{

    public void Apply(IClassInstance instance)
    {
        // do something awesome with instance.Subclasses to
        // specify the use of login_cust_id for subclass joining...
    }

    public void Accept(IAcceptanceCriteria<IClassInspector> criteria)
    {
        criteria.Expect(x => typeof(User).Equals(x.EntityType));
    }
}

The lack of a populated Subclasses collection for the passed instance caused me to look for a more specific inspector which IParentInspector appears to be. Unfortunately Fluent NHibernate does not appear to have corresponding implementations for IParentInstance, IParentConvention or IParentConventionAcceptance like it does for IJoinedSubclassInspector. While I could probably implement my own before I do I wanted to ensure I wasn't barking up the wrong tree.

Is this sort of subclass id adjustment even possible? Am I missing something obvious in either my map or the Fluent NHibernate Conventions namespace? How can I map to a joined subclass with a different column/property than the id of parent?

Answer 1

I was able to think of three possible solution to your problem please see my findings below.

Solution 1: Discriminator based mapping with Join

My initial idea was to use a discriminator based mapping for modelling the inheritance, with each sub-class containing a join with a property ref, i.e

<class name="IUser" abstract="true" table="login">
    <id name="Id" column="login_sk">
        <generator class="identity"/>
    </id>
    <discriminator column="login_holder_type" not-null="true" type="System.String"/>

    <subclass name="CustomerUser" discriminator-value="Customer">
        <join table="customer" >
          <key column="cust_id" property-ref="login_cust_id" />
          <property name="DisplayName" column="cust_mail_name"/>
          <property name="RecordChangeName" column="cust_lookup_name" />
        </join>
    </subclass>

    <subclass name="EntityUser" discriminator-value="Entity">
      <join table="entity" >        
        <key column="entity_id" property-ref="login_cust_id" />
        <property name="CompanyName"/>
      </join>
    </subclass>
</class>

Unfortunately at this time this feature is supported in Hibernate but not in NHibernate. Please see here and here for the outstanding tickets. Some work has gone towards adding this feature which can be seen on this fork on github.

Solution 2: Discriminator based mapping with Many-to-One

Another option is to still use the discriminator based mapping, but use a many-to-one mapping within each of the sub-classes, which would allow you to join on the foreign key using a property-ref. This has the disadvantage of requiring separate classes for all of the properties in your customer and entity tables but is a workable solution.

<class name="IUser" abstract="true" table="login">
    <id name="Id" column="login_sk">
        <generator class="identity"/>
    </id>
    <discriminator column="login_holder_type" not-null="true" type="System.String"/>

    <subclass name="CustomerUser" discriminator-value="Customer">
        <many-to-one name="CustomerProps" property-ref="login_cust_id" />
    </subclass>

    <subclass name="EntityUser" discriminator-value="entity">
        <many-to-one name="EntityProps" property-ref="login_cust_id" />     
    </subclass>
</class>

<class name="CustomerProps" Table="customer" >
  <id name="Id" column="cust_id">
    <generator class="assigned"/>
  </id>
  <property name="DisplayName" column="cust_mail_name"/>
  <property name="RecordChangeName" column="cust_lookup_name" />
</class>

<class name="EntityProps" Table="entity" >
  <id name="Id" column="entity_id">
    <generator class="assigned"/>
  </id>
  <property name="CompanyName"/>
</class>

Solution 3: Discriminator based mapping with Joins to Updatable Views

The final option is to create an Updatable View in the DB for the customer and entity tables which contains the login_sk field. You can then use Join within each sub-class as you wouldn't require the property-ref.

<class name="IUser" abstract="true" table="login">
    <id name="Id" column="login_sk">
        <generator class="identity"/>
    </id>
    <discriminator column="login_holder_type" not-null="true" type="System.String"/>

    <subclass name="CustomerUser" discriminator-value="Customer">
        <join table="customerView" >
          <key column="login_sk" />
          <property name="DisplayName" column="cust_mail_name"/>
          <property name="RecordChangeName" column="cust_lookup_name" />
        </join>
    </subclass>

    <subclass name="EntityUser" discriminator-value="Entity">
      <join table="entityView" >        
        <key column="login_sk" />
        <property name="CompanyName"/>
      </join>
    </subclass>
</class>