Entity Framework: A Robot Arm For Database Applications, January 12, 2013

In his book "The Passionate Programmer", Chad Fowler suggests that a programmer should "Automate himself into a job". He says that developers would be well-served to write tools that automate common programming tasks. In "The Pragmatic Programmer", Dave Thomas says "Write code that writes code".

I've been surprised by some of the automation tools that are already out there, free for the taking.

A few years ago, someone told me about Microsoft's Entity Framework. Microsoft always seems to have more frameworks and libraries out there than I can keep track of, and I knew nothing about this one.

So what is this Entity Framework? It is an "object relational mapping" (ORM) tool that works with Microsoft .net. What that means is that it is a system for mapping a relational database into .net code.

Up until Entity Framework, I had to write a lot of code to query and update my SQL databases.

The process is very tedious and error-prone. When requirements change, all that code has to be carefully reviewed and updated to match the new requirements.

With Entity Framework, the programming to do these tasks is almost entirely automated. I don't even have to write the code anymore to open and close the SQL connections.

The basic mechanics of it is that in Visual Studio 20XX, I can create a new "Entity Data Model" (*.edmx) file. Visual Studio will connect to my SQL database using a specified connection string and show me the list of objects that are accessible. I simply check the box next to each one that my program needs access to. As a bonus, it will also pick up on foreign key references. I usually refer to the tables directly, but Entity Framework also wraps stored procedure calls with ordinary .net function calls. They couldn't possibly have made it any more convenient!

Once I click thru the tool in Visual Studio to create my Entity Data Model, it is extremely easy to program with. There is a single object I instantiate to access all of the SQL objects in the model. All of the tables and views are properties of that object. Once again, they couldn't have made it any easier. These objects can be queried using the LINQ methods. I could write extensively about exactly how to do this, but I find it very intuitive to use.

The bad part is that sometimes, it isn't easy is getting the Entity Data Model set up just so. It sounds straight-forward, but often times, it will seem like the framework needs your database to be set up a little bit differently than you're used to. For example, I once worked with a database where the DBA refused to set primary key constraints on the tables; Entity Framework produced all kinds of error messages. The DBA insisted that the solution was to do everything with stored procedures! This worked fine, as Entity Framework makes it very easy to call stored procedures. It took me a while to get the hang of how to write applications with Entity Framework, but once I got used to it, I quickly came to regard it as indispensable.

When it comes to writing code to automate a programming task, check and see if the code has already been written! If my work were like a factory, Entity Framework would be one of my robot arms.

Today, I am reading Programming Entity Framework: Code First, by Julia Lerman. This book explains a new feature in Entity Framework that Microsoft calls "Code First". With this new feature, not only can Entity Framework query your database for you, but it can create it as well with a table structure that matches specified classes in your .net code.