Published: 11 Aug 2010
By: Dino Esposito

A comparison on multiple database support in the Entity Framework and NHibernate O/RMs.

Contents [hide]

The NHibernate Comparison of Entity Framework series

  • Part 1 A comparison on multiple database support in the Entity Framework and NHibernate O/RMs.
  • Part 2 Last month I took the challenge of trying to compare Entity Framework (EF4) and NHibernate (NH) in a hopefully unbiased and feature-driven way. In this article, I'll start looking into some programming features such as lazy loading.
  • Part 3 This article is about fetch plans-a recognized and common way for developers to instruct the O/RM about the structure of the SQL you desire.
  • Part 4 A Feature-driven Comparison of Entity Framework and NHibernate-2nd Level Caching
  • Part 5 A Feature-driven Comparison of Entity Framework and NHibernate - Self-tracking entities
  • Part 6 A Feature-driven Comparison of Entity Framework and NHibernate - Queries
  • Introduction

    In his masterpiece "Patterns of Enterprise Application Architecture", Martin Fowler couldn't make it clearer and simpler. He says that if any decisions made around a software project are easy to change later then they are related to the implementation of the system. If, instead, changing the decision later does have a deep impact on the project (costs, regression, large and long refactoring sessions) then the feature behind the decision is definitely part of the architecture. And, subsequently, it deserves much more attention.

    Nowadays, in enterprise applications (but not necessarily large applications) a common architectural decision is about the O/RM to employ. Oh sure, I'm taking for granted here that you already made another key decision - whether to use an O/RM framework versus a pure SQL approach. Changing the technologies behind the data access layer may be a big deal. In our book "Microsoft .NET: Architecting Applications for the Enterprise", Andrea Saltarello and I briefly hint at a painful experience we went through a couple of years ago when our team was forced to switch from NHibernate to "any other" O/RM available on a strict customer's change of request.

    When it comes to O/RM frameworks, you find a number of options to choose from - both open-source and vendor supplied products. Often, however, the debate reduces to considering just two O/RM frameworks--NHibernate and Entity Framework. In this limited scope of this article (and some that will likely follow), I'll take the challenge of comparing Entity Framework 4 (EF4) with NHibernate 2.1 (NH) from a few angles. In this first article, I'll focus on support for multiple databases.

    While I'll try to keep the discussion as unbiased as humanly possible, it is likely that I'll miss just the perspective or the angle that is key for some of you and some particular business scenarios as well. So I encourage you to take this article as a mere contribution rather than an absolute piece of truth.

    Support for Multiple DBMS

    Most O/RM tools make a point of being database independent. This behavior is commonly obtained through an extensibility model exposed via a public and documented API that the same product owner, or third-party vendors, can leverage to add support for specific databases. Both EF4 and NH offer a (small) number of native drivers to connect to a few specific databases and leave third-party companies the burden of creating and selling others. The net result is that each database vendor can support EF4 and NH by implementing a tailor-made driver according to the specifications of the O/RM.

    The number of actual drivers available to developers may be different for each O/RMs and also the quality of the driver may vary, even significantly, case by case. Put another way, the fact that you can get or buy a driver for a given database is not per se a guarantee about the quality and effectiveness of that piece of code. Let's get into more details for EF4.

    EF4 provides out-of-the-box support for SQL Server 2005 and newer versions. This means that support for other DBMS is still possible but only through third party drivers. Vendors exist to support efficiently Oracle, MySQL, DB2, Progress, Sybase and a few others. (See Table 1 and for more details pay a visit to http://www.microsoft.com/sqlserver/2008/en/us/ado-net-entity.aspx.)

    Table 1: Where you get drivers for connecting EF4 to non SQL Server databases.

    Vendor

    DBMS supported

    DataDirect Technologies

    Oracle, Sybase, Microsoft SQL Server and DB2

    Devart (formerly Core Lab)

    Oracle, MySQL, SQLite, PostgreSQL

    OpenLink

    Oracle, DB2, IBM Informix, Progress (7.x to 10.x), MySQL, PostgreSQL

    IBM

    DB2, Informix

    Sybase

    SQL Anywhere

    EF4 allows you to create your own driver (actually referred to as a "provider") through the same public API that vendors in the preceding table used to create their own ones. In addition, EF4 allows you to write "wrapping providers" namely a provider that wraps an existing provider and adds additional services (i.e. auditing, logging, caching etc).

    On the other hand, NHibernate provides out-of-the-box support for several DBMS including IBM DB2, Microsoft SQL Server 7 and beyond, Oracle, Sybase, PostgreSQL and many others as documented at http://nhforge.org/doc/nh/en/index.html#configuration-optional-dialects. You can further extend NHibernate SQL capabilities by writing a driver for the particular SQL dialect used by a given DBMS. An example is available at http://www.codeplex.com/NHibernateVistaDB.

    Multi-database Applications

    Sometimes the same application is required to support different databases in different configurations according to the user's needs. The same application, for example, installed for client A may provide its services on top of a SQL Server database; installed, instead, for client B might be required to work on top of an Oracle database. This is precisely one of the reasons that lead companies to using O/RM tools. Without the out-of-the-box abstraction provided by an O/RM, you ought to write that abstraction yourself in your own data access layer. At the end of the day, your handmade data access layer would dangerously look alike a handmade O/RM!

    Overall, both EF4 and NHibernate support multi-database scenarios, but with some differences.

    When using EF4, you typically design your entity model through the Visual Studio integrated tooling and have the designer to generate any related classes for you. The EF4 designer helps you taking care of the definition of the conceptual model, the storage model and related mappings and saves all information to an EDMX file. The content of the file is then processed during the build process. In this regard, EF4 needs to know about the physical schema of the database already at compile time. This means that playing with EF4 the default way you may need to recompile the class library with the EF4 model in order to switch to another database.

    However, some workarounds exist that enable you to move the storage model to a separate XML file to be deployed as a distinct part of the application. Let's see how to proceed.

    You open the EDMX file in a text editor and extract the SSDL section of it to a separate file. Next, you change the Build Action of the EDMX file in the Visual Studio project to Content. (The default value is EntityDeploy.) Configured this way, entity classes will still be compiled into a class library when you build the project with the EDMX file. When it comes to deployment, you must install the library with entity classes along with the distinct file with the storage model description. Figure 1 takes it further and shows the setup folder of a sample console application where CSDL, SSDL and MSL files have been taken out and deployed individually. (Normally, this information is embedded as a resource in the EF4 class library.)

    Figure 1: Deploying an EF4 class library with separated and replaceable SSDL information.

    Deploying an EF4 class library with separated and replaceable SSDL information.

    To force EF4 to read at run time information about the storage model from the distinct file you need to change the connection string. Here's the default content of the metadata token in the EF4 connection string when meta information is embedded as an assembly resource.

    As you can see, the metadata token contains three concatenated URLs in the form of res://*/resource. The resource placeholder indicates the name of the assembly resource. By simply changing these URLs (and contextually ensuring that the proper information is available) you decouple your EF4 application from the details of the storage and can replace it without touching the EF4 project. Here's how you can change the metadata token in the connection string:

    In this case, you are instructing the EF4 runtime to read class definitions, storage definitions and mappings from the specified files located in the same folder as the executable. All other tokens in the connection string, such as provider and provider connection string, remain intact. Of course, you can decide to keep the CSDL in the assembly and take out only the SSDL or arrange any other combination you like.

    Should you need to map the same class model to another database model, all you need to do is editing the SSDL file without any further compile step. Is this all good? Well, not exactly. This approach is not free of issues; for example, by taking out content from the EDMX file you may lose the ability to use the EF4 designer to update the model. How can you edit the SSDL file for a different database schema? The SSDL file is XML ultimately; so you can use a text editor or create an ad hoc EF4 project for the sole purpose of creating the schema using the integrated Visual Studio designer.

    In a way, things are simpler with NHibernate.

    NHibernate as a framework doesn't have any tooling and requires you write all models by hand editing XML files. This means that to deploy an application and make it use a different database all you need to do is changing the content of mapping and configuration files. Toolkits such as FluentNH or ConfORM also allow generating the mapping files writing C# code or code in other CLR-enabled languages which provide lambda functions. This technique is gaining momentum. In particular, FluentNH is by far the most complete toolkit available.

    Summary

    I presented how EF4 and NH approach development for multiple databases and multi-database applications. Is there any key conclusion we could draw? All in all, there's no significant difference between the two frameworks if you just count things you can or cannot do. However, a sort of different philosophy emerges to characterize differently the two frameworks. EF4 tends to offer extra tools to make things simpler and to lower the learning curve as much as reasonably possible. NH, instead, keeps things close to their natural level of complexity. The net effect is that beyond the default configuration and most common usage, using EF4 is kind of harder and sounds you're desperately looking for a workaround. It's mostly a psychological point than technical, but I firmly believe it contributes to influencing what people thinks about EF4 and NH.

    The NHibernate Comparison of Entity Framework series

  • Part 1 A comparison on multiple database support in the Entity Framework and NHibernate O/RMs.
  • Part 2 Last month I took the challenge of trying to compare Entity Framework (EF4) and NHibernate (NH) in a hopefully unbiased and feature-driven way. In this article, I'll start looking into some programming features such as lazy loading.
  • Part 3 This article is about fetch plans-a recognized and common way for developers to instruct the O/RM about the structure of the SQL you desire.
  • Part 4 A Feature-driven Comparison of Entity Framework and NHibernate-2nd Level Caching
  • Part 5 A Feature-driven Comparison of Entity Framework and NHibernate - Self-tracking entities
  • Part 6 A Feature-driven Comparison of Entity Framework and NHibernate - Queries
  • <<  Previous Article Continue reading and see our next or previous articles Next Article >>

    About Dino Esposito

    Dino Esposito is one of the world's authorities on Web technology and software architecture. Dino published an array of books, most of which are considered state-of-the-art in their respective areas. His most recent books are “Microsoft ® .NET: Architecting Applications for the Enterprise” and “...

    This author has published 54 articles on DotNetSlackers. View other articles or the complete profile here.

    Other articles in this category


    A Feature-driven Comparison of Entity Framework and NHibernate-2nd Level Caching
    Where would you place caching in your layered solution? Two main patterns exist for caching in appli...
    A Feature-driven Comparison of Entity Framework and NHibernate - Queries
    Let's explore what Entity Framework and NHibernate has to offer when it comes to their query capabil...
    A Feature-driven Comparison of Entity Framework and NHibernate—Fetch Plans
    This article is about fetch plans-a recognized and common way for developers to instruct the O/RM ab...
    A Feature-driven Comparison of Entity Framework and NHibernate - Self-tracking entities
    In this article, Dino Esposito introduces self-tracking entities.
    .NET type generation for NHibernate mapping collections
    An overview of .NET type generation for NHibernate mapping collections.

    You might also be interested in the following related blog posts


    NHibernate : Some Naked Thoughts read more
    Talking Points: ADO.NET Entity Framework read more
    Sneak Peek at the EntityDataSource Control read more
    Inheritance and the Entity Framework read more
    How can I Install Membership, Roles and Profile on my Hosted Site? read more
    Exploring EntityKeys, Web Services and Serialization a little further read more
    DLinq: Mapping a Database to a DataContext class. read more
    DLinq: Playing with knives. read more
    Get The Most Out Of Your Format String read more
    Top
     
     
     

    Please login to rate or to leave a comment.