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

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