Mapping one-to-one relationship with a wrong (brownfield) database schema

Question

I have to model this relationship in NHibernate (simplified the code a bit to stay on-topic) - an employee can be an accountmanager (so, it's optional):

table Employee (Id, Number, Name) table EmployeeIsAccountManager (Number, MaxAllowedDiscount)

instead of having an Id foreign key in table EmployeeIsAccountManager pointing to table Employee, I have a Number column in the Employee table which points to the Number column in table EmployeeIsAccountManager.

How do I map this in NHibernate? I've tried using the foreign generator on the EmployeeIsAccountManager class mapping, but if I use Number as foreign generated value, it maps to the ID of Employee, which is Id instead of Number. I modeled my class to use composition:

public class Employee
{
   public virtual int Id { get; set; }
   public virtual short Number {get; set; }
   public virtual string Name {get; set; }
   public virtual AccountManager AccountManager { get; set; }
}

public class AccountManager
{
  public virtual short Number { get; set; } /*needed because of ID only?*/
  public virtual decimal MaxAllowedDiscount { get; set }
}

I've tried a lot (one-to-one, many-to-one, foreign generator) but I can't figure out if this can be done with NHibernate. btw: I can change the classes, mappings, etc but I CANNOT change the table structure because of it's brownfield status (old application with 2+ million lines of code, nearly 1000 forms).

Any help is appreciated, thanks! Ted

Look up the property-ref mapping attribute... – JulianM Jan 14 '11 at 04:26 — JulianM, Jan 14 '11 at 04:26

score 0 · Accepted Answer · answered Feb 28 '11 at 14:13

Your question make me think about class inheritance, you could map your class AccountManager as a subclass of Employee and then you will able to do what you want to do, I'v etested a mapping for you but as you designed tables that way does not resolve your needs because there are two points you have to notice in your mapping:

The number property in the Employee table should be a unique kind of foreing key to AccountManager in order to be used as a foreign key but even so it does'mt work because NHibernate, when you try to insert a new Account Manager will insert a record in the person table and then assign the id of person to the number column of AccountManager an that break you needs.
Mapping that relation as many-to-one doesn't work for the same reason. The Number property of AccountManager is primary key? is unique? NHibernate cannot work without primary keys, so in order to make that relation to work you have to specify the Number propety of AccountManager as an Id column

The last option which comes in my mind is to use a property in the Employee class mapped to the AccountManager table with a formula where you can specify a custom select to obtain the value you need I assume the property MaxAllowedDiscount, but this too has some limitation, when you map a property with formula, this property cannot be inserted nor updated.

Hope this helps lat me konw if there are questions.

I added an extra redundant field to the database schema so I could map this as I should. the field itself is managed by triggers in the database. — TedOnTheNet, Apr 27 '12 at 08:23

score 0 · Answer 2 · answered Nov 25 '11 at 16:04

public class Employee
{
    public virtual short Number
    {
       get { return (AccountManager == null) ? 0 : AccountManager.Number; }
       set
       {
           if (AccountManager == null)
               AccountManager = new AccountManager();
           AccountManager.Number = value;
       }
    }
    public virtual AccountManager AccountManager { get; set; }
}

or with GeneratedBy.Assinged()

public class Employee
{
    public Employee()
    {
        AccountManager = new AccountManager();
    }

    public virtual AccountManager AccountManager
    {
        get;
        set { value.Parent = this; _accountManager = value; }
    }

    public class AccountManager
    {
        Internal protected virtual Employee Parent { get; set; } 

        protected virtual short Number { get { return Parent.Number; } set { } } /*needed because of ID only?*/
        public virtual decimal MaxAllowedDiscount { get; set }
    }
}

Mapping one-to-one relationship with a wrong (brownfield) database schema

2 Answers2