How to share common fields between two entities that map to different tables

Monday, May 24, 2010

The title is a mouthful so let me explain what we are trying to solve. Suppose we have two tables in the database that have common fields. When we import the tables into Entity Data Model, we want to create a base entity to contain the common fields and let other entities derive from it. Since the base entity does not map to any table in particular in our database, we want to make base entity abstract. If you have reached this far and understood what the problem is, then follow along to know to solve it!

Suppose we have two tables in the database called Person and Organization as shown below.

Notice that CreateDate is common to both tables. Additionally both tables primary key, PersonId and OrganizationId are store generated and it wouldn’t hurt to move them to a common base entity. So let’s begin our hard work. Follow the steps to build the model.

1. Import Person and Organization table into Entity Data Model.

2. Using Add Entity dialog, create a new entity and call it Base. Make sure Create Key property is checked and give the key property a generic name like Id. Figure below shows the screen shot.

3. Copy CreateDate property from either Organization or Person entity and paste it on our Base entity. Make Base entity abstract.

4. Delete OrganizationId and CreateDate property from Organization entity. Then delete PersonId and CreateDate from Person entity.

If you go further with the designer and make Person and Organization entity derive from the Base entity, it would force your derive entities to be part of the base entityset. In lay man terms you are saying that i guarantee that all entities that are under the same entity set have unique key keys. For example this would mean that there wont be a case where we have OrganizationId as 1 and PersonId as 1. Well this doesn’t hold true in our case and also not in all cases where the records are coming from two different tables like Organization and Person table.

So rest of the journey we will have to complete by modifying the xml manually.

5. Open up the edmx file in xml and remove entityset called Bases from the CSDL section.

You are removing this because Base entity is really not part of any entityset. It is simply an abstract class that allows us to share common properties among entities.

6. Derive Organization and Person entity from Base entity by setting BaseType to PracticeModel.Base for each entity. (CSDL)

7. Fix the mapping for Organization and Person entity by adding mapping for two scalar properties namely Id and CreateDate. This is not possible in the designer because both entities map to different tables in the database. Figure below shows the correct mapping.

Notice Id property on the Organization entity inherited from the Base is mapped to OrganizationId column on the Organization table and Id property on the Person entity inherited from Base is mapped to PersonId column on Person table.

Build the model and make sure you do not have any build errors.

I cover this same exact concept in my recipe book but that example is a little more complex where it has an association. There are two recipes that are useful in my book that are in the same lines.

15-5. Sharing Audit Fields Across Multiple Entities

15-4. Fabricating Additional Inheritance Hierarchies

To visit the table of contents for the other recipes in my book please look at the below link.

http://weblogs.asp.net/zeeshanhirani/archive/2010/05/04/entity-framework-4-0-recipes-book.aspx

1 Comment