Small is Beautiful - .NET Micro ORMs Blog post

The concept of Object relational mapping (ORM) has been around for a long time, but it really seems to have come to prominence in the .NET world with the rise of LINQ and ASP.NET MVC. 

Microsoft themselves have released two major ORMs in recent years - first there was LINQ To SQL and then, more recently, the heavy-weight ADO .NET Entity Framework. These join established offerings from 3rd parties such as NHibernate and SubSonic.

Rise of the ORM

Perhaps the reason for this growth in interest in ORMs is the promise they hold - to liberate us from the tedious and often repetitive task of hand-writing SQL queries, loading results and then hydrating objects from those results. Wouldn't it be nice if we could just create our database and then auto-magically have all the CRUD methods necessary to manipulate our classes generated for us? Wouldn't it be even better if we could just create our classes and generate the database and associated methods from those? This is the seductive promise of the ORM.

A Fickle Temptress

However, the ORM can be a fickle temptress - her beguiling looks are not all they seem. Eventually, if you work on any decent size projcet you will encounter the dreaded  Object-relational impedance mismatch. In essence this is the disconnect between the square peg of relational databases and the round hole of object-orientated programming. Whilst ORMs may mask this gap (with layers of abstraction and complexity), they don't always succeed in fully shielding it from you. There inevitably comes a time when you end up thinking, "Actually, if only I could hand-tune this query in SQL....". 

Sometimes this is down to performance (ORMs can often be many times slower than native SQL) and sometimes it is down to not being able to exactly generate the query you would like without resorting to hacks (the ubiquitous 'coding horror' of Subsonic). Usually there will come a point where you are banging your head against the wall - simply because modern ORMs are extremely complex beasts and sometimes that leaky abstraction ends up as a pool on your desk. What was designed to save you time and effort suddenly ends up causing your more frustration and effort. I'm sure we've all been there.

Enter the Micro ORM

A few smart people realised that perhaps trying to do away with SQL all together was perhaps not such a great idea. Whilst SQL is undoubtedly an ugly and inconsistent language it still is often, for better or worse, the best option for querying relational databases. After all, that is it's Raison d'être, yes? But mapping data to objects is still tedious, you'd agree, and does seem like a candidate for some kind of automation. This is where the micro ORM comes in - it strips down the traditional heavy-weight ORM to its core and throws away the complexity (think data contexts, XML mapping files, learning new query languages).

The new breed of micro ORM tends to be a compact single class library (often distributed as a single file), open-source (so you can meddle with it) and lightning quick (when compared to the more heavy-weight ORM offerings). They generally do away with all the trappings so you are left with a bunch of simple classes and methods for hydrating your objects from SQL queries. Nothing fancy, just a few basic methods that enable you to do the job with minimal fuss. Documentation tends to be fairly minimal simply because you can pick the concepts up from a few code examples - no need to scour Amazon to find weighty books that explain how you are supposed to use the damn thing!

The Main Contenders

Currently the most prominent micro ORMs that are available for use in .NET are:

I'll take a very quick look at all of these in the next section...

Massive

Massive started life as Rob Connery playing around with the new Dynamic datatype introduced in .NET 4.0. He explains his reasoning as:

"I wanted to stay as close to the "metal" as I possibly could. This (to me) meant that I didn't want to worry about Types, Tracking Containers, Contexts and so on. What I did want to do is describe my table - and this is the only type I have (which, ironically, is a Dynamic type)".

Massive has a simple querying model allowing you to do stuff like this:

var table = newProducts();
//grab all the products
var products = table.All();
//just grab from category 4. This uses named parameters
var productsFour = table.All(columns:"ProductName as Name",where:"WHERE categoryID=@0",args:4);

For those that don't like the inheritance model of the initial release the second release also allows you to wire-up your tables up like this:

var tbl = new DynamicModel("northwind", tableName:"Products", primaryKeyField:"ProductID");

And the ironically named Massive achieves this is all in 365 lines of code!

Simple.Data

Mark Rendle's Simple.Data is, in some ways, one of the grand-fathers of the new wave of pared-down data access libraries (it inspired Massive, for example). Is it an ORM? Well, Mark would say not. He states on his blog:

It's a database access library built on the foundations of the dynamic keyword and components in .NET 4.0.

It's not an O/RM. It looks a bit like one, but it doesn't need objects, it doesn't need a relational database, and it doesn't need any mapping configuration. So it's an O/RM without the O or the R or the M. So it's just a /.

Semantics aside, Simple.Data lives up to its name in that it's lightweight and, erm, simple. An example from the Wiki shows how it can be used:

var db =Database.Open();// Connection specified in config.
var user = db.Users.FindByNameAndPassword(name, password);

But, you may be thinking, where does the FindByNameAndPassword method on the User class come from? Well, Simple.Data is clever in that it makes clever use of the dynamic type. As Mark explains in this post:

In this example, the type returned by Database.Open() is dynamic. It doesn't have a Users property, but when that property is referenced on it, it returns a new instance of a DynamicTable type, again as dynamic. That instance doesn't actually have a method called FindByNameAndPassword, but when it's called, it sees "FindBy" at the start of the method, so it pulls apart the rest of the method name, combines it with the arguments, and builds an ADO.NET command which safely encapsulates the name and password values inside parameters.

Clever stuff, no?

PetaPoco

PetaPoco is another relatively new ORM from Top Ten Software. It was inspired by Massive (and, like Massive, is a single file) but unlike massive it can work with plain old CLR objects (otherwise known as POCOs). It's slightly more comprehensive than some of the more minimalist implementations like Dapper, but is still extremely terse compared with the "bloat" of a full-on ORM. For instance, you can decorate classes with attributes to make updates and inserts simpler, but you aren't required to.

An example of PetaPoco in action is:

var a = db.SingleOrDefault("SELECT * FROM articles WHERE article_id=@0",123));

long count=db.ExecuteScalar("SELECT Count(*) FROM articles");

Pretty simple and easy to understand, you'd agree?

As a bonus, a customised version of PetaPoco also powers the data-layer in my favourite CMS, Umbraco.

Dapper

The last micro ORM we'll be looking at is Dapper - "a simple object mapper for .NET" by Sam Saffron. Again, it's just a single class file and very easy to add to your project.

Dapper came about because Sam, a brilliant developer who works on the StackExchange family of sites, needed to improve the performance of pages on StackOverflow (a very high impression site). His blog post A Day in the Life of a Slow Page at Stack Overflow   outlines his frustration with the SQL generated by LINQ to SQL and the way it "leaks performance":

The LINQ-2-SQL abstraction is leaking 78ms of performance, OUCH. This is because it needs to generate a SQL statement from our fancy inline LINQ. It also needs to generate deserializers for our 5 objects. 78ms of overhead for 60 rows, to me seems unreasonable.

Dapper is perhaps the leanest and therefore the fastest of all the mappers we have looked at. In most circumstances you wouldn't be able to tell the difference between it and native ADO.NET code. But it is still simple to use, as shown below in this example:

public class Dog
{
public int? Age {get;set;}
public Guid Id {get;set;}
public string Name {get;set;}
public float? Weight{get;set;}

public int IgnoredProperty {get { return1; } }
}

var guid = Guid.NewGuid();
var dog = connection.Query("select Age = @Age, Id = @Id",new{Age=(int?)null,Id= guid });

dog.Count()
.IsEqualTo(1);

dog.First().Age
.IsNull();

dog.First().Id
.IsEqualTo(guid);

Conclusions

As you can see these "micro" ORMs manage to maintain a nice balance between simplicity of use and functionality. Yes, you will often end up hand-writing more code compared to using a fully-fledged ORM. On the other hand you will probably end up tearing less of your hair out, too! Of course micro ORMs have their downside, too - you might miss things like transaction management, object tracking, intellisense, compile-time query checking etc. Like in all programming there is never a single silver bullet. However, the ability to fine-tune your queries and remove the "bloat" can feel refreshing and give your app a real speed increase, too.

Of course, there is nothing stopping you from augmenting a "real" ORM with a "micro" ORM for those parts where you need to tune performance (as StackOverflow do). In most cases you can mix and match and get the best out of both worlds.

So which of the above should you try? Well, I'd say all of them - most can be installed very simply (some via Nuget packages) and you can then play around to get a feel for how they work. In the end, it probably comes down to personal preference and community support.

Addendum

Since writing this I've become aware of yet more micro ORM's for .NET. I haven't had time to evaluate these personally, but here are a some more to consider:

  • FluentData - "FluentData is a Micro ORM that makes it simple to select, insert, update and delete data in a database. It gives the developer the power of ADO.NET but with the convenience of an ORM. It has a simple to use fluent API that uses SQL - the best and most suitable language to query data, and SQL or fluent builders to insert, update and delete data."

You can also find a whole host of Micro ORMs on NuGet.


17 Comments


Ryan  Avatar

PetaPoco as T4 Templates that generate all of it's Poco's. It generates the database context class.


But what I do is I use SSDT (Sql Server Data Tools) in Visual Studio "Sql Server Database" Project Type to build out my MS Sql Database.


I use the SSDT Project to build all of my tables, stored procs, views, etc etc.


Then I use peta poco in a class Library and I copy the T4 template for each Schema in my Database and I turn off the GenerateOperations boolean so the templates just generate my Poco's for my tables and views.


To handle migrations I use SSDT and create publishing profiles. One for our Staging Server and one for Production. I can just click -> Publish -> Dev Server and the DB and it's schema changes and data scripts etc all go out.


Then I can just go into the Data Layer Library where peta poco is and rebuild and all the T4 templates run and update all my pocos to reflect the new changes.


In short, imo.. using SSDT and PetaPoco is easier on migrations than say Entity Framework. No migration classes or powershell commands to run etc etc.


SSDT can even compile to a single SQL file containing all the operations to create/populate the database and it can compile to incremental update scripts as well.


Eric Schneider Avatar

Another small but simple ORM library.


http://nuget.org/packages/SODA


frankyt Avatar

If you plan to use Simple.Data, check the "Simple.data.pad" project. It's been very usefull for me to generate code. Also if you are looking to implement something without any example in the project "simple.data.sample", check the test project. I implement some transaction like that.


Colorado Avatar

These really are not anything new. My first exposure to quick simple and fast was using ODBC.Direct in the early VB days. This driver linked up fast and passed through the query to the SQL Server passing back the results. Then as now the network was the bottle neck so we only wanted the data we intended to process or look at. The problem with ADO.Net is it builds these enormous memory structures, not unlike the old Jet db engine (if it is not fast give it a fast name). Somebody may have a need for all those objects, methods and parameters, but I have never have. However I have worked with a lot of code other people have written where they used all that stuff making the code almost too complex and costly to upgrade. Keep the Micro ORMs coming.


Mehmet Demirbilek Avatar

If you did not see yet, you should look Uni. It is in Nuget Gallery. I think, it is inspired from all of the above Micro ORMs.


Anthony Avatar

This all sounds very good. The only question I have though, what about migrations? With Entity Framework, it's fearly easy nowadays do a migration when your POCO's have changed.


Or do migrations not apply when using a Micro ORM?


greetings,


Anthony


Dan Diplo Avatar

@Anthony - migrations don't really apply with Micro ORMs. I guess their whole purpose is to be really simple, so it's up to you to keep your database and tables in sync The plus side of this is that you never get into situations where you are scared to change either the database or your POCOs because you are not quite sure what your "enterprise" ORM is going to do!


Asava Samuel Avatar

Here is an ORM that works with Microsoft Access - https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx 


Saber Avatar

@Anthony, I'm interested in this too. I like the simplicity of these Micro Orm's, but migrations makes my life a little bit easier (and also let's me curse more ;) )


I think a hybrid solution is also possible. Using Entity Framework for Migrations. And one of the Micro Orm for data access. Using Repositories you could even do both and only use Micro Orm's if performance is critical.


Andreas Kleffel Avatar

"Small is Beautiful" - that's the title of a famous book :)


http://en.wikipedia.org/wiki/Small_Is_Beautiful


Naweed Akram Avatar

Another opensource Micro ORM: https://github.com/naweed/XGENO.DBMapper 


Gus Avatar

Gus

I am fond of Thunderstruck.


https://github.com/wagnerandrade/Thunderstruck


It doesn't pretend to be anything more than an ADO wrapper but it's very easy and elegant to use.


Svyatoslav Danyliv Avatar

You have to try linq2db https://github.com/linq2db/linq2db


It fastest than dapper and even have rich linq provider


Paresh Avatar

Thank you for this useful information. Nice blog! Another powerful yet simple .Net ORM framework available here, NJDX for .Net, http://www.softwaretree.com/v1/products/njdx/njdx.html 


Ritesh Sutaria Avatar

I use Vega https://github.com/aadreja/vega. Its fast, easy and comes with a lot of other features like audittrail, concurrency check etc. Worth looking at it.


Eric Schneider Avatar

Parallel queries support, and many database vendors supported: https://www.nuget.org/packages/Symbiotic_Micro_ORM_Net_Standard_x64/3.0.0 The new version supports immutable types, interfaces, and type abstraction: https://www.nuget.org/packages/Symbiotic_Micro_ORM_Net_Standard_x64/3.1.0-rc

Just fill in the form and click Submit. But note all comments are moderated, so spare the viagra spam!

Tip: You can use Markdown syntax within comments.