The Sorry State of .NET ORMs

This article was going to be a follow-up for my previous article on Entity Framework Code First Simplicity.

What I was actually going to cover was how to take the code-first model that I introduced in the last post, and show you how to make it work with SQLite and Postgres, to give you some real choice in which kind of data store to use. Unfortunately, all that changed when I tried to write the code to support it.

What Went Wrong?

Well, quite a lot, really.

About three months ago, a well-respected DBA friend and I did a live webinar on using PostgreSQL on the Windows platform. This webinar included a segment on how to use PG inside an Entity Framework application. At the time, however, the EF6 provider for PG had more than a few problems, but the documentation told us that the PG and the SQLite teams where both busy updating their providers to work with EF6.

Why? Well, EF5 was the last closed source version of Entity Framework to be released by MS, from EF6 onwards everything is now Open Source, and as part of this new offering, the driver architecture model has drastically changed. This means that all third-party providers now need to have major re-writes to keep working correctly.

Given however, that I knew this and had let three months or so lapse since I last attempted to use the providers with EF, I figured now would be a good time to re-visit the topic and see where we stood. Unfortunately, I don’t in any way, shape, or form have very much that is good to say about the experience.

I was plagued by one exception after another, most of them seemingly nothing to do with my code (which was intentionally very simple, and the same code used in my last post). Trying to trace into these errors was a task in itself. As anyone who’s worked with EF will know, it can be insanely difficult trying to get the generated SQL back out.

Much to my surprise, however, the EF6 team have made this task much easier, and I was able to eventually work out (at least with the SQLite provider) that the generated SQL was making calls to nonexistent functions and, as a result, causing the transaction in which it was wrapped to abort.

At this point, being aware of the time I had allocated to write this article, and more so being aware that said time was fast running out, I decided to go and look for a simple ORM solution, similar to how EF works, as easy (code-wise) to get up and running as my EF sample in the previous post, and quick and easy enough that a complete beginner could pick it up, and be up to speed in an hour of less.

The Criteria

Before I started, I set myself certain criteria so that I was giving each ORM tested a fair run for its money. These criteria where all as follows:

  • Documentation must be readily available and/or easy to understand and pick up (assume no knowledge of any data technologies).
  • Library must be easily installable by NuGet.
  • No complex setup or trouble shooting should be needed to get things running (we’re aiming for: NuGet Install, a few tweaks to config, and a minimal amount of code).
  • Everything should be code first, allowing SQL and/or attribute driven use when/where needed.
  • Everything should be intuitive and similar to the code design used by the EF example in the last post, and should be easy to do in an IDisposable enclosure using a very EF/Linq-like syntax.
  • The library, if needed, should, like EF, be able to break out into complex SQL-driven scenarios if needed. Otherwise, it should be simple object manipulation all the way.
  • All MUST be freely available open source offerings with no limitations on use attached to them.

If you’re looking at this list and wondering what I was thinking, my approach was as follows:

Put myself in the shoes of a fairly mediocre junior programmer, who had an hour or so to quickly select an ORM that will get him up and running and adding/altering data in an SQLite database, using an object-orientated approach as quickly and as painlessly as possible.

Every test I did, I repeated the exact same two scenarios as used in the EF example. I had an “Entity” that consisted of an Integer ID, a String name, and a string email address.

I then created a database connection, and attempted to insert a new object, followed by attempting to re-open that database connection and modify the saved object. And, I have to say I was quite shocked at what I found.

Most (if not all) of the ORMs I looked at (I spent roughly a day and a half, and many didn’t even make the list!) claimed to be SQLite & Postgres friendly. However, when it came down to it, samples were always against SQL-Server (and related products), or documentation in getting things working with non MS databases was sketchy, incomplete, or incomprehensible at best.

All the ORMs provided support for MS technologies out of the box; the others frequently felt like I was hacking code to make them work. I eventually settled on a list of 18 ORMs to test.

Before I divulge my results, however, I would like to say that many of these ORMs are perfectly capable in their own designed scenarios. I’ve used many of them over the years on other projects without incident. But, putting them to the test in the scenario listed here, the winner was the one that I least expected.

Servicestack ORM Lite

Servicestack as a whole is a massive product, and is used in many places in the .NET world today. It powers many well known sites.

Everything was going well initially, and I thought great, this is going to be the one, and it’s well supported and documented. That was until, I tried to update.

Following the examples on the Servicestack site, I hit a brick wall in that every time I tried to update my object, I would get an update error telling me the object already existed. It seems that the library was not able to understand that the object I was modifying had just been found in the database, and so was trying to save a new entry, which meant a primary key clash in the DB engine.

NPoco

Another simple contender, but unfortunately not one where I even got my test app working. I got a number of errors in Visual Studio and .NET complaining about mixed mode assemblies in .NET4 and .NET 4.5 libraries, and an obligatory MS link to follow to change the applications config to make it all work.

FluentData

I had the same issue here as with NPoco, and while I most likely would have been able to solve it given time (and a chunk of reading), I felt the time was best spent moving onto the next candidate.

ORM-Micro

I didn’t even get off the drawing board with this one. The amount of in-line SQL required compared to the benefit of using the object interface made available really outweighted using a normal SQL-driven ADO.NET approach. I’m not against having SQL available in an ORM should you need to use it, but in this case the use of SQL was so heavy that using raw ADO.NET would actually be easier.

Dapper

Like ORM-Micro, there was far too much SQL required to warrant moving from ADO.NET. This is not a criticism of the product because this suits some folk perfectly fine. For my requirements in this test, however, the SQL level was simply just too high.

BLToolkit

Like Servicestack, BLT tries to do many things in one large, all-encompassing library, and while it has a very rich feature set, it is incredibly difficult to navigate the information on its usage that’s available. I found it immensely difficult to navigate what appeared to be simple samples, and ended up having to download the project sources, to try and make better sense of the scattered topic lists that made up the in-site docs.

Simple.Data

This was one ORM I’ve used many times in the past, and felt sure that this would be the one that ended up showing all the others how it’s done. Unfortunately, I can’t say that it did.

The installation worked a treat, the code was intuitive, and the authors have tried really hard to make the docs as simple and straightforward as possible. The problem came when I attempted to run my app and started getting odd versioning errors with the SQLite core libraries.

Monkey ORM

This was a simple, straightforward case of misleading information.

Performing a NuGet search on SQlite turned this up in the list, but visiting the site (and looking for the NuGet bindings) it was clear that at present, this library only supports MySQL.

Kerosene ORM

Kerosene looked like a very promising library. The documentation was easy enough, the programming model was reasonably close to that of Entity Framework, but unfortunately, the NuGet package install needs some serious attention.

The library package file was consistently installed at solution level, and any package file in the actual project itself was deleted, even if it contained entries for other packages in the solution, and because of the move to solution level, this meant the package still had to be referenced manaully and was difficult to manage using NuGet.

Conclusions

The observant of you will notice there aren’t 18 libs in the list above.The remaining ones tested were:

  • PetaPoco
  • Craig’s ORM (From Craig’s utility library)
  • Napper
  • CodeConform
  • Shaolinq
  • Insight Database

Those remaining ones all had problems similar to the ones above them, and the variations on most were too subtle to warrant repeating things.

So, who won? Well, in second place I rated:

Massive

There’s not much that needs to be said about Massive; it simply just works. Because it works as an extension to the underlying ADO.NET API, it doesn’t need any special attention, or configuration, other than the usual tweaks to register the DB Provider in your app / web.config files. These exact same additions are required to make EF work, so are an okay edit.

It also works off the same connection strings you already have defined, and because it doesn’t link to any third-party providers, it can be installed, and you’re left to add the other needed packages yourself.

So, why only secnd place? Well, Massive isn’t as intuitive (In a Linq fashion) as the winner. You have to use the .NET 4 default parameter syntax for most of your calls, which can be a little unexpected. Everything, however, is driven through code, and there’s no SQL unless you need it.

And, the winner is (drum roll, please):

MicroLite

I’d never heard of this lib before now, but it turned up during the course of my searches on NuGet/Codeplex and all the other usual haunts.

As the author says in his documentation:

“The reason no one knows about it, is because no one knows it’s there.”

For the most part, you have to do nothing except the above-mentioned addition to your config file, to register your provider, and then start using it. MicroLite will actually not only configure much of its use by convention, but also will handle things like opening/closing connections for you, pooling your connections and much, much more.

I can honestly say, that for a newcomer to this package, I truly was approaching this little library just as a newcomer would. The documentation had me up and running in less than 10 minutes from knowing absolutely nothing about the lib, and converting my EF-based code over to use this package took no more than about 5 minutes. The author has gone to quite some lengths to make this lib resemble EF as closely as possible, while still retaining its own nuances and characteristics.

All in all, I have to admit to being very impressed.

I had to install the SQLite assemblies myself manually, but again that’s not a problem, as this is also a step that needs to be done under entity framework too. The resulting code needed to perform the 2 tasks asked of it; that was simply just the following:

private MicroLite.ISessionFactory _sessionFactory;

Inside the constructor:

_sessionFactory =
   Configure.Fluently().ForSQLiteConnection("myConnectionString").CreateSessionFactory();

Where “myConnectionString” is the same connection string defined in your config file as would be defined for an entity framework.

To add a record:

var newObject = new MyTableObject
{
   Name = "Peter Shaw",
   Email = "shawty_ds@yahoo.com
};

using(var session = _sessionFactory.OpenSession())
{
   using(var transaction = session.BeginTransaction())
   {
      session.Insert(newObject);
      transaction.Commit();
    }
}

and to modify a record:

using (var session = _sessionFactory.OpenSession())
{
   using (var transaction = session.BeginTransaction())
   {
      var myObject = session.Single<MyTableObject>(1);
      myObject.Email = "shawty@anewemail.com";
      session.Update(myObject);
      transaction.Commit();
   }
}

and that’s all there is to it.

If you’re used to EF and Linq, MicroLite is almost identical to set up and use, and what’s more it covers everything EF does and more. It currently has information on the site showing how to use:

  • FireBird
  • MySql
  • PostgreSQL
  • SQLServer CE

and the rest of the usual suspects.

In Summary

If any of the authors of any of the packages I mention in this post are interested in discussing the pain points I found while testing them, please do feel free to reach out to me. What I don’t want is for this to end up being a list of what’s bad and what’s not. I’d rather that it was used as a jumping-off point to better serve the users of these various ORMs.

The biggest glaring thing that struck me as needing improvement was the lack of inconsistancy among products, a really great starting point would be a check list so that new users researching and looking for a great ORM to fit their project would be able to tell almost instantly which boxes are ticked.

If you have any ideas for subjects you’d like me to cover in this column, please feel free to reach out to me on Twitter as @shawty_ds or look me up on the Lidnug .NET users group on Linked-in that I help run. I’m always happy to talk about anything that involves .NET.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read