.NET Object Relational Mappers-ORMs

A look at .NET ORMs
A look at .NET ORMs

Home
›
Articles
›
A look at .NET ORMs

Intro
This article looks at the many different ORM (object relational mappers) that exist for C#/.NET. These Object Relational Mappers aim to bridge the gap that exists between database servers and their native SQL, and the object-oriented world of non-tabular data.

My main source for the ORMs I chose to look at were from csharp source and wikipedia along with a codeplex search, and just general knowledge of the products out there. I've excluded dead links from wikipedia, commercial ORMs (of which lots also exist) and narrowed it down the ones that seemed fairly mature and easy to assess from available walkthroughs.

I've been using ORMs since around 2005 in both proprietary and open source format, but right now I use NHibernate and ActiveRecord in my daily job. Having used this for almost a year I was curious what the competition was like 3 years on and being indoctrinated into the Active Record pattern, which once you start using makes others seem clumsy. I wouldn't be switching anytime soon as NH/AR are my job (and as I found out I wouldn't want to) but I did manage to test Subsonic and the Entiy Framework quite extensively with the Soot project I've been making.

Anyway, my criteria for assessing and ORM was:

- "Turnkey" abilities of it, or how easy it is to just dive in
- The documentation, support (through forums, FAQ, knowledge bases)
- The date of the last release, to indicate how fresh/stale the project is
- Ease of use and flexibility of the API
- Caching and performance
- Others: inheritence, complex relationships, concurrency

The 2 most important features, in my view, of an ORM though are:

- Querying - dynamic filtering, quality of SQL produced, joining, paging, eager/lazy loading
- Save/Create and unique id handling and ease of use of CRUD operations

I'm not going to waste the article waxing about how these make an ORM useful as there's plenty of sites out there that describe the concepts of ORMs, on codeproject and blogs. So first of all I'll introduce you to one of my favourite non-ORM, ORMs.

db4o
www.db4o.com/

db4o is an object database, and not strictly an ORM. Its intention is be an embedded database along the lines of SQL Lite. Unlike SQL Lite, it contains its own API for CRUD operations and querying, rather than an ADO.NET driver. I thought I'd include here anyway as I've had good experiences using it in the past.

db4o comes in both Java and .NET form much like Hibernate (except the .NET version has equal love to the Java one). It's released under the GPL and if you're not releasing your project as GPL'd, you need to buy a commercial license. It's particularly good for personal projects or projects with small amounts of data as it's very fast to develop with and stores itself, like SQL Lite, in a .db file.

The Pros
- As it's commercial, it's updated a lot - Has query by example, basic and advanced querying - Easy to backup and move databases about with just a .db file - No need for config files with connection strings - Testing is simplified as everything is in memory, no need for dummy data scripts - No worrying about object states, concurrency as its handled by the db4o database engine. - Version 7 supports LINQ to its own query engine, and using LINQ on the compact framework. - Good tutorial with lots of examples - Includes an object viewer to view your saved objects with.

The Cons
- Doesn't scale well with heavy objects above 1m records in my experience (using version 5.x). This may have changed since then. Of course d4bo can counter for this by saying it's intended for embedded use and not as a replacement for an enterprise RDBMS.
- Slow to query large sets when the objects are 'heavy'.
- Support takes a while on the forum
- The tutorial is an .exe and not a webpage

I mention it's slow with large amounts of data, especially textual data as I tried to write an ASP.NET forum using it, but came to a dead end when it came to querying strings and working with large post data. It is quicker with 30,000 objects than mySQL/Hibernate though, as their benchmark shows: www.db4o.com/about/productinformation/benchmarks/ which is probably a lot more than the amount of objects I'd imagine most people choosing an ORM would have.

Mini example
Linq to Entities / Entity framework
MSDN entity framework page

LINQ to Entities and the Entity framework is Microsoft's attempt (a word which most people seem to accredit it) at building an ORM into the .NET framework. It was released with 3.5 SP1 under the System.Data.Entities namespace, and has tools built into Visual Studio 2008 SP1 to work with it.

It works but defining all your entities and their relationships in an XML file. You then specify this XML file in your connection string, and change your provider to the entities provider. You then have to create your own classes than implement various classes. All saving and loading is done via one facade class. Visual Studio 2008 SP1 produces these classes for you automatically in a "designer.cs" file that it makes by looking at your databaes schema. You can then rename the properties and relationships in a UML-esque designer.

The Pros
- Microsoft are behind it, so it's guaranteed to have an active team and updates and not just be a pet project occasionally looked at.
- Produces very clean and efficient SQL without tweaking
- Allows you to use LINQ on objects to produce the SQL
- Lazy loading is enabled by default, to load collections you have to use Load()
- Uses the recognisable conventions of ADO.NET, a connection string to indicate where the entity definition XML file is.
- Easy to implement caching system

The Cons
- Very patchy documentation, except 5 walkthrough videos. Most information is found through the ADO.NET blog (see the links at the bottom of the page for a selection of links)
- The class hierarchy makes it hard to manually edit the classes, as so many events and attributes are required by your implementing class.
- Visual Studio designer doesn't produce meaningful property names
- The whole XML entity format is the usual overly verbose Microsoft style. 400 lines for 4 database tables of 4-5 columns each.
- The session class is inflexible and you end having to place business code everywhere. You can't update outside the original scope. This is a restriction of version 1 however.

Mini example

// The Person class is a lot bigger than this after the Entity Framework Designer has got its grubby paws on it
// I've included this shorter version just so you can see what a Person class is
public class Person
{
	public Guid Id { get;set; }
	public string Name { get;set; }
	public string Email { get;set; }
}

public class PersonManager
{
	public static void Example()
	{
		// Requires app.config/web.config connection string settings

// This 'EntityManager' is created for you by the designer
		using (EntityManager manager = new EntityManager())
		{	
			// Save example
			Person person = new Person();
			person.Id = Guid.NewGuid();
			person.Name = "John";
			person.Email = "john@aol.com";
			manager.SaveChanges(person);

// Getting everything
			IList<Person> list = manager.PersonItemSet.ToList();
		
			// Paging example, use Include() to include parent/child objects in the query.
			// The include statement indicts to EF to join on the tables, and not lazy load.
			int page = 1;
			int itemsPerPage = 10;
			var x = entities.PersonItemSet.Include("Address").Include("ParentCompany.Address").Where<Person>(p => p.Name == "John").OrderBy(i => i.Name).Skip(page).Take(itemsPerPage);

// The benefit of the EF is the above linq produces a joined query any dba would be proud of
		}
	}
}

SOODA
sooda.sourceforge.net/

I couldn't get the tool to work in Sooda, it just froze each time I used it (which could be a Visual Studio 2008 issue), so I can only list the features of it

- 8 developers and a stable release
- Started in 2003, last update (v.1) August 2007.
- The mailing list seems to have died since 2007, and the forums
- Works like NHibernate: an XML file to describe your entities and their relationships.
- Supports SQL Server, mySQL, Postgres, Oracle 8
- Comes with an installer.
- Has a good amount of documentation - virtually a whole book's worth - on a single web page
- Includes a tool to reverse engineer a database.
- Supports before and after update/insert events for objects and fields.
- Contains NHibernate-esque Level 1 and Level 2 cache systems.
- Has its own SQL like query language SOOQL, plus a converter class to convert to SQL and back.

Mini example

Persistor.NET Express
www.persistor.net/

Persistor is a commercial ORM but features a community edition like many commercial software tools do now. I tried it out but didn't bother writing any CRUD code with it, or querying as its free version was really too restrictive to be any use to anyone except the complete ASP.NET beginner doing their first website.

The Pros
- Works with zero configuration with SQL Express, defines and creates the schema for you
- As with db4o, this makes automated testing a lot easier
- Good documentation
- As it's commercial it has regular updates

The Cons
- (Free edition) SQL Server Express only
- Uses a very strange way of storing objects - doesn't just map tables to classes and columns to properties, but seems to use SQL in a non-relational way.
- Doesn't use joins for its schema - relationships are added as columns on the table, e.g. Employee.Person.Name would be stored as a column called Employee.Person.Name.
- Doesn't work with existing databases
- Doesn't support nullable types
- (Free edition) no lazy loading or retrieving objects by id.
- Not as flexible as other ORMs
- Forum is a ghost town
- Seems to store POCOs (plain old CLR objects) definitions in the database too.

Mini example
Paladin
www.codeplex.com/paladin

I've used Paladin previously for some mid-sized projects. It works in the way the Entity Framework works, by having a generator create your entity classes for you, it differs though as it uses the Active Record pattern approach, where you save on the domain objects themselves, e.g Person person = new Person(); person.Save();

The codeplex project doesn't link to the forums but they can be found here: community.sgdotnet.org/forums/50.aspx and the documentation at community.sgdotnet.org/forums/t/3332.aspx.

The framework was available from this site before it reached codeplex in 2007. It seems to be a dead project now so it wouldn't be advisable for anything except to play around with, and pros and cons seem a bit useless as it doesn't compete with the other ORMs really, I just thought I'd give it a mention.

NPersist
www.codeplex.com/PuzzleNPersist
www.puzzleframework.com

NPersist is one of the contenders for NHibernate/ActiveRecord in my view, and has a lot of features that sell an ORM to me. The main of these is querying via linq, comprehensive documentation and being able to specify relationships in attributes or XML. Unfortunately the forum seems to be a bit dead since February 2008 and all the examples are locked inside a doc file instead of on the website. This can make finding details via search engines difficult.

All operations in NPersist work via a session, or context (facade class). Unlike the Entity framework though, this doesn't appear to lose its scope but does mean you end up passing the context around outside the "business layer" if you need to perform tasks in other worker classes (for example, if you had namespace that performed all crawling of websites in a search engine).

The Pros
- Supports XML entity definition files, and also attributes
- Thorough documentation - 200 page manual
- Impressive feature list
- Has its own query language NPath, just like SQL and also linq.
- Includes validation
- All new objects are created via a factory class, making object identity confusion less messy.

The Cons
- Only 3 contributors (though this isn't really a small amount)
- Last updated in February and quiet forums
- No web-based help, it's all inside a large Word document.
- All new objects are created via a factory, making "offline" creation hard, or meaning the context has to be passed around.
- As with the Entity Framework, NPersist's context model means all the object belong to the context they were created in, although it's not as restrictive (i.e. doesn't have the one context only bug) as the EF one.

Mini example

[ClassMap(Table="People")]
public class Person
{
	[PropertyMap(Columns="EmployeeId", IsIdentity=true, IsAssignedBySource=true)]
	public virtual Guid Id { get;set; }

[PropertyMap(Columns="Name")]
	public virtual string Name { get;set; }

[PropertyMap(Columns="Email")]
	public virtual string Email { get;set; }
}

// These are required in your assemblyinfo
[assembly: DomainMap(Source="HelloWorldSource")]
[assembly: SourceMap(Compute=true, Name="HelloWorldSource")]

public class PersonManager
{
	public static void Example()
	{
		using (IContext context = GetContext())
		{
        		// app.config/web.config settings would be used in GetContext()
			Person person = (Person) context.CreateObject(typeof(Person));
			person.Id = Guid.NewGuid(); // This might not be needed
			person.Name = "John";
			person.Email = "john@aol.com";
			context.Commit();
		
			Person person = (Person) context.GetObjectById(Guid.NewGuid()); // this would be a proper guid
			IList<Person> list = context.GetObjects(typeof(Employee));
		
			// Filtered loading, basically SQL
			NPathQuery query = new NPathQuery("Select * from Person Where Name=?", typeof(Person));
			query.Parameters.Add(new QueryParameter(DbType.AnsiString, "John"));
			list = context.GetObjectsByNPath(query);
		}
	}

private IContext GetContext()
	{
		IContext context = new Context(this.GetType().Assembly);
		context.SetConnectionString("server=(local);uid=xxx;pwd=xxx;database=yourdb;");
		return context;
	}
}

Subsonic
www.codeplex.com/subsonic

Subsonic is a kind of amalgamation of the Entity framework and ActiveRecord with no custom querying language. The whole package is very slick and friendly to use. I was put off by the generator and reams of code that it produced, even though it was very quick to get going. It has the clever facility for working with the web 2.0 style web apps (app_data folder style) via its own build provider, though this style of project isn't something I use and have never liked the app_data etc. model.

The Pros
- Uses the Active Record pattern
- 9 Contributors
- Good documentation howtos and active forums
- Last update July 2008
- Well marketed so there's plenty of guides for getting started and performing tasks
- Bundled with a useful winforms app for editing data with.
- Intuitive and fast to use
- Querying is performed using method names for SQL actions - .InnerJoin() and so on.

The Cons
- Relies on the generator to produce your entity classes, doing it manually is like the Entity framework - lots of work is involved.
- "Subsonic <3", "All your database belong to us" - The Conservative world of enterprise middle tier just isn't ready for the netspeak!
- I had problems with the SQL parser (Ansi92 generator), it suffered from null reference exceptions doing a 3 table joins. But being open source atleast I could find the bug

Mini example

// This class is autogenerated for you by SubSonic, so it's a lot larger than this.
public class Person
{
	public Guid Id { get;set; }
	public string Name { get;set; }
	public string Email { get;set; }
}

public class PersonManager
{
	public static void Example()
	{
		// Requires app.config/web.config settings (a SubSonicService node)
		int page = 1;
		int itemsPerPage = 10;

// Save example
		Person person = new Person();
		person.Id = Guid.NewGuid();
		person.Name = "John";
		person.Email = "john@aol.com";
		person.Save();

// Number of records
		int count = new Select().From(Person.Schema).GetRecordCount();

// All items
		IList<Person> list = Select().From(Person.Schema).OrderAsc("Name").ExecuteTypedList<Person>();

// Paging
		SubSonic.SqlQuery q = new Select().From(Person.Schema).InnerJoin(Address.Schema);
		// You can append predicates like below
		q = q.Where(Address.StreetColumn).IsEqualTo("Long Road");

list = q.Paged(page,itemsPerPage).ExecuteTypedList<FeedItem>();
	}
}

ODX
www.codeplex.com/odx/

This project looks like a one-man ORM. It uses ActiveRecord's attribute idea to mark classes as tables and properties as columns. You have to also inherit your classes from a base Entity class to denote that they should be persisted.

It supports SQL Server, Oracle and Access. Querying is done via a form of SQL where the parameters are specified with the usual "?" holders. Lazy loading is the default behaviour for it on collections, and operations are done via a session/context facade class.

The Pros
- PDF overview to get started quickly
- Attributes on classes and properties makes mapping to tables fast (although some might say this polutes the Domain Model, if you like your domain models skinny and non-functional).
- Supports "offline" editing of objects, then going back online and it synchronising
- Has logging and caching inbuilt
- Has 13 sample projects that come with it
- Has some nice features for saving state at certain points, going back online.
- Intuitive API in the session object for retrieving/saving.

The Cons
- Only one contributor
- Initiation makes you pass ADO.NET objects around with DbFactorys.
- The only documentation is in CHM format
- Last updated November 2007, but 1.6 release.
- A few unanswered messages on codeplex, and a lack of any forum or community

NHibernate / ActiveRecord
www.nhforge.org/
www.castleproject.org

Lastly is Active Record, an open source project that sits ontop of NHibernate using the Active Record pattern. Before I describe Active Record I'll go into some brief detail about NHibernate. As I mentioned above I use the two in my job so this section will be longer than the rest.

NHibernate is the .NET port of the Java Hibernate project. The Hibernate project itself is quite mature (currently version 3) and has a large community surrounding it, plus several books in print. So it's obviously trusted by people to as an ORM.

NHibernate isn't a carbon copy of the Hibernate project, but rather a project run by one Hibernate contributor full time, and a couple of others, one of which now works at Microsoft. As it is taken from Java, it contains some esoteric java-esque ways of doing things that can maybe befuddle people aren't familiar Java, mostly in the interface and hierarchy of the classes. This is all nomenclature however. NHibernate receives a fair amount of downloads on Sourceforge where it's hosted, around 500+ downloads per day. It has 22 people involved with the project.

NHibernate uses XML to define its entity to table mappings and relationships. These XML files are embedded as resources into your binary, which NHibernate scans, and provides mappings to the classes you have specified in the XML. All operations are performed by Session object (like a facade class) for CRUD activities. The session has various different modes of operation to ensure you have concurrency and non dirty read/writes.

For querying you have 3 options: criteria API, query by example (which uses the criteria api), and HQL which is an object oriented version of SQL. In HQL you can use something like SELECT p from Person p WHERE p.Address.PostCode='SW1'.

Castle ActiveRecord sits as a layer on top of NHibernate, and provides several new features. The main features is you no longer have to define your class to table relationships in an XML file. Instead you simple add attributes to the classes and properties of the class to indicate which table or column they map to. So on a class you would add [ActiveRecord("MyTable")] and on one of its properties, [Property("MyColumn")]. ActiveRecord then reads these attributes once at start up, and creates the NHibernate XML mappings for you.

ActiveRecord also obviously implements the Active Record pattern. This allows you to subclass the ActiveRecord class by your data classes, which then in turn gives you a set of methods relating to CRUD activities. For example you could now perform user.Save(), user.Delete() on your class.

Other features it adds include automatic session management, a set of 'helper' methods for querying, and also a way of using a façade class (ActiveRecordMediator) to perform CRUD actions without using the activerecord pattern. The Pros and conns listed below are for ActiveRecord rather than NHibernate.

The Pros
- Fast to get going and intuitive to use
- Provides both the ActiveRecord model, and the facade or dao model via a ActiveRecordMediator class
- Has a lot of web-based documentation on the Castleproject website
- Lots of forum activity
- Access to NHibernate's toolbox when you need it, and has its own SimpleQuery,HQLBasedQuery as helpers.
- Provides validation
- Ability to tie into save,update etc. events
- Unlike other ORMS (though this is mostly a feature of NHibernate) - no messy domain model source code with 10s of event handlers and attributes - all base classes are cleanly done
- Simplifies NHibernate session management with ASP.NET

The Cons
- Slow initial load up time (for debugging)
- For complex scenarios, it can often be slower to produce a result than simply writing raw SQL
- Hasn't been updated for NHibernate 2.0, making it a year old
- Doesn't work with NHibernate's 2nd level SysCache as it's compiled with a different version of NHibernate. To get it working you have to get the SysCache project and recompile it
- Scarce MSDN style documentation that doesn't have many solid examples
- Examples normally end up searching the forums or blog posts.
- yourobject.FindAll() doesn't perform any eager fetch/join strategy by default, producing huge amounts of SQL and also doesn't make use of 2nd level caching
- Confusion can arise with how the relationships are mapped onto their NHibernate versions
- Lazy loading requires every property and method in the class to be virtual - interfaces aren't supported I got this wrong, ActiveRecord does support interfaces, so map the entity to an interface, mark the implementing class as Lazy=True and it functions the same way as marking properties as virtual.
- Primary key issues can occur with custom ids
- The main developer is now at Microsoft, so Castle is no longer used day to day for his income

Mini example
I've been deliberately critical above but this is mostly because I use it day to day. Overall from evaluating the ORMs above I've realised that Active Record and NHibernate are in my view the strongest allies in the .NET ORM world right now, even with Active Record's foibles. One of the biggest of these 'foibles' is the fact no published material is available for NHibernate yet, and finding how to do things is normally a case of going through the original Hibernate documentation and forums as well as the NHibernate ones.

That's about it.

Links
csharp-source.net/open-source/persistence
en.wikipedia.org/wiki/List_of_object-elational_mapping_software

Additional resources
Entity Framework
FAQ
Howto videos
Channel 9 article
Creating your own Cache
Performance
Performance comparison
EDM types
Dynamic linq queries (for combining AND/OR or predicates in queries)

NHibernate
Learn NHibernate
Quick start guide
Resources/tools for NHibernate
Codeproject articles about NHibernate
HQL guide