When I’m learning new technologies and methods in software development, I like to read a bit about it, go over a few articles on MSDN or scan some blogs, and perhaps watch a video or two. Then I jump in to see what it’s all about. When the .NET 2.0 Framework was released with support for Table Adapters to handle data access, I was excited about that. I thought that here there was, at last, an easy way for a developer to handle all the database “stuff” that an application needed. I thought this was cool and there would never be a reason to go back.

Fast forward a few months and the project with the Table Adapters had grown to be unwieldy and difficult to navigate. While I thought I was using the Table Adapters correctly, I was putting more work into making them do what I wanted them to do, rather than just having them do what they were intended to. I started looking for a better way and started to investigate the topic of Object-Relational Mapping.

Object-relational mapping or O/RM is a technique which creates a virtual database you can use from inside of your application, where, instead of tables of “things”, there are collections of “objects”.  And an object can be a Person, a Car, a Record Album, an Invoice, whatever it is your application is working with. For example, a Record Album has a collection, or list, of tracks. And, for the most part, only has one Artist or Band. On the flip side, an Artist or Band has a list of Record Albums they have recorded. At its heart, what an O/RM does is to map database tables, and its relations, into a set of objects, or classes. This  allows you to think in the context of your application, instead of how to make the data fit correctly into the tables.

There are many free and commercial O/RM tools available, and I dabbled around with several of them. I attended the Southern California Code Camp in January of 2009, and watched a presentation on building a web site using Microsoft’s Entity Framework, and was very impressed at how easy it was to work with. At the time, it seemed that all I had to do was drag and drop a few things around, write a few lines of code to interact with my objects, and I was good to go. At the time, I didn’t have a current or upcoming project in which I could use the Entity Framework, so I put it on the back shelf, thinking that, when I had the opportunity, I would start getting into it, put it through its paces, and see what it was really all about.

Flash forward about 10 months and I started a new project. This project was, and still is, a very extensive, enterprise level, document generation application, where, it seems, that every table has several  relationships. In thinking about the design of the application I considered several different ways to interact with the database: Then I remembered that presentation on Entity Framework, and decided to give it a try.

One of the coolest things I get out of presenting and writing is the two-way flow of information that goes between me and my readers or meeting attendees. I probably learn more from them, than they do from me, and I plan for these articles to do just the same. If you read something I’ve written that just doesn’t make sense, please let me know. If something here is just like oh, so totally wrong, then please let me know as well, telling me where I’m wrong and I’ll make the correction and post it in following articles.

What follows, and what this series of articles will be on, are my trials and tribulations of getting up to speed on working with the Entity Framework from a developer’s point of view, someone who jumps in headfirst, steps on the occasional landmine, but for the most part has an enjoyable journey. Let’s go.

Relationships First

“Can we talk? Listen, it’s not you, it’s me.” Yeah, relationships are work.

The first thing you need to do when you’re setting up a database to use with the Entity Framework, is to establish the relationships. You must do this before cracking open Visual Studio and the Entity Framework Modeler. I’m sure most of you reading this are saying, “But I do that already”. Remember who is writing this, however; someone who jumps in without looking, and never really spent the time trying to grok what all these relationship and foreign key things were for. For me, it was easier to write the SQL to get all the records from the associated tables where the record Ids matched up. Ok, so it was more work for me, but then I knew exactly what was going on.

EF doesn’t like it if the relationships aren’t enforced with constraints: Nor do any of the other O/RM tools. Build your relationships first; it’s really easy using the Diagram Modeler in SQL Server Management Studio. Once you get the knack of thinking  how the different tables interact with each other, it makes it even easier to understand your application. Besides, it’s such a cool thing, when you see how it’s really supposed to work, and you think to yourself,”Self, you could’ve been doing this all along.”

Creating a new Entity Data Model

“Tall and skinny? Chiseled abs? Every day or Ultra Glamorous?” It’s your app, and it’s all up to you.

So, let’s build a Model. Open up Visual Studio and create a project. It can be a Web Forms app, an MVC app, a Console App, Desktop App, or whatever you would like. EF is friendly that way and plays with everyone. For the rest of this series we will be using the Chinook database. The Chinook database is an alternative to everyone’s favorite Northwind, being smaller with fewer tables and can be run on a variety of database servers. You can grab your copy at http://chinookdatabase.codeplex.com.

To add a new Entity Data Model, you should open your Solution Explorer and right-click on the folder you want the files to be. In my case I am building an MVC app, so I will right-click on the Models folder. Some MVC apps will reference an entirely different project for their Model, but for this series we will just be keeping everything together.

1. Right click on Models
2. Click on Add, New Item
3. Click on Data, then ADO.NET Entity Data Model
4. Give your beautiful model a meaningful name, something similar to the name of your application is good

This brings up the Entity Data Model Wizard. Select Generate from database then click Next. On the next step, define your connection.  The last text box on this wizard step is important because this is where you name the connection string. The name that you give to the connection string is also the name you use to refer to the Entities in the Model within your code, so choose wisely. For this article I am naming my connection settings, ChinookEntities.

Click Next, and you will be shown  a list of the tables that are contained in the database. You can now include them  in your Entity Data Model. I will select all the tables in the Chinook database. The last step is to name the Model Namespace. Typically selecting the default – a system generated combination of the name of the database and “Model” – is fine. I’ll live it at “ChinookModel“. Click Finish.

Once you click Finish, the required Assemblies are added to your project. These include System.Data.Entity, System.Runtime.Serialization, and, System.Security, and the Entity Model Designer is then opened, displaying the tables and relations from the database on the designer surface.

I’m at one with my Entities

When a plurality becomes single and vice versa, or did my singularity just swallow the universe?

Going back to the whole O/RM thing, each database table that you decided to include into the Entity Model is now considered to be an “Entity”. It is no longer a collection of stuff; it represents one, and only one item, object, or thing-a-majig – a row in a table. Because of this, each of the entities on the model designer are now considered to be singular, and their names will reflect this. If, for instance, a table in your database was named “Albums”, in the Entity Model, it would be named “Album”. When the Entity Model is being created, the Entity Framework will take care of this for you; singularizing any plural object names.

Navigation Properties

“Yes dear, you do need to stop for directions.”

So now that we know that an Entity is a singular instance of a row in a table, we now have to go back and think again in plurals when it comes to some entities. Looking at the diagram below, isolate the “Track” and “Album” entities. In the lower part of the Entity object,  find the Navigation Properties. These represent the foreign keys associated with other tables, and you can see that Album and Track are associated.

Now, an Album can have several Tracks, while there can be only one Track on an Album. Later on when you select an Album and want to get a list of Tracks on that album, you will write in your code something like List<Track> tracks = Album.Tracks; . If you think about it in common grammar, you want all the Tracks in an Album. So how do we set this up?

This is where Navigation Properties come in. In the Album entity object, select Track in the Navigation Property section. Either select “Track” and start typing, or change the name in the Properties window. You will want to change the singular Track to plural Tracks. Continue along this process of renaming the Navigation Properties of each entity so as  to match the plurality of the related tables. Chapter becomes Chapters. Person becomes Persons (or People). SuperModel becomes SuperModels. You get the idea. Now, keep in mind, this is not required, but is a way to help you make sense of all of this.

In the Entity Diagram Modeler you can see the Associations between the entities. These were created for you by the Entity Framework when examining the relationships you created in the database before you started all of this. The model diagram shows the types of Associations; One to Many, Zero or One to Many, and Many to Many.

Changing the multiplicity of the association is easy.  In the Properties window change the value in the Multiplicity drop-down list to one of the other selections we just talked about.

Entity Keys

The song doesn’t necessarily remain the same. Ah, more renaming. When will it end?

The primary key defined in the table maps to what is called the Entity Key of the entity object. As you can see here, the Entity Key is named AlbumId. This is fine if you want to keep it this way. However, in later articles I will be talking about Repositories and how to make interacting with the Entities easier. And you know it’s all about making our jobs and life in general easier, so for now take my word for it and rename all the Entity Keys to Id.

Trust me, it will still work. And the Mapping Details for the Album Entity/Table proves it.

You can also rename the other Properties of the each entity to make it easier to think about. For instance the Chinook Invoice table contains BillingAddress, BillingCity, BillingState, etc. While Intellisense will help you while typing your code, if you want to make the property names even more concise, feel free to. BillingAddress becomes Address, BillingCity becomes City, BillingState becomes State, on down the line. It’s all a matter of preference as to which line of code feels better to you, Invoice.BillingAddress or Invoice.Address

What’s next you may ask?

Please sir, may I have some more?

Hopefully this introduction has whetted your appetite to continue on this quest with me. Stay tuned for upcoming articles where I will cover Contexts in “Keeping your contexts intact”, Repositories in “How a repository can supports you”, LINQ to EF in “Please give me my data”, and more advanced,  self- embodying topics of the Entity Framework.