When working in Silverlight RIA applications, there are some common scenarios that are useful:

• Table Per Type inheritance – Useful when you have one table that “extends” another table with additional data.  Your main table would be a common “type” and you’d have one (or more) tables which add more data.  More information
• Table Per Hierarchy inheritance – Useful when you have one table that has many “types” inside it with some data useful depending on the “type”.  Usually, one column in your table would denote the “type” of data, and from there, your application would read additional columns.  These additional columns often would be unused if a row’s “type” was not of a particular value.  More Information
• Table Per Concrete Type inheritance – Useful when you want to have a “history” table in addition to the “current” data.  More Information

All 3 of these are useful in their own times.  But what if you needed to mix types?

Here is a particular database scenario which I believe to be quite common:

In this setup, we have a database representing an online store.  One table lists all the products.  Some products are shirts, other products are shoes.  Two additional tables exist to provide additional data depending on the product type (similar to Table Per Type).  However, we have a discriminator (ProductType) to determine the product type (similar to Table Per Hierarchy).  In our case, if ProductType == 1, then there’s a Shirt record corresponding to the Product record.

For lack of a better name, I’m calling this Table Per Type Hierarchy inheritance.

By default, entity framework would generate the following:

 

This would work, however, there are 3 side-effects to this:

1. Product has a navigation member called “Shirt”.  This means “Product has a Shirt”.  Likewise, “Shirt has a Product” since Shirt has a navigation member called “Product”.  In reality, “Shirt is a Product”.   This is “unnatural”.
2. If I wanted to query all the Products which are Shirts, then I’d have to query “where ProductType == 1”.
3. There are no safeguards to ensure that “if ProductType == 1 then a Shirt record exists”.
   

Instead, we want to setup a conditional hierarchy.  At first, you may think that we can just use Table Per Hierarchy to create a hierarchy between Shirts and Products and Shoes and Products, then add a conditional for Shirt “where ProductType = 1”.  However, this is not the case.  The entity framework user interface does not allow you to select a member from Product as a condition for Shirt.  Maybe this is a bug.  Maybe you can do this by editing the Xml.  To get this to work, you need to add a proxy entity between Product and Shirt.

Step 1.  Start with your default entity framework setup.

 

Step 2.  Remove the 2 associations that were generated.  Click each and delete.

 

Step 3.  Remove the ProductType member from Product.

Step 4.  Make Product abstract.

Step 5.  This step is exactly as is done for normal Table Per Hierarchy inheritance.  Create a new entity and call it “ShirtProduct”.  Derive this new entity from Product.  Map the new entity to the Products table.  In the mapping, add a condition such that ProductType = 1.  But don’t move any properties from Product to ShirtProduct.

Step 6.  This step is just like normal Table Per Type inheritance.  Derive Shirt from ShirtProduct. 

Remove ProductId from Shirt since we already have it in Product.  Once we’ve done that, our entity model should look like this:

 

 

Step 7.  Repeat steps 5 and 6 for Shoe, but set it’s condition to “ProductType = 2”.  Our model now looks like:

The blue rectangle represents the portion using Table Per Hierarchy inheritance.  The red rectangle represents the portion using Table Per Type inheritance.

If we had any products which did not require extra data in secondary tables, we could just use Table Per Hierarchy inheritance.  For example, if we had a third product type called “Pants”, then we could create a new entity called “Pants” (not “PantsProduct”), derive it from Product and set it’s condition to be ProductType = 3.  In this case, the proxy class is the working class.

To access the classes, we do so just like Table Per Type.  The example code shows ‘get’ functions for a DomainContext in a Silverlight RIA appliication:

public IQueryable<Shirt> GetShirts()
{
	return this.ObjectContext.Products.OfType<Shirt>();
}

public IQueryable<Shoe> GetShoes()
{
	return this.ObjectContext.Products.OfType<Shoe>();
}

When I generated my DomainContext, I chose to retrieve and update the ProductTable.  So my DomainContext already had GetProducts() and InsertProduct().  This means:

1. I can use GetProducts() to get a list of all products, and
2. I can insert a product of any type by calling InsertProduct()

Actually, for a Silverlight RIA application, you may choose to not call InsertProduct() directly.  Instead, you can use the following code:

Shirt shirt = new Shirt();
shirt.ProductId = Guid.NewGuid();
shirt.Title = "Red Polo";
shirt.Price = new Decimal(11.99);
shirt.Colour = "Red";
shirt.Style = "Polo";

ProductDomainContext context = 
   (ProductDomainContext)domainDataSource1.DomainContext;
context.Products.Add(shirt);
context.SubmitChanges();

 

Notice 2 things:

1. I am using the normal entity framework model to insert a Shirt into the list of Products.
2. I am not setting Product.ProductType anywhere.

The entity framework takes care of inserting the correct data into the Shirts table, the correct data into the Products table, and setting ProductType = 1 properly.

Magical!

Included is a sample Silverlight RIA application that demonstrates this.  It includes a list of all products, a list of shirts, and a list of shoes.  It also inserts a new shirt into the database.

TPTHSample.zip (4.58 MB)

I’d like to thank Lingzhi Sun over on the MSDN Forums for help in this matter.