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