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.



Related Articles

Comments

  • Pedantry, and then some.

    Posted by Brady Kelly on 11/19/2014 08:37am

    Where you say" struck me as needing improvement was the lack of inconsistancy among products", I think a lack of consistency is a plus, where it's normally a lack of consistency that concerns me. Then, in you example of MicroLite you use an explicit transaction to add a record, but not to modify an exisitng record. Any reason for this besides chance?

    • RE: Pedantry and then some.

      Posted by Peter Shaw on 12/03/2014 05:00am

      Yes indeed there is. :-) I used that to expand the examples out to a few more lines, and make things look a bit fuller. The fact of the matter is, Microlite can do most operations it needs to in about 1 line of code, and when it comes to marking that up on the blog it's easy for it to get lost in among the other text. So yes, Guilty as charged on that one, it's an artificial inflation and I've taken a slap on the wrist accordingly.

      Reply
    Reply
  • Glimpse + the problems of community-developed software

    Posted by Neil on 09/15/2014 08:37am

    "As anyone who's worked with EF will know, it can be insanely difficult trying to get the generated SQL back out." Run Glimpse. It's easy and beautiful and free and will tell you everything you ever wanted to know. As for PG and SQLite compatibility with the new EF6, I think you have to ask the communities that support those libraries. MS did a lot of work to get those layers working in EF5, but now they're going open-source. Which means community developed. Which means the community has to step up. It's an impressive list of ORMs you've tested, but the problem isn't when things are easy (like in your example). It's when things get hard. I've seen some crazy logic thrown at EF. It's not perfect but it has the power to handle it. Do these others? I really don't know. It sounds sexy to say, "Hey, I'm not a MS fanboy! Look my webapp uses PostgreSQL!" but in reality if you're developing on the MS stack why wouldn't use use one of the many variants of SQL Server? You can go a LONG way on the free versions. And if you're hooking into your GIS team's cool library, you should be making JSON API calls to their node or Ruby server, not hooking directly into their database. What I'm afraid you've done here is thrown the baby out with the bathwater, dumping an established and feature-rich ORM at the first sign of trouble to meet a questionable requirement. But I could be wrong. It's still impressive research and I'm glad you did it! :-)

    • RE: Glimpse + the problems of community-developed software

      Posted by Peter Shaw on 09/24/2014 03:05am

      Hi Neil, Thanks for your comments. RE: EF, I actually do a lot of work in .NET and I use EF quite extensively. EF for me in general does have a lot of potential, especially when used with MS based technologies. I do agree with you regards to it being community supported now, but I also have to say that during my debugging of EF 6 against both PG & SQLite that EF demonstrated more than it's own fair share of problems. This will be something I'll be keeping tabs on, as I have a huge volume of existing code that's created to work with EF, and a lot of cases where I'd ideally like to move projects between databases as easy as possible. To answer your question on why I might use PG rather than MS-SQL on the MS Stack, well there's many cases to be honest. Yes I openly admit that I do a lot of GIS work, but without giving too much away NOT everything I write is web based. I write a large amount of support code for back and services and for embedded applications designed to run on telemetry devices and many other things. I do use the JSON approach where it fits with the project, but a lot of the time I do have to talk directly to a DB connection. As for reasons why you might use PG, well it depends on what exactly your using your database for. PG has a massive amount of functionality for broad data processing and manipulation that MS-SQL simply does not have, it also has things like true object inheritance at a table level and built in array types for columns that can be fully indexed. I'm not saying that PG is the be all and end all of databases, but if your doing your project justice, then it should never be a case of picking a data technology simply because it's in the same platform, you should be choosing the technology based on the needs of the project, and if that means using EF is a better fit for what you need to do then so be it. I do hope that the providers for EF eventually get the improvements they need, I've used both data technologies successfully in the past under EF5 and apart from having to do a few things manually, I can't complain about them, going forward however there needs to be considerable improvement all round on both sides of the equation.

      Reply
    Reply
  • open source ORM dev

    Posted by Roman Ivantsov on 09/14/2014 06:03pm

    would be interesting to know your opinion about another ORM which is not on your list: VITA (http://vita.codeplex.com). I believe it is a true challenger to your winner picks. It supports MS SQL, MySql and Postgres, no SQLite yet, but its' coming. All extensive unit tests run against all three (and SQL Compact). Supports caching, batching updates, authorization framework, identities, many-to-one and many-to-many, and lots of other stuff. Modular app construction suggested I believe is a big win. Docs are sparse, but Quick Start guide is there. thank you Roman thanks Roman

    • RE: open source ORM dev

      Posted by Peter Shaw on 09/24/2014 03:08am

      Hi Roman, Vita was not one that turned up when I researched this article, but I've made a note of it now so I'll take a look. Given just how much interest this article generated I think there'll definitely be a follow up at some point. Thanks for letting me know.

      Reply
    Reply
  • Consultant

    Posted by Jitendra on 09/14/2014 12:00am

    Also, why did u not consider Subsonic 3 by rob conery for your test? We have been using it for a long time, would like to know your comments on the same

    • RE: Consultant

      Posted by Peter Shaw on 09/24/2014 03:16am

      Hi Jitendra, I did consider subsonic, but I had problems even getting the libraries to install and work correctly so I never even got as far as writing any test code. This meant that even though I had various ORM's with various points of pain, I was at least able to get to the "Attempting to Code" stage with many. I only allocated an hour for each test, and once that hour was exhausted it was time to move onto the next, this was the only way I could be fair to each and every one of them. If I'd allocated more time for the test, then there's a very high likely hood that the outcome would have been much different here, and there would most probably have been a very different list published. My goal was to try and replicate the scenario and environment a busy developer would face in there working life, this meant short time scales, no time to do a proper deep dive learning exercise and code to production in as short a time as possible. For the record, I have used subsonic in the past (Although I think that may have been V2) and it worked well for me at the time on the project I used it on.

      Reply
    Reply
  • What about NHibernate?

    Posted by Ricardo Peres on 08/22/2014 11:00am

    Why didn't you consider NHibernate? It's one of the oldest ORMs around, its open source and offers far more functionality than EF. One aspect where it is behind is documentation, though.

    • RE: What about NHibernate?

      Posted by Peter Shaw on 09/24/2014 03:22am

      Hi Ricardo, Like subsonic, NHibernate was tested, but I failed to make sense of the documentation or get the install working to a satisfactory level in the short time I allocated, given a longer time frame I have every faith that NH would have delivered. I've not used NH much, but I certainly know that it is indeed a very strong contender, unfortunately it's documentation does let it down, badly. Granted there are a number of mainstream books available to learn it and become one with the NH eco system, but IMHO I would only go as far as buying books if I or the company I was working with, had already made a long term commitment to using it. NH is a big project with quite a heavy weight impact, as a result you really want to be sure your going to stick with it if your going to use it. It's not the type of ORM you can pick up, use for a day then throw away in favor of the next newer one that comes along.

      Reply
    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Today's agile organizations pose operations teams with a tremendous challenge: to deploy new releases to production immediately after development and testing is completed. To ensure that applications are deployed successfully, an automatic and transparent process is required. We refer to this process as Zero Touch Deployment™. This white paper reviews two approaches to Zero Touch Deployment--a script-based solution and a release automation platform. The article discusses how each can solve the key …

  • On-demand Event Event Date: December 18, 2014 The Internet of Things (IoT) incorporates physical devices into business processes using predictive analytics. While it relies heavily on existing Internet technologies, it differs by including physical devices, specialized protocols, physical analytics, and a unique partner network. To capture the real business value of IoT, the industry must move beyond customized projects to general patterns and platforms. Check out this webcast and join industry experts as …

Most Popular Programming Stories

More for Developers

RSS Feeds