Small is Beautiful - .NET Micro ORMs

A quick look at Massive, Simple.Data, Peta Poco and Dapper

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.

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.

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 = new Products();
//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?

Peta Poco

Peta Poco 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 Peta Poco 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?

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 show 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 { return 1; } }
}            
            
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."
Posted: 15 August 2011 | Bookmark: Permalink | Comments: Post Comment | Follow: RSS Feed | Bookmark and Share |
Tagged: , , | Social Tags: Databases, .NET framework, Query languages, Java enterprise platform, Language Integrated Query, Object-relational database, NHibernate, ADO.NET Entity Framework, SQL, Computing, Data management, Object-relational mapping | Industry Terms: brilliant developer, impression site | Organisations: Top Ten Software, Amazon, ADO.NET, Microsoft, ASP.NET | People: Mark Rendle, Sam Saffron, Rob Connery

12 comments for “Small is Beautiful - .NET Micro ORMs”

  • Another small but simple ORM library.

    http://nuget.org/packages/SODA

     
     
  • Gravatar of frankyt
    frankyt

    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.

     
     
  • Gravatar of Colorado
    Colorado

    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 comming.

     
     
  • 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.

     
     
  • 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

     
     
  • @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 synch. 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!

     
     
  • Gravatar of asava samuel
    asava samuel

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

     
     
  • Gravatar of Saber
    Saber

    @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.

     
     
  • Gravatar of John Vance
    John Vance

    The example for dapper was confusing, until I realized that html sanitizing had stripped off the parameterized type from this line:

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

    Substitute angle brackets for square braces in the above. The html sanitizer for comments is pretty broken. Submitting angle-bracket enclosed "Dog" causes a 503 error.

     
     
  • Gravatar of Andreas Kleffel
    Andreas Kleffel

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

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

     
     
  • Gravatar of Jesús López
    Jesús López

    Another .net micro ORM: http://www.codeproject.com/Articles/753796/i-nercya-EntityLite-A-Lightweight-Database-First-M

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

     
     

Post a Comment

Please fill the form in below if you wish to add a comment:
Gravatar

Please don't post raw HTML - use editor above - otherwise you will get an error.