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 II - Inheritance

Getting Started

Why ADO.NET Entity Framework over LinqToSql?

Because we have no choice.  That answer is actually a bit tongue-in-cheek, but it has a kernel of truth to it.  LinqToSql is not slated to get any updates in the next release, and officially according to MS, it is deprecated.  The issue here is that LinqToSql was never really supposed to be part of the official release according to the scuttlebutt, but since the entity framework wasn’t completely ready when 3.5 shipped they included LinqToSql as a stop gap.  I suspect they are sorry they did that now.  It was the ideas in LinqToSql that apparently helped drive AEF design. It had always been the intention to have a single framework that abstracted the business objects from the data base. 

Now don’t get me wrong I REALLY like LinqToSql but it lacks some support the AEF has that makes it seem like it is worthwhile to look at changing.  That’s what we are doing here.

Our Database

To start with here is the structure of our database that I am using in this example

you can download a SQL 2008 .bak of it here 

So here you can see we have a Person table with two related tables, contractor and employee that will allow us to do Table-Per-Type Inheritance.  Now this is something that LinqToSql WON’T let you do, and that alone is worth the price of admission to me (LinqToSql only does Table-Per-Hierarchy Inheritance).  In addition we have a mapping between person and address.  A Person can have multiple addresses (home and work for example) and an address can be shared by multiple people (two spouses who both work for you for example), duplicated and extraneous data is to be avoided.

Let’s just start by getting our project up and running. I’m going to create a quick console test app, so we can concentrate on the AEF.

Now I create a new Model folder in the project

Right mouse on the new folder and add a new item

I Select an ado.net entity framework model

Select Generate from Database and click next.  Now I set up my connection string to the database and add it to my app.config

Hit next, then I’m going to select add just 2 tables, Person, and PersonAddressMap.  I want to do this bit by bit so I can show some of the issues that may come up for you.  I’m also going to rename the namespace for the model to AefData

And here is what I get

Now one of the things I’ve learned is, as you are getting started, regularly get into the habit of checking the validity of your model at almost every step and be prepared for the necessity of using the undo command, until you totally understand what the model design environment is doing

You will get a warning if there is validity problem, if everything is OK you get no indication, other then no reported issues.

By default the designer during database generation names the business objects after the table names, and the entity sets returned from the model are given the same name, first thing I do, is rename the business object to the singular (if necessary) then I rename the entity sets to be the plural.  Makes the usage of the code much more logical and self documenting (to me at least)

  becomes

Now you can see we have the map table so let’s bring in the address table… (this may not go the way you may expect from LinqToSql)

Right mouse on the design environment and select Update Model from Database…

In the Update Wizard dialog, drill down and select the Address table and click finish

Now look at what you got

The address table was added, BUT it wasn’t added thru the Map table, it was added directly and it has a many to many relationship defined.  The AEF environment has successfully understood what the map table was intended to do and has “hooked” it up correctly for you.  The Person object now has an Address navigation property and the address has a person navigation property.

Before we go any further let’s validate our model, and you will see that in fact validation fails with

Problem in Mapping Fragments starting at lines <%Line>, <%Col>: Two entities with possibly different keys are mapped to the same row. Ensure these two mapping fragments map both ends of the AssociationSet to the corresponding columns.

Wow, what does that mean? I gotta tell you there isn’t a lot of good information on the AEF out there on the web, the documentation is pretty poor and the Bing and Goggle searches don’t have much to index on to help you find out, but here is what it means:

The AEF has detected that within a single model, multiple entry methods to the PersonAddressMap table exist, meaning it would be possible for a programmer to set up conflicting edits.  Since the AEF understands that the Person to Address association happens thru the map table, as you add or edit rows to either of those entity sets, it will make the appropriate modifications in the map to keep your relationships correct, but… it is possible for you to go directly into the PersonAddressMap entity set directly thru a different entry point, you could set up a condition where they don’t match, and that is what that error is trying to tell you.

Right mouse click on the relationship between Person and Address, and click on Table Mapping

You get a nice mapping details screen

As you can see, the AEF understands that the relationship happens thru the PersonAddressMap entity set and has mapped the values to plug in to the map table from the source tables.

Since the PersonAddressMap entity set is directly accessable from your model, AEF says,

“Hey, a programmer (not you of course) may not be as smart as you and corrupt the data, so don’t you think it might be a good idea to not allow that?”

So the solution here is Delete the PersonAddressMap entity set from your model and try validate again

  Lo and Behold, no validation errors

Now one other thing I like to change, since this is a many to many relationship, I don’t like the naming convention on the Navigation Properties, I really think they should be plural so it is obvious to the programmer that a person can have multiple addresses and addresses can have multiple people.

Sweet, so now let’s go use this model

So off we go into the main entry for the program and let’s do a little printing out (and notice the really nice intellisense you get).

Here is our code:

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

namespace AEFTestApp {
	class Program {
		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 item in people ) {
				
				//print out thier name and birthday
				Console.WriteLine( "{0}, {1} {2}: born on {3}" , item.LastName , item.FirstName , item.MiddleName, item.BirthDate.HasValue ? item.BirthDate.Value.ToString("s"): "Unknown" );

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

And here is our output

Finally let’s take a look at the addresses associated with them. Here is our code:

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 ) {

        //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" );

        //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 );
        }

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

}

And here is our output:

Come back tomorrow when I take a look at inheritance.