Introduction

In this series of blog entries I’m going to take a look at some work I have been doing with the ADO.NET Entity Framework (AEF) (VS 2008, .Net 3.5), comparing it to LinqToSql, finding the odd bits that aren’t as obvious as they seem and basically giving my impressions of working / understanding it…

You can view my other entries here

Part I - Getting Started

Inheritance in the Entity Framework

What does ADO.NET Entity Framework have in Inheritance that LinqToSql doesn’t?

The big thing that AEF has that is truly missing from LinqToSql is Table-Per-Type inheritance, it only supports Table-Per-Hierarchy.

This is Table-Per-Hierarchy.  All of the objects are stored in one table, a field in the table is used to denote which type the object is, but each row has entries for ALL different types that might be used.  This can be useful, but let’s not kid ourselves and call this anything but what it is, a limited usage workaround.  Any database table that has unused columns… Well if you need to add a new type, you need to modify your table to include the new “unique” properties, increasing the size of your database, unnecessarily.

This is Table-Per-Type.  There is a base table to hold the base type and all the derived types have separate tables that hold just the data particular to them.  In addition in SQL it is obvious to see what type is what type.  All the derived types get their ID from the base table and we have a one to one relationship. Any 4th level normalization freak (which I can be from time to time) will appreciate this setup.

Some housekeeping

I did a little housekeeping from yesterday’s project and I noted that I had mistakenly used AEFTestApp.  According to the new “standards” for .net development, Tla’s (Three Letter Acronym) should not be completely capitalized, but should participate in camelCase. So I modified all the odd places in the project where AEF was located to be Aef.

In addition I added a “using AefTestApp.Model” to my main program so we don’t have to fully qualify all these objects every time they are used.

Implementing Inheritance in AEF

From yesterday, our database structure looks like this

And our AEF design environment is currently looking like

So let’s go:

Looking at our database you can see I want to derive an employee type and a contractor type from a person type. So let’s start by opening our AEF designer, right mouse on it, and selecting to add the Contractor and Employee tables to the model

and here is what we get after a little table arranging

Ok that doesn’t look AT ALL like it is inherited, and in fact, it isn’t.  Here the model can infer what we want to do must based on the Foreign Keys in the database and so it adds our tables using the simplest “known” truth, a straight forward one to one relationship. Let’s start by trying to hook up the Employee.

Right mouse on the person and select Add… Inheritance

In the Add Inheritance dialog make sure the base entity is set to Person, and for the derived entity, pick Employee, and click OK

And now we have a change

That looks right, but do we still need that old association?  Right mouse click on the designer surface and choose (all together now)

   Validate!

And what do you know, we have an error of the form

Association End '%1' is not mapped

Now this took a few tries before I understood all that happens here. To start with, we DON’T need the old Association once we have set up the inheritance, but the problem here is the designer (in this version at least) is not smart enough to tweak the model up for us, we need to do it manually.  Unfortunately there are three steps you have to take, and you wouldn’t know it from the errors given

To get rid of our first error, delete the old association and validate again

Now we have an error of the form

Problem in Mapping Fragment starting at line %1: Must specify mapping for all key properties (%2) of the EntitySet %3.

in our case the specific error we got was

Problem in Mapping Fragment starting at line 224: Must specify mapping for all key properties (People.Id) of the EntitySet People.

So the obvious thing to do is take a look at the mapping properties for the employee… WHAAA?  Yes, the error says nothing about the employee, but that is in fact where the error lies.  I find in general, although the errors tell you what is wrong with the model, they don’t tell you in a way you can decipher / relate to the model.

So here are the mapping details of the Employee

OK, I hear you , I don’t see anything in here to do with the People.Id, but it is in fact the PersonId that the model is choking on.  You see with Inheritance, the model “knows” its value should come from the person table, so go back to the Employee object and delete PersonId Property from the Employee, and now look at the mappings (you can validate if you want and see that you now have two errors)

As you can see the PersonId is missing a value, if you drop it down, you will see that now we have an “ID” available to us in the mapping.

Since the employee table does not have an Id column, that value must be coming from someplace else, and in fact it is, it is coming from the Person table mapped thru the inheritance association (Primary Keys are available to you once you set up an inheritance). Here you are telling the environment to map the Id from person to the value in the PersonId column in the Employee.  It will take care of keeping it all straight!

Now hit Validate, and you are done!  Go ahead and do the exact same steps to the Contractor, (I will too). And now we have:

Using Inheritance

So how do we use this stuff?

Well back to our Main in the program and lets see if I can get a list of employees printed out, and contractors (with their address). First up let’s Refactor out the part where the addresses for a person are printed out.

Here is what we have in code after that.

static void Main ( string[] args ) {

    //set up reference to the data model
    var data = new AefTestApp.Model.AefTestEntities( );

    //Get a list of our people
    var people = data.People.ToList( );

    Console.WriteLine( "Here is everyone" );
    Console.WriteLine( );

    foreach ( var person in people ) {

        PrintPersonInfo( person );

        PrintAddresses( person );

        Console.WriteLine( );
        Console.WriteLine( );
    }

}

private static void PrintPersonInfo ( AefTestApp.Model.Person person ) {
    //print out thier name and birthday
    Console.WriteLine( "{0}, {1} {2}: born on {3}" , person.LastName , person.FirstName , person.MiddleName , person.BirthDate.HasValue ? person.BirthDate.Value.ToString( "s" ) : "Unknown" );
}

private static void PrintAddresses ( AefTestApp.Model.Person person ) {
    //let's load our address data
    person.Addresses.Load( );

    //and print it out
    foreach ( var address in person.Addresses ) {
		Console.WriteLine( @"	{0}
	{1}
	{2}
	{3}, {4} {5}{6}" ,
            address.AddressType , address.Street1 , address.Street2 , address.City , address.State , address.Zip , string.IsNullOrEmpty( address.ZipPlus4 ) ? "" : "-" + address.ZipPlus4 );
    }
}

And if we run it, we get the same thing from yesterday.

Now let’s modify this to get out two different lists, employees first and Contractors next.

The obvious thing would be (and what you might be expecting) is to do something like this

//Get a list of our people
var employees = data.Employees.ToList( );

foreach ( var employee in employees ) {

    PrintPersonInfo( employee );

    PrintAddresses( employee );

}

But as much as I hate to say it, that won’t work, because you don’t have an Employees type on the AefTestEntities model.  Let’s quickly go back to the model and take a look at the properties of the Employee type.

The thing to notice here is that the EntitySetName has been set to “People” and there is no way for you to override it.  That would imply that you have to use the People entity set to get your employees.  Well that’s doesn’t seem much of an advantage, you are still getting all the people then having to go off and look up the employees, or at least that was what I first assumed but a little Binging showed me there was a way to get JUST my employee objects.  It’s not obvious at all!

You do get to the Employees thru the People Entity Set but we tell it we want only those people who are of type Employee, like this:

static void Main ( string[] args ) {

    //set up reference to the data model
    var data = new AefTestEntities( );

    Console.WriteLine( "Here Are Our Employees" );
    Console.WriteLine( );

    //Get a list of our people
    var employees = data.People.OfType<Employee>( ).ToList( );

    foreach ( var employee in employees ) {

        PrintPersonInfo( employee );

    }

    Console.WriteLine( );
    Console.WriteLine("--------------------------" );
    Console.WriteLine( );

    Console.WriteLine( "Here Are Our Contractors" );
    Console.WriteLine( );

    var contractors = data.People.OfType<Contractor>( ).ToList( );

    foreach ( var contractor in contractors ) {

        PrintPersonInfo( contractor );

        PrintAddresses( contractor );
        Console.WriteLine( );

    }

}

And here is the output we get from that

For the record, I don’t like that construct anymore then you do, and in fact it Violates Microsoft’s own Code Analysis / FxCop rule.  A rule by the way that Code Analysis help tells you should never be overruled.  LOL. Do as they say, not as they do!

The CA rule by the way is (to paraphrase) “Don’t require the user to have to enter a type in a templated function, infer the type from passed in arguments”

So before we are done with today’s entry, let’s make that code a BIT more clear using my new favorite refactoring trick, Extensions!

Let’s add a new class to the model folder in the project and call it AefDataModelExtensions. Furthermore declare the class as internal static.

Add using System.Data.Objects; to the new class, and create a new public static method called Employees like this

public static ObjectQuery<Employee> Employees ( this AefTestEntities data ) {
    var employees = data.People.OfType<Employee>( );
    return employees;
}

And do the same thing for Contractors giving us an extension class of this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;

namespace AefTestApp.Model {
	internal static class AefDataModelExtensions {

		public static ObjectQuery<Employee> Employees ( this AefTestEntities data ) {
			var employees = data.People.OfType<Employee>( );
			return employees;
		}

		public static ObjectQuery<Contractor> Contractors ( this AefTestEntities data ) {
			var contractors = data.People.OfType<Contractor>( );
			return contractors;
		}

	}
}

And there, now we have logical methods added to our Data Model

so now our loops look something like this

var employees = data.Employees( ).ToList( );

foreach ( var employee in employees ) {
    PrintPersonInfo( employee );
}

And that looks much better to my eye (yes the extra parenthesis are different from our other entity set call methods, but I can live with that).

Finally just to show you that we are in fact doing inheritance, you will notice that the refactoring left our printout method signature as working against Person objects

private static void PrintPersonInfo ( Person person ) {

And that works fine, but now when we print out, we want to be able to print out some specific information for employees, that isn’t present in Contractors and vice versa.

Notice here, the employee object has a department but the contractor object does not!

Inheritance at its finest.

So here is our final main code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AefTestApp.Model;

namespace AEFTestApp {
	class Program {
		static void Main ( string[] args ) {

			//set up reference to the data model
			var data = new AefTestEntities( );

			Console.WriteLine( "Here Are Our Employees" );
			Console.WriteLine( );

			//Get a list of our people
			var employees = data.Employees( ).ToList( );

			foreach ( var employee in employees ) {

				PrintPersonInfo( employee );
				Console.WriteLine( "        Department: {0}" , employee.Department );

			}

			Console.WriteLine( );
			Console.WriteLine("--------------------------" );
			Console.WriteLine( );

			Console.WriteLine( "Here Are Our Contractors" );
			Console.WriteLine( );

			var contractors = data.Contractors( ).ToList( );

			foreach ( var contractor in contractors ) {

				PrintPersonInfo( contractor );

				PrintAddresses( contractor );
				Console.WriteLine( "        Rate: ${0} / hour" , contractor.HourlyRate.ToString("C") );
				Console.WriteLine( );

			}

		}

		private static void PrintPersonInfo ( Person person ) {
			//print out thier name and birthday
			Console.WriteLine( "{0}, {1} {2}: born on {3}" , person.LastName , person.FirstName , person.MiddleName , person.BirthDate.HasValue ? person.BirthDate.Value.ToString( "s" ) : "Unknown" );
		}

		private static void PrintAddresses ( Person person ) {
			//let's load our address data
			person.Addresses.Load( );

			//and print it out
			foreach ( var address in person.Addresses ) {
				Console.WriteLine( @"	{0}
	{1}
	{2}
	{3}, {4} {5}{6}" ,
					address.AddressType , address.Street1 , address.Street2 , address.City , address.State , address.Zip , string.IsNullOrEmpty( address.ZipPlus4 ) ? "" : "-" + address.ZipPlus4 );
			}
		}
	}
}

and here is our result (and that’s not a typo, Lazarus is expensive, what would you expect from a 4,000 year old man):