5

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?

David
  • 15,750
  • 22
  • 90
  • 150

1 Answers1

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; }
Handcraftsman
  • 6,863
  • 2
  • 40
  • 33
  • My question may not have made this clear, but it was always my intention to use two separate properties in the class. What I really want to know is how I can query independently on the ExpenseOrLiability property, when to do so would require a LIKE (rather than =) operator in the SQL query. – David Nov 29 '10 at 09:37
  • @David Solve the LIKE query problem by making the intrinsics of the code queryable. You do that by denormalizing or storing them as separate fields in the database. The important thing is to keep your data synchronized by updating either the code OR the intrinsic properties but not both. – Handcraftsman Nov 29 '10 at 15:15
  • Thanks again, Handcraftsman. Unfortunately I'm working with a legacy database whose schema I have no control over. I would never design a database schema like this! – David Dec 02 '10 at 09:07