Entity Framework Model Inheritance with SQL CE 4

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:

image

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

image

My Person schema looks like this:

image

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.

image

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

image 

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

image

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.

image

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

image

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:

image

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.

image

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.

image 

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

image

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.

image

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:

image 

And the Student and Employee display templates are:

image

Finally, this is the output that gets rendered. You’ll see the change in the background color of the two partial renderings.

image

Sample code

Verdict: Entity Framework + SQL CE = Easy to use and makes way for some powerful programming - that says it all!

Published Tuesday, March 29, 2011 12:20 PM by nmarun

Comments

# re: Entity Framework Model Inheritance with SQL CE 4

Tuesday, March 29, 2011 2:14 AM by Lohith

Excellent dude... keep it comin :)

# re: Entity Framework Model Inheritance with SQL CE 4

Tuesday, April 3, 2012 3:42 PM by is this db

Is the DB not being normalized?

Leave a Comment

(required) 
(required) 
(optional)
(required)