Saturday, January 31, 2009

ADO.NET Entity Framework

Wow is it 2009 already? Sorry about the lack of posting, I've been swamped with project work for Ortho Clinical Diagnostics -- not to mention all of the turbulence surrounding the company's reorganization last fall. I'll try to do better this year.

Some of the team and I have been discussing the Microsoft's next generation data access technology, the ADO.NET Entity Framework, and someone suggested (demanded?) that I put this up on the jimblog. "Next generation" is a bit misleading however -- this has been out for nearly a year with the release of the .NET 3.5 framework. Our current .NET technology at TranSystems -- for the Modeling Studio, or for other software development -- is .NET 2.0, which was available with Visual Studio 2005. I'm not sure when we will transition, but the Entity Framework is a good reason to consider it.

The key concept behind the Entity Framework is to provide a conceptual language for you to code with, instead of writing low-level data access code. So let's take an example from the MSDN overview where you want to find all the pending orders for sales people in Washington. In traditional ADO.NET, you would write a SQL command that joins the tables together, return the results in a DataSet, and then iterate through the rows in the dataset. Something like:

sSQL = "SELECT * FROM tblSalesOrders _
INNER JOIN tblSalesPeople ON tblSalesOrders.SalesPersonID = tblSalesPeople.SalesPersonID _
WHERE tblSalesOrders.Status = 'Pending Stock Verification' _
AND tblSalesPeople.State
= WA"

cmd = New SQLCommand(sSQL, cnn) // you've already set up cnn somewhere

dsOrders = cmd.ExecuteReader

I'm positive I messed up the syntax, but you get the idea.

Note that implicitly you need to know some things about how the data is stored -- what tables have relationships to each other, on which fields, etc. And you may tell me, "This is usually not a big deal -- if you're writing the code, you're probably also the one who designed the database. And we're all in the habit of doing this anyway." And you would be correct.

But the cool thing about the Entity Framework is that all of this SQL stuff is just abstracted away. You are programming in the business logic itself. You don't need to know anything structural about the database. Rather, once you set it up, you can simply write (this is C# below):
 // find all the pending orders for sales people
// in Washington
var orders = from order in orderTracking.SalesOrders
where order.Status == "Pending Stock Verification" &&
order.SalesPerson.State == "WA"
select order;
Check it out. There's no joins. No SQL. You go from the Order object to a related SalesPerson object to get the State field. It's almost like the code was written in English! In case you don't see right away why this is so cool, here's how MSDN describes it:

There are two elements that are important to highlight in the code above:

  • No artificial constructs. It's common to see applications that need to adapt to peculiarities of the underlying store schema. For example, applications built on top of relational databases often have to make extensive use of joins in order to navigate through relationships. In the code above, in contrast, the "shape" of the data follows the abstractions of the problem being modeled; there are "orders", which have "order lines" and that are related to a "sales person".
  • No plumbing. The code is very database intensive, yet there are no database connection objects, no external language such as SQL for query formulation, no parameter binding, no configuration embedded in code. In this sense, you could say this code is "pure business logic".
Think about this last point -- the pseudocode (you do that, right?) that you'd write for a critical design review (you do those with your team sometimes, right?) becomes really really close to the actual code that's implemented. How much time do you think that would save you?

Anyway, there's a lot more in the full technical article. Also, here are some white papers that might be of interest. They're from a company called IdeaBlade, whose product DevForce helps boostrap construction of enterprise-level software solutions so you can get going quickly (but that's a subject for another day...)

Though I haven't tried Entity Framework yet, I've been monitoring the technology for a couple years, and I'd encourage you to put it on your radar screens as well. We have at least two pure software development projects right now (NASSCO and WholeWorks), and while it's probably too late in the game to affect those, it's always a good idea to keep your design options open for the future. At least we should know it exists!

Cheers,
-- Jim