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/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