Welcome to EMC Consulting Blogs Sign in | Join | Help

Simon Evans' Blog

My blog covers the technology areas I focus on here at EMC Consulting, namely Architecture using the .NET Framework, ASP.NET, WCF, WCF Data Services and Windows Azure Follow me on twitter @simonevans

Choosing An Appropriate Technology For Accessing Data In .NET Solutions

Introduction

The data access wars are over; long live the ORM wars. The new battles are fought with even more fanatical zealous behaviour than in previous conflicts. Rather than just debating the pros and cons of how best to execute SQL and wire it into our objects, there is now a whole new dimension to developer’s polarized opinions; what is the best ORM to use, if you believe you should use one at all that is.

The objective of any ORM is to bridge the object-relational impedance mismatch that occurs between a normalized database model (optimized for data storage, consistency and performance), and an object model (optimized for writing maintainable domain logic). Without an ORM, a developer needs to bridge this mismatch by writing tedious code that is hard to test and maintain. Thus ORMs should improve productivity, and the trade off is one of loss of control over the exact SQL executed against your database, and the performance overhead of running the ORMs code.

What makes the ORM wars so intensely fought is that there is little or no objective comparisons of the available options, and many people do not have equal experience in all the options either, meaning they find it hard to come to an objective view. This post attempts to provide an objective guide to choosing a technology to access data and return the data in the desired entity model of the developers choice.

This post compares the most common set of options facing a .NET developer at the present time, although other ORMs are available. The options considered here are:

  • Writing traditional ADO.NET data access code and manually mapping the data to your object model.
  • Using LINQ to SQL to surface data and LINQ to Objects to map this data to your object model.
  • Using NHibernate as an ORM to access data and map this information to your object model.
  • Using ADO.NET Entity Framework as an ORM to access data and map this information to your object model.

Assessment Criteria

In order to provide an objective assessment of each option, we need to have specific criteria which to measure. The criteria used in this post are as follows:

  • Productivity and Maintainability
  • Control over SQL
  • Operational Support and Maturity
  • Mapping Capabilities
  • Database Vendor Support
  • Modelling Approach
  • Coherence with the rest of the .NET Framework
  • Testability
  • Futures

Each technology choice is measured out of ten for each area of assessment. No overall “score” is given to each technology option, because that is missing the point of this work; really it depends on the design goals of your project as to what the best fit is.

Productivity and Maintainability

The business objective of an ORM is to provide a more productive method of populating an object model with data than traditional data access code, which is prone to human error (due to repetition). This section looks at how productive the development phase is for each option, looking at tooling, engineering practices and maintenance after release.

ADO.NET Data Access Code

A typical traditional data access layer in a .NET application will use ADO.NET Connection, Command, Parameter and DataReader objects to execute stored procedures (containing SQL) against a database connection. Parameters are passed into a stored procedure via a Command object, and after the procedure is executed, data is read by using a DataReader, or reading the output Parameter objects of the command. Normally, a developer will map the contents of a DataReader object to their own Entity object in code.

From a productivity perspective, the creation of Command and Parameter objects to execute against a stored procedure is prone to human error during development, because of the monotony of the task, and the lack of any compile time safety beyond the syntax of the ADO.NET code. The work of mapping a DataReader to an Entity is also tedious, although the advent of C# 3.0 and LINQ to Objects has made this task less onerous.

From a maintainability perspective, such a data access pattern does have one benefit over ORM approaches; SQL encapsulated in stored procedures can be more easily patched in a production system without a redeployment of the application.

Score: 2 /10

LINQ to SQL

LINQ to SQL provides code generation of data access objects using a command line tool called sqlmetal, which is integrated into Visual Studio 2008. It auto generates objects which represent to table structure of the database and are accessed using an auto generated DataContext. This provides a very quick start up time of developers, and the visual tooling is Visual Studio is very good.

A common misconception with the data objects that LINQ to SQL generates is that they replace the entities in your traditional architecture. They do not, because LINQ to SQL is not a fully fledged ORM. You will not be able to model the generated data objects as you would like using the designer. The best approach to using LINQ to SQL is to create your entities yourself and map the data objects to your custom entities using LINQ to Objects. This means that there is still be an amount of tedious mapping code to maintain, but the hand crafted SQL stored procedures have gone completely and you do have compile time safety over your data objects and mapping code.

The biggest issue that LINQ to SQL has both for development and maintenance is what happens when you make changes to the data model during development. Whilst none of the options here have a magic answer to this problem, but really the only answer LINQ to SQL has is to regenerate the entire data model. This becomes very onerous if you have customized the auto generated data objects in some say (like changed the names of properties or data objects using the designer). You can circumvent this weakness by not changing what is generated, but that does beg the question of the point of the designer in the first place.

Score: 6 / 10

NHibernate

NHibernate enables you to map your own entities to the database model using XML mapping files. Normally one mapping file is created per entitiy. Each mapping file contains meta data about the database model the entity maps to and how this data is mapped to the entity. Execution of these maps to populate objects with data from a database is handled using a Session object, which manages connections to database and generation of SQL against the database.

Like LINQ to SQL, the fact that NHibernate generates the SQL to execute against the database removes one productivity issue. However, each mapping file must be maintained by hand, and tooling support is thin on the ground, not really getting any better than XML intellisense. This additional control does not suffer from LINQ to SQL’s code regeneration issues (because nothing is generated), but is does beg the question “haven’t I just solved one maintenance problem but created a new one?”. Additionally, querying a repository using the session is achieved using a loosely typed syntax (string based), which is prone to error unlike a LINQ implementation like LINQ to SQL or LINQ to Entities (entity framework). LINQ support is however under development by the open source community.

Post production, any changes to the underlying database schema will require the deployment of the NHibernate mapping file (usually embedded within the data access layer assembly).

Score: 4 / 10

ADO.NET Entity Framework

The ADO.NET Entity Framework is an ORM that maps the structural model of a database schema to a conceptual (object) model. The framework uses the Entity Data Model (EDM) to achieve this. The EDM is broken down into three sections: the structural model (SSDL), the conceptual model (CSDL) and the mapping model (MSL), which are stored as XML normally embedded within an assembly. The entity framework is similar to NHibernate in that ORM mappings are stored as XML, but differs in the separation the EDM forces between the three models.

Like LINQ to SQL, the ADO.NET Entity Framework provides code generation for its EDM via a command line tool called EdmGen, which has integrated designer support in Visual Studio 2008 SP1. Because this design time support is based on changing the conceptual model of the EDM, the entity framework does not suffer from the same shortcomings as LINQ to SQL, in that it is a fully fledged ORM that will can (on the whole) produce the desired object model from the designer.

Better still is the tooling for the entity framework. This is where it plays an ace card; compile time support for the EDM comes for free, and what a free lunch it is. The entity framework refuses to build if there is an issue with the EDM that will cause it to fail under any condition. By this I mean any issue. So, the EDM will fail to build if there is an issue with your database schema, or if your conceptual model will not work under all CRUD conditions. Surely, this is the real problem an ORM was supposed to fix, and of these options, the entity framework is the only option that really helps you.

However, the ADO.NET Entity Framework still suffers from difficulties when in comes to changing the database model post production release. Depending on the nature of these changes will depend on how much of the EDM needs to be reworked; if the change is an addition to the structural model, it is possible to use EdmGen to regenerate the SSDL while keeping the conceptual model (you will need to do some extra mapping).

Score: 9 / 10

Control over SQL

If database performance matters (I mean it really matters to your scenario), then you may need to strictly control the SQL that is executed against your database. For these scenarios, ADO.NET Data Code cannot be beaten, because you can do anything you like. Most of the time however, all three of the other options will suffice. All the other options also provide support for stored procedures should it be necessary to execute some SQL that the technology cannot generate.

ADO.NET Data Access Code Score: 10 / 10

All The Rest Score: 6 / 10

Operational Support and Maturity

Its all too easy for developers to focus on a solution from a purely development perspective, but the reality is a truly useful application will remain in production far longer than its development phase. In many organizations the development team are not responsible for keeping an application running, largely because this is not cost effective. Hence, a support team is normally responsible for keeping the application running, and their skill set differs from that of the development team, in that they have less application specific knowledge, but more platform specific knowledge.

Operational support teams are largely concerned with procedures to get a system back online should an application fail. When these procedures do not suffice, the team will escalate the issue to the development team and ultimately the vendor’s support team.

For many businesses, this is a critical reason why they have chosen Microsoft technologies over other vendors, or open source offerings; operational support like the fact that they can fall back on Microsoft support should they need to. Having said that, NHibernate has a very active open source community, and if all else fails, you have the source code which you can fix.

Another important angle to operational support is maturity; the wisdom goes that the more mature a technology is, the less likely it is to have bugs. Of the technologies compared here, the most mature is ADO.NET Data Access code, then NHibernate, then LINQ to SQL, and finally ADO.NET Entity Framework.

ADO.NET Data Access Code Score: 10 / 10

LINQ to SQL Score: 8 / 10

NHibernate Score : 6 /10

ADO.NET Entity Framework Score : 6 / 10

Mapping Capabilities

The mapping capabilities of an ORM can prove a crucial deciding factor when choosing an ORM to use for data access. After all, the whole point of an ORM is to bridge the object relational impedance mismatch between your desired object model and the best relational database model.

Of the options considered here, only two can be considered as ORMs in the true sense (NHibernate and ADO.NET Entity Framework). Hence only these two can be measured in this area.

Firstly, both ORMs deal with associations between objects perfectly. In other words, both options model one to one, one to many and many to many associations without issue.

NHibernate provides more options for modelling inheritance than ADO.NET Entity Framework. The entity framework provides support of table based inheritance. This means that each sub classed object maps to a separate table, each row representing the sub-classed object. NHibernate also supports table based inheritance, but it additionally provides other options for modelling inheritance.

NHibernate Score : 10 / 10

ADO.NET Entity Framework Score : 8 / 10 

Database Vendor Support

Many businesses have a chosen database vendor, and thus choosing a data access option will require that you can easily utilize the chosen database vendor. Common databases include SQL Server, Oracle, DB2 and MySQL.

ADO.NET Data Access Code

Out of the box ADO.NET has support for both SQL Server and Oracle, and other third party providers can be purchased for support for most common database vendors. This is the most supported method of accessing data from .NET code, an in some more exotic database it may be the only viable option.

Score : 10 /10

LINQ to SQL

As the name suggests, LINQ to SQL is for use only with SQL Server. If this is your database, LINQ to SQL is not a problem. If you are using anything else, LINQ to SQL is not an option.

Score : 1 /10

NHibernate

NHibernate has support for SQL Server and Oracle out of the box, and it is possible to roll your own support of other database vendors.

Score : 8 /10

ADO.NET Entity Framework

Out of the box the ADO.NET entity framework only has support for SQL Server, but unlike LINQ to SQL, other database vendors are not ruled out, as the architecture supports using a custom data access provider. Custom providers can either be built or purchased, and third party support is growing by the day for support of databases such as Oracle and MySQL. For example, DevArt have providers that support multiple third party databases here. There are also samples available from Microsoft on rolling your own here.

Score : 6 /10

Modelling Approach

There are two initial approaches to designing a solution with a database and an object model: either design the database model first, or the object model first (although clearly these two activities should never be executed entirely in isolation). There is no right or wrong order to these tasks, but each of the technology options provide varying support of both approaches.

ADO.NET Data Access Code

This option does not stop you from approaching a solution’s design from either standpoint, but then neither does it really provide any support for either option. Here, you can do what you like, including doing some very bad practices indeed. The only modelling you will have is the class designer and your database vendor tools.

Score : 3 /10

LINQ to SQL

LINQ to SQL only really models the data layer, which means you are free to do the object and database modelling either way round. But like the straight data access option it’s not really giving you much value here.

Score : 3 /10

NHibernate

NHibernate enables you to model your database and entities in either order, and for many in the object model first camp, this option feels like the most natural choice of the four here. But again, modelling support only comes in the form of not hindering you, rather than helping you

Score : 6 /10

ADO.NET Entity Framework

If there is one area that will rule the entity framework out for many developers, this is it. In reality, the entity framework currently only supports database model first development. If you can work this way, the modelling support is superb, but this does limit how the entity framework can be employed, particularly if you do not have control of an existing database’s model; this part of the entity framework is very unforgiving, yet also most giving.

Score : 3 /10

Coherence with the rest of the .NET Framework

One often overlooked point when considering a data access option is how coherent the technology is with the rest of the .NET Framework. By coherent I mean that the API feels like a natural extension to the rest of the framework. This is important, because it reduces the learning curve for developers new to the technology, and can also help future proof it with advances in the core framework. It was for all these reasons that I have always disliked many aspects of the Enterprise Library; because it felt unnatural to program against, and at its worst hid many core aspects of framework development.

In these respects, NHibernate is at a distinct disadvantage in that all the other options are the .NET framework. From a developers perspective its API shares no coherence with either the underlying ADO.NET paradigm, or the IQueryable (LINQ) paradigm. This makes learning NHibernate a uphill task for .NET developers skilled in either of these areas.

Of all the options, ADO.NET Entity Framework scores best, because it supports both the ADO.NET paradigm and the IQueryable paradigm, meaning that developers who have invested time in these areas can adopt the entity framework more easily. Another important area to consider here is the support ADO.NET Entity Framework has from other parts of the .NET Framework. Again the ADO.NET Entity Framework wins here because it has a lot of support from both the ADO.NET Data Services team and the ASP.NET Dynamic Data team. Once you have an entity model, you can create a data service or scaffold a website fairly easily, giving you significant quick wins (LINQ to SQL does not have this level of support).

ADO.NET Data Access Code Score : 5 / 10

LINQ to SQL Score : 6 / 10 

NHibernate Score : 1 / 10

ADO.NET Entity Framework Score : 9 / 10 

Testability

Making software easily testable by creating unit tests is an important engineering practice in ensuring quality in your solution. More specifically, the ability to test the interactions between the layers of your architecture is as important to testing for results of executing code in a specific layer of your application.

ADO.NET Data Access Code and NHibernate

Both of these options enable you to create unit tests and mock layers of the architecture, because you are complete control over all the code you test. This is because both options support the use of POCO classes for the creation of business objects and entities. Additionally, both options provide interfaces for you to mock out enabling you to test interactions.

Score : 10 /10

LINQ to SQL and ADO.NET Entity Framework

Both of these options generate out classes for you to work with that represent your database (for LINQ to SQL) or your entities (Entity Framework). These objects are not POCO objects, which forces a dependency of framework assemblies in all parts of your application that consume these objects. For ADO.NET Entity Framework, this problem is more serious, because entities proliferate around your architecture more than data layer objects. Additionally, these generated objects do not expose separate interfaces, meaning that you cannot use mocking practices against these objects to test interactions easily. The counter argument here is that you do not need to test these interactions, because the code is generated anyway.

Score : 1 /10

Futures

No one wants to invest money or time in a technology that has no future, or will be quickly superseded. It is a key factor for development teams who consider that their application will evolve through several releases over a period of time.

ADO.NET Data Access Code

You can be sure that your investment here will not be superseded, but neither will it be greatly improved upon in future. Its mature, but it is also feature light.

Score : 3 /10

LINQ to SQL

Microsoft are not developing LINQ to SQL features, because it is now considered to be superseded by the entity framework. Of all the options here, this one has the most bleak future (although Microsoft will obviously continue to support the technology).

Score : 1 /10

NHibernate

NHibernate continues to have a active ALT.NET community, and features continue to evolve. LINQ support is due soon, which will level the playing field with the Entity Framework in terms of support for ADO.NET Data Services and ASP.NET Dynamic Data. The only doubt here is how a maturing entity framework will affect NHibernate support in the future.

Score : 7 /10

ADO.NET Entity Framework

Many of the entity framework’s short comings are promised to be fixed in the .NET 4 timeframe. In particular, it will (apparently) support POCO objects, object model first design (including database generation) and better testability. What is for certain is that Microsoft are backing this horse, which should not be overlooked.

Score : 10 /10

Summary

Wow… that was a long blog post! But in short:

You might choose to use ADO.NET Data Access Code when:

  • You really really need to worry about performance
  • You’ve got a database not supported by any other means
  • You have not need for an entity model in your application

You might choose to use LINQ to SQL when:

  • You want to create a prototype quickly
  • You haven’t got .NET 3.5 SP1 installed so you can’t use the Entity Framework
  • You only ever want to work with SQL Server

You might choose to NHibernate when:

  • You like modelling an object model before the database model
  • You don’t have control over the database model
  • Operational support have no issues with using a third party framework
  • You want good testability
  • The project is sufficiently complex that you would benefit from the investment creating maps manually
  • You are not concerned with the lack of ADO.NET Data Services or ASP.NET Dynamic Data Support

You might choose to use the ADO.NET Entity Framework when:

  • You have control over the database model and are happy modelling this first
  • You want compile time safety over your entire ORM
  • You want productivity without compromising your architecture (very much!)
  • You want ADO.NET Data Services or ASP.NET Dynamic Data support
  • You trust the generated non-POCO entities
  • You want the most future proof option
Published Tuesday, February 24, 2009 5:59 AM by simon.evans

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

rajiv mathew said:

Thats a nice compilation and a side by side review of each......

really effective and Agile....!!!!

February 25, 2009 1:33 PM
 

Neil Blackburn said:

An excellent article.  Very helpful when you haven't used all of the choices.

Roll on Entity Framework v2!

February 25, 2009 3:26 PM
 

Hugo said:

Hello,

I was introduced to the "orms world" by a person how told me that "found the perfect orm was like found the treasure in the end of the rainbow" so I decide to investigate the orms I found a lote of problems with all orms that I try. Same of the orms meets same features that I want but not found a orm that meet all the features I want so I decide to play around and start to develop my own orm with the objective to create a new orm simple to use and at same time meet all my requirements and so that is. Now is in the version 1.4 with hundreds of downloads and a very small community registered in the orm forum (7 people include me :)).

One of the bigger feauture challenge requested by several users is to have many-to-many relation with to-way directorion that is almost done for the next 1.5 version.

If someone wants to try can download it now from the official site http://entityorm.uuuq.com

There is also de documentation in pdf format and technical documentation and also a sample video.

June 26, 2009 3:44 PM
 

Buu Nguyen said:

Good analysis.  One minor thing: LINQ-2-SQL does support POCO.

September 2, 2009 2:53 PM
 

High School Homework Help said:

I really appreciate your professional approach. These are pieces of very useful information that will be of great use for me in future.

January 21, 2010 11:39 AM
 

KMan said:

Great analysis.

February 17, 2010 7:24 AM
 

Andy B said:

Hi.

I have used all of these choices except for Hybernate.

1. Entity framework seems to be the best choice for most projects especially the medium to largely complex one. I took a database that had over 700 tables, 400 views, 200 ustom data types, 700 stored procedures and udfs and as well over 70MB in size and turned it into an EDMS in about 2 minutes flat. I had to make some modeling changes in the generated code, but that didn't take very long. I like the idea of being able to extend the EntityContext object without bothering the auto generated code. Just remember to make your custom mods in new files or you will lose them when the model regenerates itself. What normally would have taken me 50 lines of ado.net access code took me only 5 with entity framework. I ran into a few modeling problms with queries. Mainly the ones that use the containes functions or the like keyword. I was able to fix this by turning the data into a string. first.

2. LINQ was just a royal pain, was too bulky, didn't let you change anything at all and had coding limits. LINQ to object/xml is good though becuse by the time you get your data from the database into an object, you have mastered the limitations and don't need to worry about it anymore.

3. ado.net code is good for certain things. I use it for very light weight functionality like creating an RSS feed out of database data. In this case, carrying around something as huge and bulkey as EDMS or LINQ is overboard.

February 22, 2010 10:50 AM
 

Ajit Singh said:

Great article. Its about a year old. Would love to see the upgraded score in view of the entity framework in .NET 4.0

March 7, 2010 3:52 AM
 

Yachtcharter Griechenland said:

Good post, but have you thought about Welcome to EMC Consulting Blogs before?

April 20, 2010 1:31 PM
 

options trading said:

This is the good step u have taken. Thanks for writing this, its clear you have spent a good amount of time on your sites development.

August 26, 2010 10:19 AM
 

Devart said:

As for the third-party providers, Devart presents not only ADO.NET providers with Entity Framework support. We provide a new product - LinqConnect (http://www.devart.com/linqconnect/) that supports various database vendors (Oracle, MySQL, PostgreSQL, SQLite) and provides enhanced LINQ to SQL functionality.

December 10, 2010 8:51 AM
 

Shimmy said:

I can't wait to read your updated comparison on NHibernate vs. EF 4 (and 5 which in CTP ver)!

Thanks

December 27, 2010 11:18 PM
 

joca said:

Would be cool if you put the current version of those.

February 1, 2012 11:15 AM
 

indian designer sarees said:

"There are useful information and most importantly, for sharing great.

June 19, 2012 8:29 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About simon.evans

Simon is a Managing Consultant for Conchango in the UK, part of EMC Consulting Services. He is an expert in .NET development, and more specifically in WCF and ASP.NET, having participated in several Microsoft early adoption programs. Simon believes deeply that a broad understanding of key technology concepts is an essential foundation to being a gifted designer and builder of solutions.
Powered by Community Server (Personal Edition), by Telligent Systems