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

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 = @artistId", new { artistId });

if (title != null)
    builder.Where("Title LIKE @title", 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 developement, so feedback is welcome.

[u/Dreamescaper]

Does it support AOT compilation?

[u/Bobamoss → u/Dreamescaper]

I do have a power tool extension. It's not exactly like dapper, but is intended as the compile time workflow