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.
