FluentNHibernate mapping of one column to two properties: is this possible?

Question

The legacy database I work with has a table with some sample information shown below:

LiabilityType
134
137
140
143
146
999
001
003
006
009

These codes actually contain two bits of information:

1. Whether the debt is classified as an expense or liability (if code starts with '1' -> expense; if it starts with '0' -> liability
2. The type of debt (e.g. mortgage, funeral expense, overdraft etc)

As such, I would like to map this field to two properties in my entity.

I have looked at ICompositeUserType, but this appears to be about mapping two fields to one (composite) property, not mapping one field to two properties.

I can see how I can create two classes implementing IUserType which examine this field and convert it to the correct property values, but I can't work out how the class would convert the properties back to the appropriate database values. For example, I would like to map this such that I can create a linq query where I can say;

.Where(x => x.ExpenseOrLiability == ExpenseOrLiability.Expense)

and this will be converted into SQL like the following:

WHERE LiabilityType LIKE '1%'.

Is such a thing possible?

Answer 1

We handled this situation through denormalization. Store the code and the debt classification type in separate fields. This allows you to present the code to the user but use its intrinsics in queries.

To prevent the values from getting out of sync you have to derive one from the other. For example, derive the breakout:

public virtual LiabilityType LiabilityType { get; set; }
public virtual ExpenseOrLiability ExpenseOrLiability
{ 
    get
    {
        return // extract ExpenseOrLiability from LiabilityType
    }
    set{} // ignore set
}
public virtual DebtType DebtType
{ 
    get
    {
        return // extract DebtType from LiabilityType
    }
    set{} // ignore set
}

OR derive the code

public virtual LiabilityType LiabilityType 
{
   get
    {
       return // combine ExpenseOrLiability with DebtType somehow
    } 
   set { } // ignore set
}

public virtual ExpenseOrLiability ExpenseOrLiability { get; set; }
public virtual DebtType DebtType { get; set; }