Introduction

ORM, or object-relational mapping, is one of the tougher things to accomplish in modern, object-oriented programming languages. It involves moving away from the traditional data store paradigm: there is no (or very little) dedicated, pre-compiled code involved in reading/writing an object to/from the database or other backing store. Instead, the logic involved in accessing the backing store is determined at runtime using a combination of reflection and attributes that decorate the business objects in question. Many projects and frameworks have been created to try to address this concept, with varying degrees of success. What this article covers is a general introduction to ORM concepts, the approach that .NET 3.5 takes, and how it compares to these other packages.

In the beginning...

Prior to .NET 3.5, you had several choices when it came to getting your business objects to and from the database:

1. Roll your own – This means you don't use any frameworks and don't auto-generate any code. The database schema and the .NET classes are created by hand, as is the data access layer. While this will provide the ultimate level of customizability and performance, it's tedious (involves copying a lot of boilerplate code), error prone, and difficult to maintain when the objects or the database schemas change.
2. Auto-generate the classes and the data access layer – This is where code generation tools like CodeSmith or MyGeneration come in: you point them at your database and it will generate the .NET classes and the data access layer. Like option 1, this isn't true ORM: you still have pre-compiled code responsible for accessing the database to read or write an object's data. However, its automatic generation of the code is a step in the right direction, removing the error-prone human factor when creating the classes and the data access layer.
3. Use a true ORM framework – There are several well-known ORM packages available for previous versions of the .NET framework, including NHibernate and Gentle.NET. As mentioned previously, ORM removes the dedicated data store code and inspects an object at runtime to determine what it needs to do to read/write it to/from the database. Attributes are used to decorate the class and its properties to give the framework pointers about where things go in the database. The actual SQL for an operation is generated dynamically based on these attributes. There is often a code-generation component in these packages that generates the .NET business object classes from the database schema, but no dedicated data access code is generated.

Major Malfunctions with ORM

So all this dynamic, runtime SQL generation stuff sounds great, right? Not so fast: ORM has several serious drawbacks. The first of these is performance, as you're going to encounter a slowdown any time you bring reflection into the equation and start dynamically generating SQL. ORM will never be as fast as rolling your own: there's no substitute to being able to hand-tweak your stored procedures and pre-compile all of the data access logic. Another drawback is that ORM doesn't deal well with extremely complex databases. When designing complex databases with a lot of constraints and relationships spanning several tables, it's often necessary to include intermediary tables to link various entities together that is great from a RDBMS standpoint, but doesn't translate all that well to an object-oriented environment. This can lead to obtuse and difficult to understand auto-generated classes. Keep in mind that RDBMS and object-oriented environments are fundamentally different, and each includes its own set of design and performance considerations. What works in one environment is not necessarily optimal for the other environment. That being said, the upside to ORM in terms of maintainable, clean, and easy to understand code can be quite compelling, provided that it's used correctly.

LINQ, DataContexts, and general ORM in .NET 3.5

So, now that you have a good idea of what ORM is all about and its potential pitfalls, let's delve into how Microsoft approached this concept in .NET 3.5. It takes a different approach to the challenge of ORM by not focusing on slaving the object model to a relational model, but by instead giving us an entirely new way to access and query our data that's not limited only to relational data. With this approach, the ORM capabilities of .NET 3.5 evolve almost as a side-effect instead of being the prime focus of this new data access scheme. How do they do it? LINQ. It stands for Language INtegrated Query and Microsoft wants it to be THE way that you sift through data in the .NET framework. Its structure will be immediately familiar to anyone with experience writing SQL statements and it marries the simple yet powerful query syntax of SQL to the strong typing of an object-oriented language. The real kicker, however, is that it's not limited to relational data: anything that implements the IEnumerable and IQueryable interfaces can be used with LINQ. Here's a quick example so that you can get an idea of what it's capable of:

It's just a simple search of a generic string list instance for elements that contain the letter "n". Whereas before you would have had to accomplish this imperatively, that is, you would have had to write code to iterate over the collection and drive the search, you can now accomplish the same thing declaratively. Basically, you're stating what you want to do instead of how to do it. While the syntax takes some getting used to, this approach is inherently less error-prone. It also bears mentioning that Intellisense is in full effect in the above sample, so you lose none of the "ease of use" features of Visual Studio when you employ LINQ.

So let's take a look at how LINQ fits in with ORM. First, there is a code generation component: there's really no getting around this since you have to define the classes that map the relational data in the database to an analog in the object-oriented world. To do this, Microsoft provides a tool called SQLMetal. To use it, just open up a Visual Studio command prompt (Start->Programs->Visual Studio 9.0->Visual Studio Tools->Visual Studio Codename Orcas Command Prompt) and type in sqlmetal to take a look at its options. It's pretty simple: all you do is point it at a database and tell it the names of the code and/or DBML files that you want it to generate. I created a sample database called LinqTest that contained two tables: Person and PersonAddress. PersonAddress had a column called personId that was a foreign key to its equivalent column in the Person table. So, to generate the ORM classes, I just ran:

The code that results from this process contains two sets of classes: a DataContext class and a series of object classes corresponding to tables in the database. The object classes are simple: they're mirrors to the database tables, with a property representing each column. Each class and its properties are decorated with attributes describing where it fits into the database, which is similar to any other ORM framework. A nice enhancement that I haven't seen anywhere else is that each of these object classes automatically implements the INotifyPropertyChanging and the INotifyPropertyChanged, meaning that they expose events that allow external classes to respond when the value of a property changes. To facilitate this, the meat of the set portion of each property is wrapped in OnPropertyChanging() and OnPropertyChanged() calls which, in turn, fire off the event handling functions if they are set. Another nice feature is the fact that all of the classes generated by SQLMetal are partial classes that implement only the necessary core functionality. This means that you are free to add on to the class via separate code files without having to update the auto generated output. The DataContext class serves as your link to the backing store (in this case, the database) containing the data that will be used to populate the object classes. You pass a connection string or an instantiated database connection object to its constructor and, in turn, it exposes a series of generic Table<> properties, each representing a table in the database. These Table<> properties are what really give LINQ its power: the Table<> class is fully LINQ-enabled and is responsible for acting as the interface between LINQ and the database, translating the LINQ syntax into the necessary SQL code to retrieve your data from the database. In the same vein as our earlier example, let's take a look at another LINQ query that uses these Table<> properties:

After all this talk about how difficult ORM is to implement, seeing it in action like this in .NET 3.5 is almost anti-climatic: it's easy to understand if you have SQL experience, it's fully integrated into the language and the development environment, and it's surprisingly fast. Want to add a new Person to the database? No problem:

Just call the Add() method of the necessary Table<> property and, when you're done, call the SubmitChanges() method in the DataContext class.

Wrapping it up

I hope this gives you a good idea of the great things you can do with ORM in .NET 3.5. It's not a silver-bullet to solve all data access challenges, but as any good developer knows, there never is. When a database schema is complex and performance is paramount, there is no substitute for hand-coding and tuning the .NET data access code as well as the SQL logic. But, for straightforward schemas and general filtering of data in .NET, LINQ represents a new and intriguing implementation of ORM.

About Luke Stratman

Sorry, no bio is available This author has published 2 articles on DotNetSlackers. View other articles or the complete profile here.