Introduction to Entity Framework: Part I - Object Relational Mapping

Table of Contents

• Part II - Designing the Database
• Part III - Building the Model
• Part IV - IQueryable

Now that I've completed my Introduction to LINQ tutorial I want to move on to another technology that takes Language Integrated Queries to a whole new level. Entity Framework (EF) is what is known as an Object Relational Mapper (ORM). ORM is simply the concept of processing data from a relational database and modeling it in a format that is more readily usable by your code. Instead of looping through rows in a table you model each row as an object and loop through a collection of objects, called entities.

The Old Way

The simplest way to explain ORM is to first see how we would access data without it. Since this blog is .NET-centric I will show you how to access a database in C# the old fashioned way, using plain old ADO.NET. Here are the steps you would need in order to connect to a SQL server database:

1. First you need a SqlConnection object.

SqlConnection connection = new SqlConnection(connectionString);

If you've never seen a connection string before, it's just a string containing all the authentication data necessary to connect to the desired database. Here is a sample of what a connection string looks like.

1. Once you have a connection, you need a SqlCommand object.

SqlCommand command = new SqlCommand(@"INSERT INTO [CodeTunnel].[Users]
                                 VALUES (@Username, @Password, @Administrator)", connection);

1. Now we have to pass in the parameters by adding them to the Parameters collection on the SqlCommand.

command.Parameters.Add(new SqlParameter("Username", "Alex"));
command.Parameters.Add(new SqlParameter("Password", "Exp3rtC0d3r1"));
command.Parameters.Add(new SqlParameter("Administrator", true);

1. Then you have to open the connection to the underlying database.

connection.Open();

1. Execute the command.

int rowsAdded = command.ExecuteNonQuery();
Console.WriteLine("{0} rows were added.", rowsAdded);

1. Finally you have to close the connection to the database.

connection.Close();

While ADO.NET greatly simplifies a lot of the steps necessary to connect to a database and execute queries, there are still several problems. First, all six steps here are required for every query you want to execute. In fact, an extra step is required if the query returns any data you want to collect; you have to create a SqlDataAdapter object and use that to populate a DataTable object. This is a giant pain to do with every single query. Adding a single row to a table requires quite a bit of work.

Another issue with old fashioned ADO.NET objects is that you have to include your SQL query in a string. As far as the .NET compiler is concerned your query is just a string. You won't know if you had any errors in your query until you run the application and execute the query. Only then will you get a SQL exception telling you something was wrong. This is not fun to debug, especially in large applications where you may not use that query very often and you won't know it's broken until months later when a visitor ends up executing it and getting an ugly error you didn't anticipate.

Another inconvenience with this method is the dependency on the developer knowing the syntax of another language. While I think learning SQL syntax is something every good developer should do, it is definitely an inconvenience for someone who may not be a SQL guru and just wants to build a small application that does some basic reading, inserting, and updating.

The New Way

Today we have Object Relational Mappers. In .NET there are two different ORM products that do basically the same thing. The first one is LINQ to SQL (L2S). Second we have Entity Framework. L2S is the older of the two technologies. At their core they both accomplish the same goal, but L2S has quite a few more limitations than EF. The most obvious limitation is that L2S only works with a SQL Server database. Because EF is the more robust new kid on the block I'm going to focus this tutorial on that technology. Ultimately they are both just as easy to use and knowledge you gain while learning one can easily be translated to the other. Personally I feel that EF is the better technology and that L2S is slowly being phased out.

The ultimate goal of ORMs in .NET is to remove the need to execute string queries and give the developer the ability to write strongly-typed queries. If you're familiar with LINQ then you already know how to write strongly-typed queries against IEnumerable<T> collections. The benefit of writing a strongly-typed query is that the compiler can look at a LINQ query and tell you if you made a mistake. Of course now and then you'll still run into runtime errors, but the benefit of compiled queries is immense.

The ORM's job is to take your LINQ statements and translate them into SQL queries for you behind the scenes. This enables a developer to write complex queries against the database without ever having to write a single line of SQL. This does have the potential to allow SQL ignorant developers to remain ignorant but I think that is a small risk compared to the advantages it provides.

In the next parts of the tutorial I will go into detail about how to get going with Entity Framework but real quickly before I end Part I let's do a brief overview of the steps it would take to accomplish the same task that we did with plain ADO.NET. If you are unfamiliar with EF then this won't make tons of sense quite yet, but don't worry about it. Stay tuned for Parts II and III for more details.

1. Instantiate the data context object.

CodeTunnelEntities dataContext = new CodeTunnelEntities();
// EF will automatically read the connection string from your application configuration file :)

1. Create a new User object.

User newUser = new User
{
	Username = "Alex",
    Password = "Exp3rtC0d3r1",
    Administrator = true
};

1. Add the user to the data context.

dataContext.Users.AddObject(newUser);

1. Save changes to the data context in order to persist those changes to the database.

dataContext.SaveChanges();

That's it! Obviously there is a lot more details you're missing in order to get started with EF and set up your model, but we'll cover that in the next part. For now you can sort of see the simplicity that the ORM provides. Remember how I said retrieving data in plain old ADO.NET would require an extra step? Well here retrieving data would actually be less steps! Here is an example of pulling out some data through a LINQ query.

List<User> administrators = dataContext.Users.Where(x => x.Administrator);

Could that be any easier? I think not.

Part II - Designing the Database