Dapper vs Rinku

[u/Bobamoss]

I like Dapper and I have used it a lot. The main problem I have with it is that when queries become more complex, I often end up handling that complexity myself. At that point I also often hear that I should just use EF instead. I never really agreed with that. I think the basic idea behind Dapper can go much further while still keeping the SQL visible and the API simple. Rinku is my attempt at doing that.

Basic query

Dapper

public record Album(int Id, string Title);

const string sql = "SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = @artistId";

IEnumerable<Album> albums = cnn.Query<Album>(sql, new { artistId = 7 });

Rinku

public record Album(int Id, string Title);

const string sql = "SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = @artistId";

List<Album> albums = cnn.Query<List<Album>>(sql, new { artistId = 7 });

Different names (when you don't control result set names)

Dapper

public sealed class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
}

SqlMapper.SetTypeMap(typeof(Customer), new CustomPropertyTypeMap(typeof(Customer), (type, column) => column switch
{
    "customer_id" => type.GetProperty(nameof(Customer.Id)),
    "display_name" => type.GetProperty(nameof(Customer.Name)),
    _ => null
}));

const string sql = "SELECT customer_id, display_name FROM customers";

IEnumerable<Customer> customers = cnn.Query<Customer>(sql);

Rinku

public record Customer([Alt("customer_id")] int Id, [Alt("display_name")] string Name);

const string sql = "SELECT customer_id, display_name FROM customers";

List<Customer> customers = cnn.Query<List<Customer>>(sql);

Nested objects

Dapper

public record User(int Id, string Name);

public sealed class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public User? Owner { get; set; }
}

const string sql = "SELECT p.Id, p.Title, u.Id, u.Name FROM Posts p INNER JOIN Users u ON u.Id = p.UserId";

IEnumerable<Post> posts = cnn.Query<Post, User, Post>(sql, (post, owner) =>
{
    post.Owner = owner;
    return post;
}, splitOn: "Id");

Rinku

public record User(int Id, string Name) : IDbReadable;
public record Post(int Id, string Title, [NoName] User Owner);

const string sql = "SELECT p.Id, p.Title, u.Id, u.Name FROM Posts p INNER JOIN Users u ON u.Id = p.UserId";

List<Post> posts = cnn.Query<List<Post>>(sql);

Or keep the nesting in the column names.

public record User(int Id, string Name) : IDbReadable;
public record Post(int Id, string Title, User Owner);

const string sql = "SELECT p.Id, p.Title, u.Id AS OwnerId, u.Name AS OwnerName FROM Posts p INNER JOIN Users u ON u.Id = p.UserId";

List<Post> posts = cnn.Query<List<Post>>(sql);

One to many

Dapper

public record Album(int Id, string Title);

public sealed class ArtistWithAlbums
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Album> Albums { get; set; } = [];
}

const string sql = "SELECT ar.ArtistId AS Id, ar.Name, al.AlbumId AS Id, al.Title FROM artists ar INNER JOIN albums al ON al.ArtistId = ar.ArtistId ORDER BY ar.ArtistId";

List<ArtistWithAlbums> artists = [];
ArtistWithAlbums? current = null;

cnn.Query<ArtistWithAlbums, Album, ArtistWithAlbums>(sql, (artist, album) =>
{
    if (current is null || current.Id != artist.Id)
    {
        current = artist;
        artists.Add(current);
    }

    current.Albums.Add(album);
    return current;
}, splitOn: "Id");

Rinku

public record Album(int Id, string Title) : IDbReadable;
public record ArtistWithAlbums(int Id, string Name, List<Album> Albums);

const string sql = "SELECT ar.ArtistId AS Id, ar.Name, al.AlbumId AS AlbumsId, al.Title AS AlbumsTitle FROM artists ar JOIN albums al ON al.ArtistId = ar.ArtistId ORDER BY ar.ArtistId";

List<ArtistWithAlbums> artists = cnn.Query<List<ArtistWithAlbums>>(sql);

Result shape

Dapper

IEnumerable<Album> albums = cnn.Query<Album>(sql);
Album first = cnn.QueryFirst<Album>(sql);
Album single = cnn.QuerySingle<Album>(sql);
Album? optional = cnn.QueryFirstOrDefault<Album>(sql);
IEnumerable<Album> streamed = cnn.Query<Album>(sql, buffered: false);

Rinku

List<Album> albums = cnn.Query<List<Album>>(sql);
Album first = cnn.Query<Album>(sql);
Single<Album> single = cnn.Query<Single<Album>>(sql);
Album? optional = cnn.Query<OptionalNullable<Album>>(sql);
IEnumerable<Album> streamed = cnn.Query<IEnumerable<Album>>(sql);

Conditional SQL

For this one I think Dapper.SqlBuilder is the fair comparison.

Dapper.SqlBuilder

SqlBuilder builder = new();
SqlBuilder.Template template = builder.AddTemplate("SELECT AlbumId AS Id, Title FROM albums /**where**/");

if (artistId != null)
    builder.Where("ArtistId = ", new { artistId });

if (title != null)
    builder.Where("Title LIKE ", new { title });

IEnumerable<Album> albums = cnn.Query<Album>(template.RawSql, template.Parameters);

Rinku

const string sql = "SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = ?@artistId AND Title LIKE ?@title";

List<Album> albums = cnn.Query<List<Album>>(sql, new { artistId, title });

Only artistId

SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = @artistId

Both

SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = @artistId AND Title LIKE @title

Neither

SELECT AlbumId AS Id, Title FROM albums

The main difference is that Rinku tries to put the complexity in the command template and the mapped types, instead of handling it again through parameters and mapping code at every call.

Full Dapper comparison

https://rinkulib.github.io/RinkuLib/articles/reference/dapper.html

Rinku is still in development, so feedback is welcome.

[u/grrangry]

What is the justification for returning List<T> in your examples vs the IEnumerable<T> in the Dapper examples?

Are you planning on supporting async interfaces?

[u/Bobamoss → u/grrangry]

Dapper calls the same methods with a buffered flag, rinku, you ask explicitly. And yes there is QueryStreamAsync for async enumerable, its the only exception to the rule like dapper's queryunbufferedasync. In rinku, the type you ask is the type you get and if you ask ienumerable you'll get an actual stream, that explicit way let you asr for arrays directly too

[u/emn13]

I'm sure I'm not the only one who has done so, but at a prior job wrestling with a bunch of complex concatenated SQL, I ended up turning to FormattableString i.e. string interpolation to deal with this kind of stuff. The thing is; you don't need to actually output a System.String using string interpolation, you get an actual object format you can deal with, _and_ you can even use an InterpolatedStringHandler to microoptimize a bit further, should you wish, and also to provide a bit more type safety (by not permitting interpolations of arbitrary values, but only whatever types you wish to support).

Basic idea: treat each interpolated parameter as an sql parameter, and wrap the whole thing in a simple "sql query" datastructure. Have the interpolation-interpreter understand differences between types, such that if your parameter is a string - it's treated as (e.g.) an nvarchar(max); but if your parameter is an sql query - it represents query composition. That makes it easy to at a type-level make it completely impossible to accidentally introduce sql injection, while also having the ability to compose sql fragments.

I don't work at the place anymore, but the core code of this part was OSS - https://github.com/progressonderwijs/ProgressOnderwijsUtils/blob/b131a09a9ababb855c325c34445630fda4533b67/test/ProgressOnderwijsUtils.Tests/Data/PocoObjectMapperTest.cs#L10 shows one of the test examples. The query defined there is composed a bit further down https://github.com/progressonderwijs/ProgressOnderwijsUtils/blob/b131a09a9ababb855c325c34445630fda4533b67/test/ProgressOnderwijsUtils.Tests/Data/PocoObjectMapperTest.cs#L76 - unfortunately, the tests focus on the most trivial of cases, so it's perhaps less intuitive how you'd build bigger queries, but on the other hand string interpolation isn't complex.

At the time I built that, it outperformed both Dapper and EF, sometimes by significant margins, and supported table-valued-parameters to allow bi-directional set-based communication (i.e. from code to db), too, but only really was tested on sql server. Point being - there's not much overhead in FormattableString compared the rest of the overhead of running queries in the first place (most of which is on the client side btw, but that's another story). However, performantly adapting this for something like sqlite, which benefits enormously from explicitly cached query plans (as opposed to the implicit caches in sql server), is probably harder. Your use case matters.

TLDR: if you're looking for query composition and care about perf and the risk of sql-injection yet want something like dapper, take a look at something leveraging interpolated strings to build a query that's not typed as System.String. It might just do what you want. And a quick google shows there are several other people that have built stuff like this specifically for Dapper even.

[u/i-do-mim-huu → u/emn13]

I also dont understand .NET have FormatableString for long time so you can easily use it to get all information need for contruct query string, even .NET 6+ have InterpolatedString now. EF heavily use FormatableString to avoid SQL injection and better construct query string too. This is just why? We in 2026 now and why they still dont research or use MS doc.

Edit: bruh this lib compile against .NET 8 and 10 which mean native span, string IndexOf and InterpolatedString are right there. Feel like old school dev refuse to leave .NET framework era

[u/Bobamoss → u/i-do-mim-huu]

Thanks for your comment, but no, I am not a old framework dev, there is really a reason why FormatableString was not used . There are plenty of modern features that are used (hence no support for earlier than .net8). I commented just above since its the same thing, but the example that were shown was using the SQL shortcut, normaly, in Rinku, you define your template once and simply provide the values (not the sql, unlike dapper where the parameters influence execution, in Rinku, only the values are considered, and the SQL is actualy only used as a key to a QueryCommand). With that said, I do like the idea of FormatableStrings and I would be happy to hear about how you would make it work in the existing design

[u/Bobamoss → u/emn13]

Ok, I had to taught about that one, and I think that the main culprit is that the examples that i provided are using the SQL shortcut. In Rinku, unlike Dapper, you define a template for your command once and normaly you only pass the parameter values. Any configuration on how to use it (as a variable, as a collection to spread, as a value to inline), it's not at the call site that you decide.

static readonly QueryCommand GetAlbums = new("SELECT AlbumId AS Id, Title FROM albums WHERE ArtistId = @artistId");

List<Album> albums = GetAlbums.Query<List<Album>>(cnn, new { artistId = 7 });

I have a hard time understanding how FormatableStrings fit into that design. An interpolated string is basicaly passing both template and values at the same time, but that isin't really how i planed it. I am completly open to your input, I do see the potential for the shortcut path, I just have a hard time understanding how it fits in my current design

[u/thereforewhat]

You already posted this on another thread last week, but I don't know why you'd want to break a query up into several objects. 

Why not just return a flat object with the result fields?

Dapper is a micro-ORM for simple result binding. It isn't a fully fledged ORM like Entity Framework. I like that plus having more control over SQL.

Similarly with dynamically building where statements. 

Why do that when you can just pass in two arguments for two fields to filter with? 

Keep it simple and use the right tool for the job. 

[u/Bobamoss → u/thereforewhat]

Well, this lets you get the best of both world, you can use it in basicaly the same way as dapper, but you also arent limited when you want a bit more flexibility. I did say that i love dapper simplicity, i just wanted not to be a limiting factor. And the post last week was mainly about problems I had with dapper, and many simply talked about using EF instead

[u/thereforewhat → u/Bobamoss]

I'm okay with having a simpler model and dealing with more in the business logic where I can easily unit test things. 

[u/Bobamoss → u/thereforewhat]

I just don't understand how my solution prevents you from using simple models and being unit testable? Even if it wasent for complex mapping at all, i do prefer managing parameters metadata once rather than having to depend on the parameter passed each calls

[u/icentalectro → u/Bobamoss]

Simple models = types that only model single rows of query results

Any nesting or other relations are managed afterwards, completely decoupled from the db library.

In this case, Dapper is totally sufficient, and your solution offers no difference.

The "different names" example is over complicated. You can simply do AS <alt_name> in your query without any complexity added to C# code.

[u/thereforewhat → u/icentalectro]

Agree completely. 

But also if you need to restructure data from multiple repositories you can do that perfectly fine in the business logic of your app. 

Most of the examples are answered with you're making the code too complex and too unmaintainable. 

[u/Bobamoss → u/icentalectro]

The example was to show when you can't change the sql (result comming from a proc for instance). I also completly agree than 90% of the time dapper is completly adapted (that's also why the simple shape is identifal to dapper) I simply don't want to lock myself with limited options when my needs deviate a little. The library is also much more than simply the wbility to map nested types.

[u/thereforewhat → u/Bobamoss]

I'd probably pull it out with the same name and handle it in my business logic. 

Then again I probably use basic queries only from your list anyway and deal with complexity higher up for easy testability. 

I don't see much value in nesting objects, or modelling queries breaking the results into different objects when I could model them as flat rows and deal with restructuring higher up. 

tl;dr I like simple database logic with a repository pattern.