Entity Framework Model Inheritance with SQL CE 4

Tuesday, March 29, 2011

For some (unknown) reason, I’ve been curious to work with SQL CE, just hadn’t gotten a chance to work with it. If you’re not familiar with what SQL CE is all about, go through this article from Scott Gu.

To begin with I have an empty MVC 3 application. In the App_Data folder, I choose to add a SQL Server Compact 4.0 Local Database calling it Person:

Switch to the Server Explorer to add a new table to the database:

My Person schema looks like this:

Just as a reminder, all this is done using Visual Studio 2010 itself (no switching back-and-forth between SQL Management Studio and VS2010).

The general idea is this. This table represents a person who is either employed or is a student. The IsStudent column makes this distinction. If the entry is a student, the IsStudent will be true, UniversityName column will have a value and the CompanyName remains null. The vice-versa is true for a person who is employed. That completes my SQL Compact database work. Let’s get to the Entity Framework side of the project.

I have added an ADO.NET Entity Data Model to my project and named it as PersonModel.

Right-click in the white-space of the page and choose Add->Entity to bring up the Add Entity dialog:

Set the entity name as Student and the base type as Person. The resulting diagram will look like this:

Now we know that UniversityName column belongs to a student, so let’s move it down to the Student entity. To do this, just cut the column name from the Person entity and paste it in the Student entity.

I’ll go ahead and do the same process to create an Employee entity and move the CompanyName column to this entity.

Open the Mapping Details window (View->Other Window->Entity Data Model Mapping Details). Select the Employee entity and make the changes as shown in the image below:

Here’s what all that means. For this Employee entity, the CompanyName property is correctly mapped to the CompanyName column of the Person table. The column UniversityName makes no sense for the Employee entity so there’s no mapping for it and remains null for this entity. There’s also a condition added here for the IsStudent column. This maps the Person table entry to the Employee entity when the value is 0.

A very similar set up is done for the Student entity as well with a difference that there will be no mapping for the CompanyName column and the IsStudent condition is set to 1.

I also delete the IsStudent property from the Person entity as the derived entities define this property. As a last step, I mark the Person entity as abstract (Right-Click->Properties->Abstract=True). And I’m done setting up my entity model.

Coming to the coding part now, I have a Create action method in the Home controller.

You see that the UniversityName and the IsStudent properties are missing from this entity. The PersonId and Name are inherited from the Person entity and since CompanyName property belongs to the Employee entity, it shows up in bold face.

        1: public ActionResult Create()

       2: {

       3:     using (PersonEntities personEntities = new PersonEntities())

       4:     {

       5:         Employee employee = new Employee

       6:                                 {

       7:                                     Name = "Vijay Kumar",

       8:                                     CompanyName = "Contoso Inc",

       9:                                     // IsStudent, UniversityName not visible

      10:                                 };

      11:         // .AddToEmployee/Student not an option

      12:         // cos Student and Employee are not database concepts

      13:         // they're only entities mapping to the Person entity

      14:         personEntities.AddToPeople(employee);

      15:  

      16:         Student student = new Student

      17:                                 {

      18:                                     Name = "Suraj Kumar",

      19:                                     UniversityName = "Contoso School Of Art",

      20:                                 };

      21:         personEntities.AddToPeople(student);

      22:         personEntities.SaveChanges();

      23:     }

      24:  

      25:     return RedirectToAction("Index");

      26: }

That right there is the complete implementation of Create action method. I run the app and go to home/create page. This adds the two records to the database table and gets redirected to the Index action method (not yet shown).

Before we go into the Index action method, let’s see what it looks like in the database. From the Server Explorer, right-click on the Person table and choose New Query.

There they are - the two entries that we created. You see that IsStudent, UniversityName and CompanyName columns are set accordingly on the two records.

My Index action method is just a call to pull out all the entries of the table and pass the collection to the view.

       1: public ActionResult Index()

       2: {

       3:     List<Person> people = new List<Person>();

       4:     using (PersonEntities personEntities = new PersonEntities())

       5:     {

       6:         people = personEntities.People.ToList();

       7:     }

       8:     return View(people);

       9: }

There’s an interesting thing that happens when I run the application.

The two entries that get loaded into the person collection are of type Employee and Student and not of type Person. This is due to the condition that we set up in the Mapping Details window earlier. This is absolutely amazing and can be used for various types of implementation. As a simple example, I can render these two types differently in my output. See below for the views: