Here is an excerpt of the schema for the top level of OdeToCode:
The Community_ContentPages table carries all of the data needed by every type of content – like moderation flags, published date, and owner ID. Community_Articles carries just the extra information needed for a published article – like the article text itself.
Inside the code, Article and Book classes derive from ContentPage. Inheritance simplifies the implementation, because a great deal of the business logic applies to all the different types of content objects. For instance, the moderation and approval rules apply to articles, book reviews, comments, images, and other entities that all derive from ContentPage.
Inheritance mapping is one good litmus test for the capabilities of an ORM product. LINQ to SQL, for example, only handles one of the three common strategies for modeling inheritance in an RDBMS – the "table per class hierarchy" strategy, which doesn't help me in this scenario. Most full featured OR/M products, like NHibernate, support multiple strategies, including the "table per subclass" strategy I need.
The Entity Framework documentation includes some pointers on table per subclass mapping (known as "table per type" or TPT in EF terminology). Although the EF designer is easy for getting tables and columns spit out into a mapping file, I found working with the designer a bit tedious. Trying to model inheritance using the designer created build errors, so I did most of the XML editing by hand. The key to TPT mapping is:
Once the XML work is done, it's relatively easy to pull out persisted entities.
Given the following DTO:
I can use the following LINQ query to pull out only Article objects. The trick is the OfType() operator:
Although I still have some reservations about EF, I'm warming up to the framework...
*If the naming convention strikes you as odd, then remember the schema was designed to work in a shared hosting environment where a single database might need to support multiple applications. The Community prefix helps to avoid naming collisions. An equivalent design today could use schemas in SQL 2005.