Demystifying The Code

Entity Framework Modeling : Entity Splitting

Entity Splitting involves mapping a single entity in the conceptual layer to multiple tables in the store.  To put that into English, suppose that you have a normalized data structure.  Within this structure, you have a table that stores “People” and additional tables that store information about specific kinds of people like “Students”, “Business Students”, “Instructors” and “Admins” (see below).  In this post, I will illustrate how to implement Entity Splitting in the Entity Framework.

image

(Disregard the TPT in each of the table names.  I have various Person tables in this database to illustrate varying mapping scenarios.  If you are curious, TPT stands for Table Per Type and these tables will be used in a future screencast to illustrate the Table Per Type mapping)

The motivation for building a data model such as this should be pretty clear.  Essentially it comes down to protecting data.  Suppose that we have business rules that state that every Student have a degree defined.  We would like to set this column as non-nullable.  However, if Students were stored in a generic People table along with Admins and Instructors, this would not be possible.  This is because the degree column would have to be nullable because Admins and Instructors do not have degrees (in the case of this example).  By storing the Student, Admin, Instructor and BusinessStudent information in their own tables, we are able to use nullability to enforce certain business rules.

What I just outlined in the previous paragraph is a databaseism.  What I mean by that is that we defined a specific model to protect our data.  The needs of applications consuming this data were not considered (rightfully so) when defining the model.  That is the role of the Conceptual Layer in the Entity Data Model.  In our example, we want to define a single entity named “Student” whose data is sourced from the PersonTPT and StudentTPT tables.  With the Entity Framework, this is easy to do.

Entity Framework Training

View The Screencast

 You can view the screencast here.

Implementing Entity Splitting

Step 1: Add the Entity Data Model

image

Step 2: Generate the Model From The Database

image 

image

Step 3: Choose the source tables

image

Here is what our conceptual layer looks like now:

image

We want to combine these 2 entities into one.  We are going to change the PersonTPT entity into our final Student Entity.  To do that, follow the next steps:

Step 4: Rename the PersonTPT Entity and Entity Set Name

image

Step 5: Cut and Paste the properties from StudentTPT to the Student Entity (our old PersonTPT entity)

Move all of the properties that you want to include in the new Student Entity.

image

Step 6: Delete the StudentTPT Entity

image

Step 7: View Mapping Details

image

image

Step 8: Map the StudentTPT table

Choose the StudentTPT table under Add a Table or View

image

The Columns will automatically map, because the names line up

image

We are done and can now test our model.

Consume the Conceptual Layer

Let’s write some code to consume the conceptual layer.  I probably failed to note that I am using a Console Application for this sample.  Given that, here is the code:

static void Main(string[] args)
{
    using (SchoolDataEntities school =
        new SchoolDataEntities())
    {
        var students = from s in school.Students
                       select s;

        foreach (var s in students)
        {
            Console.WriteLine("{0}, {1}: Degree {2}",
                s.LastName,
                s.FirstName,
                s.Degree);
        }

        Console.ReadLine();
    }

}

Notice that our LINQ query is pulling from our new Students Entity Set.  Also notice that the Student Entity contains information from the PersonTPT table (LastName, FirstName), as well as information from the StudentTPT table (Degree, etc.).

I hope that illustrated how to implement Entity Splitting using the Entity Framework.  In my next post, I will illustrate how you can implement inheritance in conjunction with entity splitting.

Comments

5 Responses to “Entity Framework Modeling : Entity Splitting”
  1. Nick says:

    Great example. Is it possible to do this via a foreign key relationship. For example imagine PK of Student was StudentId and Person table contained a FK of StudentId. I’ve tried but keep hitting bugs.

  2. admin says:

    The relationship has to be 1:1.

  3. scott stewart says:

    when I tried it, after deleting StudentTPT, it was no longer available for mapping, hence could not get this to work

  4. admin says:

    You likely chose yes when the popup came up. What the popup was asking was do you want to delete the entity from the SSDL, as well. You did not want to do that. You want it in the SSDL and now want to map that table to another entity.

Trackbacks

Check out what others are saying about this post...
  1. [...] Entity Framework Modeling: Entity Splitting [...]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

Demystifying The Code