3 Reason Why I Prefer Using Norm vs Dapper

Norm data-access library for .NET was something that started as a fun pet-project (as many of these homebrew micro ORM's projects are) and, now, I believe it has reached its full maturity with the latest version 3.1.

All these small micro ORM's share the same basic functionalities, for example, they will all map the result of your SQL query to the data structure of your choice.

So for example, if we have a class like this:

public class OrderDetail
{
    public int OrderDetailID { get; set; }
    public int OrderID { get; set; }
    public int ProductID { get; set; }
    public int Quantity { get; set; }
}

And, we wanted to map the results from query SELECT TOP 10 * FROM OrderDetails, Dapper example would typically look like this:

using var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools());

var sql = "SELECT TOP 10 * FROM OrderDetails";
var orderDetails = connection.Query<OrderDetail>(sql);

// output the results
Console.WriteLine(orderDetails.Count());                
FiddleHelper.WriteTable(orderDetails);

Try it yourself

And, similarly, Norm example would look like this:

using var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools());

var sql = "SELECT TOP 10 * FROM OrderDetails";
var orderDetails = connection.Read<OrderDetail>(sql);

// output the results
Console.WriteLine(orderDetails.Count());                
FiddleHelper.WriteTable(orderDetails);

Try it yourself

That is indeed very, very similar.

However, for me, there are at least three good reasons - why I would want to prefer to use Norm over Dapper in my projects.

Here they are...

1. Not Everything Has To Have a Model Class.

I think we're just simply used to creating a new class (or a record in newer versions) model for every query that can yield some results.

That's how it was always done.

Of course, not every query needs to have a model class. A typical example would be a count query, for example:

SELECT COUNT(*) 
FROM OrderDetails

For such cases, Dapper has special methods, other than the Query. For example:

var sql = "SELECT COUNT(*) FROM OrderDetails";
var count = connection.QuerySingle<int>(sql);

Norm still uses the same Read method, but it uses an LINQ extension Single to yield the first result:

var sql = "SELECT COUNT(*) FROM OrderDetails";
var count = connection.Read<int>(sql).Single();

Now, what about if we wanted to get the results from the query that returns two values, instead of one.

In that case, creating a model class is still a bit of overkill in my opinion.

Let's say, for example, a minimal OrderID value with a total count for that ID.

The query would look something like this:

SELECT TOP 1 min(OrderID) as OrderID, COUNT(*) 
FROM OrderDetails 
GROUP BY OrderID

This is possible with Dapper, but we must use the multi-mapping feature and it is a little bit complicated in my opinion:

var sql = @"
SELECT TOP 1 min(OrderID) as OrderID, COUNT(*) 
FROM OrderDetails 
GROUP BY OrderID
";
var (orderId, count) = connection.Query<int, int, (int, int)>(sql,
    (orderId, count) => (orderId, count), 
    splitOn: "OrderID").Single();

Norm, still will still use the same Read method:

var sql = @"
SELECT TOP 1 min(OrderID), COUNT(*) 
FROM OrderDetails 
GROUP BY OrderID
";
var (orderId, count) = connection.Read<int, int>(sql).Single();

Notice that in this case, min(OrderID) doesn't have a name alias, because Norm doesn't need a splitOn parameter to distinguish values by name.

Those two values are mapped by the position as they appear, names are completely irrelevant in this case.

This is neat, because, again, if we simply need a couple of values, we don't really need to create a class, that is too much typing.

For example, three values, product name, product unit, and product price:

var sql = "SELECT TOP 10 ProductName, Unit, Price FROM Products";
foreach(var (name, unit, price) in connection.Read<string, string, decimal>(sql))
{
    Console.WriteLine($"{name} has price {price} per {unit}");
}

Try it yourself

Typically, for the example above, we would only need those three values, perhaps for some output or some algorithm, we might don't want to create a model class just for that.

Let's now say that we want to build a dictionary from the database for products, where the dictionary key is product id and dictionary value is the product name.

Norm expression would look something like this:

var sql = "SELECT TOP 10 ProductID, ProductName FROM Products";
var dict = connection.Read<int, string>(sql).ToDictionary(p => p.Item1, t => p.Item2);

Try it yourself

This is a bit suboptimal because now we've lost field names and we must use generic Item1 and Item2 names.

In complicated LINQ expressions, this can be a serious issue.

This is why Norm supports named tuples.

So, example from above can look like this:

var sql = "SELECT TOP 10 ProductID, ProductName FROM Products";
var dict = connection.Read<(int Id, string Name)>(sql).ToDictionary(p => p.Id, t => p.Name);

Try it yourself

This is much better now, because we still have strong types, and we have proper names also, together with full IDE and IntelliSense editor support.

And we still didn't have to create a model class.

But, of course, you can if you really want to.

And it would be a probably wise decision because then, fields are no longer mapped by position, but rather by name.

2. Enumeration Generator by Default

Those familiar with Dapper may remember that the Query method in Dapper basically has two different versions:

• Buffered (default): Dapper builds an entire List from the results that then the Query method returns.

• Unbuffered: Dapper creates an enumeration generator that yields the result on actual iteration and the Query method returns that generator instead of an actual List structure.

The buffered version is a suboptimal solution for a couple of reasons. For example:

• Possibility of multiple iterations over the same data. Once to build the actual List and second time to do something with that data in your code (render the UI, generate the service result, etc).

• You might not want to have to build the List in the first place. You might prefer to have an Array or Dictionary for whatever reason.

• If you would like to fetch only the first row from the result - there is no way to stop the iteration that builds the result list after the first row. As a consequence of this design decision, Dapper has to have separate methods to make iterator stop after the first row. That is what the methods QuerySingle and QueryFirst are for.

Now, I'm convinced that the real reason behind this decision is because there was no way to implement an async version of unbuffered query prior to .NET Standard 2.1. and .NET Core 3.

Because you have to return IAsyncEnumerable instead of Task<IEnumerable> to achieve that, and that interface didn't exist prior to those versions.

So, I believe that having just a sync version Query unbuffered method and async only in buffered version was a good compromise.

Norm, on other hand, is strictly .NET Standard 2.1. library. That means that it is supported only on the following .NET implementations:

• .NET5
• .NET Core 3.0 and 3.1
• Mono 6.4
• Xamarin.iOS 12.16
• Xamarin.Android 10.0

And, as such, it only implements an unbuffered version. That means that the Norm Read method creates and returns an enumeration generator that yields the result on actual iteration.

The decision to do this was really simple. If you want the same or similar functionalities in unsupported versions (such as .NET Framework for example), you can always use Dapper. It covers pretty much everything you need.

So, for example, to yield only the first row with Norm, you can use LINQ extension Single:

var sql = "SELECT COUNT(*) FROM OrderDetails";
var count = connection.Read<int>(sql).Single();

Single stops the iteration after the first read.

Following code with Dapper would iterate the entire result set only to return the first result:

var sql = "SELECT OrderID FROM OrderDetails";
var count = connection.Query<int>(sql).Single();

To avoid that performance pitfall, you need to fall back to the unbuffered version:

var sql = "SELECT OrderID FROM OrderDetails";
var count = connection.Query<int>(sql, buffered: false).Single();

But, working, with an unbuffered version by default, has other advantages. For example, you might prefer other data structures such as a dictionary or an array and skip the list part altogether:

var sql = "SELECT OrderID FROM OrderDetails";
var orderIds = connection.Read<int>(sql).ToArray();

Or with Dapper:

var sql = "SELECT OrderID FROM OrderDetails";
var orderIds = connection.Query<int>(sql, buffered: false).ToArray();

This why Norm can have only one extension/method for all reading operations and that is Read.

For async versions, standard LINQ expressions won't work because they are extensions over the IEnumerable interface, not the IAsyncEnumerable.

The solution is to reference an System.Linq.Async into the project.

This library provides support for Language-Integrated Query (LINQ) over IAsyncEnumerable<T> sequences.

It implements all the same extensions as the standard LINQ library.

It is created and supported by the ".NET Foundation and Contributors" and it has over 3 and a half million downloads so far.

And it also uses the same namespace.

So async Single example from above might look something like this:

var sql = "SELECT COUNT(*) FROM OrderDetails";
var count = await connection.Read<int>(sql).SingleAsync();

But there is also one powerful async feature that you can do with the IAsyncEnumerable<T> result.

That is asynchronous streaming. With the Norm, you can asynchronously iterate foreach over the results. For example:

// Asynchronously stream values directly from database
var sql = "SELECT TOP 10 OrderId, ProductId, Quantity FROM OrderDetails";
await foreach(var (orderId, productId, quantity) in connection.ReadAsync<int, int, int>(sql))
{
    Console.WriteLine($"order={orderId}, product={productId} with quantity {quantity}");
}

Try it yourself

This allows getting the results from the connection as soon they appear and asynchronous streaming that gives you better performances and scalability.

3. PostgreSQL

And finally, small things that made me happier.

I'm a pretty heavy PostgreSQL user and it is my database of choice.

So, how important features for PostgreSQL developers are available out of the box without any additional configuration:

• Snake case mapping. When mapping to a class or a record, Norm will map all snake case names that you might have in your queries by the default.

• Array type support. If your PostgreSQL database queries return arrays, no problem, just declare your types as an array too (int[], string[], DateTime[], etc). It works the same as with simple types, tuples, or when mapping classes and records.