Micro-ORMs for .NET Compared – Part 3

This is the final part of a 3-part series comparing micro-ORMs.  We’ve already seen Dapper and Massive.  Now it’s time for PetaPoco.

PetaPoco

Website: http://www.toptensoftware.com/petapoco/
Code: https://github.com/toptensoftware/petapoco
NuGet: http://nuget.org/packages/PetaPoco

Databases supported: SQL Server, SQL Server CE, Oracle, PostgreSQL, MySQL
Size: 2330 lines of code

Description

PetaPoco was, like the website states, “inspired by Rob Conery’s Massive project but for use with non-dynamic POCO objects.”  A couple of the more notable features include T4 templates to automatically generate POCO classes, and a low-friction SQL builder class

Installation

There are two packages available to install: Core Only and Core + T4 Templates.  I chose the one with templates, which raises a dialog with the following message:

“Running this text template can potentially harm your computer.  Do not run it if you obtained it from an untrusted source.”

PetaPoco has a click-to-accept Apache License.  If your project is a console application, you’ll need to add an App.config file.

Usage

Because PetaPoco uses POCOs, it looks more like Dapper than Massive at first glance:

class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
}

class Program
{
    private static void Main(string[] args)
    {
        var db = new Database("northwind");
        var products = db.Query("SELECT * FROM Products");
    }
}

There is also experimental support for “dynamic” queries if you need them:

var products = db.Query("SELECT * FROM Products");

PetaPoco has a lot of cool features, including paged fetches (a wheel I’ve reinvented far too many times):

var pagedResult = db.Page(sql: "SELECT * FROM Products",
    page: 2, itemsPerPage: 20);

foreach (var product in pagedResult.Items)
{
    Console.WriteLine("{0} - {1}", product.ProductId,
        product.ProductName);
}

While POCOs give you the benefit of static typing, and System.Dynamic frees you from the burden of defining all your objects by hand, templates attempt to give you the best of both worlds.

The first thing you have to do the use templates is ensure that your connection string has a provider name.  Otherwise the code generator will fail.  Then you must configure the Database.tt file.  I changed the following lines:

ConnectionStringName = "northwind";  // Uses last connection string in config if not specified
Namespace = "Northwind";

When you save it, you might get a security warning because Visual Studio is about to generate code from the template.  You can dismiss the warning if you haven’t already.

Now you can use the generated POCOs in your code:

var products = Northwind.Product.Query("SELECT * FROM Products");

First Impressions

PetaPoco is surprisingly full-featured for a micro-ORM while maintaining a light feel and small code size.  There is too much to show in a single blog post, so you should check out the PetaPoco website for a full description of what this tool is capable of.

Final Comparison

All of these micro-ORMs fill a similar need, which is to replace a full-featured ORM with something smaller, simpler, and potentially faster.  That said, each one has its own strengths and weaknesses.  Here are my recommendations based on my own limited testing.

You should consider… If you’re looking for…
Dapper Performance, proven stability
Massive Tiny size, flexibility
PetaPoco POCOs without the pain, more features
Advertisements

Micro-ORMs for .NET Compared – Part 2

This is Part 2 of a 3-part series.  Last time we took a look at Dapper.  This time we’ll see what Massive has to offer.

Massive

Website: http://blog.wekeroad.com/helpy-stuff/and-i-shall-call-it-massive
Code: https://github.com/robconery/massive
NuGet: http://www.nuget.org/packages/Massive

Databases supported: SQL Server, Oracle, PostgreSQL, SQLite
Size: 673 lines of code

Description

Massive was created by Rob Conery.  It relies heavily on the dynamic features of C# 4 and makes extensive use of the ExpandoObject.  It has no dependencies besides what’s in the GAC.

Installation

Unlike Dapper and PetaPoco, Massive does not show up in a normal NuGet search.  You’ll have to go to the Package Manager Console and type “Install-Package Massive -Version 1.1” to install it.  If your solution has multiple projects, make sure you select the correct default project first.

If your project is a console application, you’ll need to add a reference to System.Configuration.

Usage

Despite its name, Massive is tiny.  Weighing in at under 700 lines of code, it is the smallest micro-ORM I tested.  Because it uses dynamics and creates a connection itself, you can get up and running with very little code indeed:

class Products : DynamicModel
{
    public Products() : base("northwind", primaryKeyField: "ProductID") { }
}

class Program
{
    private static void Main(string[] args)
    {
        var tbl = new Products();
        var products = tbl.All();
    }
}

It’s great not having to worry about setting up POCO properties by hand, and depending on your application, this could save you some work when your database schema changes.

However, the fact that this tool relies on System.Dynamic is also its biggest weakness.  You can’t use Visual Studio’s Intellisense to discover properties on returned results, and if you mistype the name of a property, you won’t know it until runtime.  Like most things in life, there are tradeoffs.  If you’re terrified of “scary hippy code”, then this could be a problem.

First Impressions

Massive is very compact and extremely flexible as a result of the design choice to use dynamics.  If you’re willing to code without the Intellisense safety net and can live without static typing, it’s a great way to keep your data mapping simple.

Continue to Part 3…

Micro-ORMs for .NET Compared – Part 1

Recently, I have been made aware of a lightweight alternative to full-blown ORMs like NHibernate and Entity Framework.  They’re called micro-ORMs, and I decided to test-drive a few of the more popular ones to see how they compare.

Each of the tools listed here are small and contained within a single file (hence the “micro” part of the name).  If you’re adventurous, it’s worth having a look at the code since they use some interesting and powerful techniques to implement their mapping, such as Reflection.Emit, C# 4 dynamic features, and T4 templates.

The Software

Dapper

Website: http://code.google.com/p/dapper-dot-net/
GitHub: https://github.com/SamSaffron/dapper-dot-net
NuGet: http://nuget.org/packages/Dapper

Databases supported: Any database with an ADO.NET provider
Size: 2345 lines of code

Description

Dapper was written by Sam Saffron and Marc Gravell and is used by the popular programmer site Stack Overflow.  It’s designed with an emphasis on performance, and even uses Reflection.Emit to generate code on-the-fly internally.  The Dapper website has metrics to show its performance relative to other ORMs.

Among Dapper’s features are list support, buffered and unbuffered readers, multi mapping, and multiple result sets.

Installation

In Visual Studio, use Manage NuGet Packages, search for “Dapper”, and click Install.  Couldn’t be easier.

Usage

Here we select all rows from a Products table and return a collection of Product objects:

class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
}

class Program
{
    private static void Main(string[] args)
    {
        using (var conn = new SqlConnection("Data Source=.\\SQLEXPRESS;
            Initial Catalog=Northwind;Integrated Security=SSPI;"))
        {
            conn.Open();
            var products = conn.Query<Product>("SELECT * FROM Products");
        }
    }
}

As you can see from the example, Dapper expects an open connection, so you have to set that up yourself.  It’s also picky about data types when mapping to a strongly typed list.  For example, if you try to map a 16-bit database column to a 32-bit int property you’ll get a column parsing error.  Mapping is case-insensitive, and you can map to objects that have missing or extra properties compared with the columns you are mapping from.

Dapper can output a collection of dynamic objects if you use Query() instead of Query<T>():

    var shippers = conn.Query("SELECT * FROM Shippers");

This saves you the tedium of defining objects just for mapping.

Dapper supports parameterized queries where the parameters are passed in as anonymous classes:

    var customers =
        conn.Query("SELECT * FROM Customers WHERE Country = @Country
            AND ContactTitle = @ContactTitle",
        new { Country = "Canada", ContactTitle = "Marketing Assistant" });

The multi mapping feature is handy and lets you map one row to multiple objects:

class Order
{
    public int OrderId { get; set; }
    public string CustomerId { get; set; }
    public Customer Customer { get; set; }
    public DateTime OrderDate { get; set; }
}

class Customer
{
    public string CustomerId { get; set; }
    public string City { get; set; }
}

...

var sql =
    @"SELECT * FROM
        Orders o
        INNER JOIN Customers c
            ON c.CustomerID = o.CustomerID
    WHERE
        c.ContactName = 'Bernardo Batista'";

var orders = conn.Query<order, customer,="" order="">(sql,
    (order, customer) => { order.Customer = customer; return order; },
    splitOn: "CustomerID");

var firstOrder = orders.First();

Console.WriteLine("Order date: {0}", firstOrder.OrderDate.ToShortDateString());

Console.WriteLine("Customer city: {0}", firstOrder.Customer.City);

Here, the Customer property of the Order class does not correspond to a database column.  Instead, it will be populated with customer data that was joined to the order in the query.

Make sure to join tables in the right order or you may not get back the results you expect.

First Impressions

Dapper is slightly larger than some other micro-ORMs, but its focus on raw performance means that it excels in that area.  It is flexible and works with POCOs or dynamic objects, and its use on the Stack Overflow website suggests that it is stable and well-tested.

Continue to Part 2…