Object Relational Mapping

We have spent most of this chapter covering the “standard” process for creating .NET applications that read and write data that resides in a database. Although Visual Studio and the ADO.NET libraries themselves do a lot to abstract away the difficult pieces of that process, problems still remain. In fact, there is one common problem that developers writing database-driven applications face: the mismatch between an application’s normal object-oriented programming model, implemented in C# or Visual Basic, and the relational programming model surfaced in databases or data sets, implemented primarily with SQL.

In the object-oriented world, we manipulate objects via methods and properties, and each object can be (and often is) a parent or container for other objects. The relational database world is much more straightforward: Entities are implemented as row/column-based tables, and each “cell” in a table holds simple scalar values. The core issue is that you must change programming models when dealing with an application’s internal framework or the relational database used as its data store (and translating from one to the other isn’t a straightforward task).

As a simple example, rows from an invoice table are easily fetched into a DataSet object using the various data-binding tools and classes discussed previously. But deriving a “native” Invoice object from the data set involves two-way manual translation and manipulation to get the core values to translate across this object/relational barrier. This highlights several issues. Do you abandon the data set approach and read directly from the database into your applications’ objects? Do you eschew the object approach and try to use the DataSet component throughout all layers of your application? Or is a hybrid approach best, maintaining strongly typed object collections in addition to the data sets?

Ideally, application developers would be free to work with and manipulate objects within the program’s object model and have those objects and changes automatically stored in the database with little or no intervention. Not only does this keep the focus on core well-understood object design patterns, but it lets the individual developer work with his core language strength without having to learn or become expert in SQL. Pursuit of this goal obviously requires some sort of standard approach and tooling support for mapping objects to and from their equivalents within the relational database. This is exactly what object/relational mapping tools do.

The term object/relational mapping (or O/R mapping) refers to this general process of translating objects to and from databases. O/R mapping tools have been on the market for years now, and Microsoft has finally delivered O/R mapping support directly in the .NET Framework and in Visual Studio through two different but similar technologies: LINQ to SQL and Entity Framework. Let’s briefly discuss both of these technologies and their Visual Studio tooling, starting with LINQ to SQL.