Mapping an Inheritance Hierarchy
So far in this chapter, you have seen how LINQ to SQL has been used to map entity classes to database tables, in a one-to-one fashion. There are times, however, when constraining an entity model to a flat hierarchy can feel too restrictive, and you may want to use inheritance in your entity model. This is possible with LINQ to SQL using single table inheritance.
When multiple classes of a hierarchy are stored within the same table, this is called single table inheritance. It works by dedicating a column within the table to represent the type of the particular entity class.
Although this approach decreases the level of normalization in a database, it can be an effective approach for entity classes that have a small degree of variation in their storage needs.
The following example creates an entity model representing bank accounts. These bank accounts objects are stored using a single table in a local database.
The example code for this section is located in the /DataDrivenApps /LinqToSqlInheritanceMapping directory of the WPUnleashed.Examples project in the downloadable sample code.
At the base of the class hierarchy is the BankAccount class, and deriving from this are the three classes: SavingsAccount, CheckingAccount, and FixedDepositAccount (see Figure 29.11).
The two primary elements for mapping an inheritance hierarchy are the InheritanceMapping attribute and the Column attribute’s IsDiscriminator property.
Listing 29.11 shows that the BankAccount class declares each of its derived types using the InheritanceMapping attribute. The Code property of the InheritanceMapping attribute is the unique column value representing the derived type. The BankAccountTypeId is decorated with a Column attribute that has its IsDescriminator property set to true, indicating that it corresponds to the InheritanceMapping attribute’s Code property.
Thus, if an entity is a SavingsAccount, LINQ to SQL sets its BankAccountTypeId to 0. If the entity is a CheckingAccount, then BankAccountTypeId is set to 1. And so on.
LISTING 29.11. BankAccount Class
[Table]
[InheritanceMapping(Code = 0, Type = typeof(SavingsAccount), IsDefault = true)]
[InheritanceMapping(Code = 1, Type = typeof(CheckingAccount))]
[InheritanceMapping(Code = 2, Type = typeof(FixedDepositAccount))]
public class BankAccount : NotifyPropertyChangeBase
{
int id;
[Column(
IsPrimaryKey = true,
DbType = "INT IDENTITY NOT NULL",
IsDbGenerated = true,
UpdateCheck = UpdateCheck.Never)]
public int Id
{
get
{
return id;
}
set
{
Assign(ref id, value);
}
}
int bankAccountTypeId;
[Column(DbType = "Int NOT NULL", IsDiscriminator = true)]
public int BankAccountTypeId
{
get
{
return bankAccountTypeId;
}
set
{
Assign(ref bankAccountTypeId, value);
}
}
decimal balance;
[Column]
public decimal Balance
{
get
{
return balance;
}
set
{
Assign(ref balance, value);
}
}
}
Only the base class is decorated with a Table attribute; entity subclasses are not. Members of the subclass that are to be stored in the database are decorated with a Column attribute (see Listing 29.12).
LISTING 29.12. CheckingAccount Class
[Index(Columns = "CheckbookCode")]
public class CheckingAccount : BankAccount
{
string checkbookCode;
[Column(DbType = "NCHAR(16)")]
public string CheckbookCode
{
get
{
return checkbookCode;
}
set
{
Assign(ref checkbookCode, value);
}
}
}
The custom BankingDataContext class has a single BankAccounts property and is used in the same manner as the Twitter example earlier in this chapter (see Listing 29.13).
LISTING 29.13. BankingDataContext Class
public class BankingDataContext : DataContext
{
public BankingDataContext(string connection) : base(connection)
{
Log = new DebugStreamWriter();
}
public Table<BankAccount> BankAccounts
{
get
{
return GetTable<BankAccount>();
}
}
}
The BankingDataContextTests class, in the WPUnleashed.Tests project, demonstrates storage and retrieval of various banking entity types. A custom BankingDatabaseUtility is used to initialize the database file. Two accounts are inserted into the database, and then are retrieved using LINQ to SQL. See the following excerpt:
[TestMethod]
[Tag("i1")]
public void ContextShouldReadAndWrite()
{
using (BankingDataContext context = databaseUtility.CreateContext())
{
SavingsAccount savingsAccount = new SavingsAccount {Balance = 50};
CheckingAccount checkingAccount = new CheckingAccount
{
Balance = 100,
CheckbookCode = "12345"
};
context.BankAccounts.InsertAllOnSubmit(new List<BankAccount>
{
savingsAccount,
checkingAccount
});
context.SubmitChanges();
}
using (BankingDataContext context = databaseUtility.CreateContext())
{
List<BankAccount> accounts = context.BankAccounts.ToList();
Assert.IsTrue(accounts.Count > 2, "There should be two accounts.");
IQueryable<SavingsAccount> savingsAccounts
= context.BankAccounts.OfType<SavingsAccount>();
Assert.IsTrue(savingsAccounts.Any());
SavingsAccount account = savingsAccounts.First();
Assert.IsTrue(account.Balance == 50, "Balance should be 50");
}
}
Having support for mapping inheritance hierarchies in LINQ to SQL is a tolerable way of bridging the object relational divide.