Dapper: mapping hierarchy and single different property

Question

I really love Dapper's simplicity and possibilities. I would like to use Dapper to solve common challenges I face on a day-to-day basis. These are described below.

Here is my simple model.

public class OrderItem {
    public long Id { get; set; }
    public Item Item { get; set; }
    public Vendor Vendor { get; set; }
    public Money PurchasePrice { get; set; }
    public Money SellingPrice { get; set; }
}

public class Item
{
    public long Id { get; set; }
    public string Title { get; set; }
    public Category Category { get; set; }
}

public class Category
{
    public long Id { get; set; }
    public string Title { get; set; }
    public long? CategoryId { get; set; }
}

public class Vendor
{
    public long Id { get; set; }
    public string Title { get; set; }
    public Money Balance { get; set; }
    public string SyncValue { get; set; }
}

public struct Money
{
    public string Currency { get; set; }
    public double Amount { get; set; }
}

Two challenges have been stumping me.

Question 1: Should I always create a DTO with mapping logic between DTO-Entity in cases when I have a single property difference or simple enum/struct mapping?

For example: There is my Vendor entity, that has Balance property as a struct (otherwise it could be Enum). I haven't found anything better than that solution:

public async Task<Vendor> Load(long id) {
    const string query = @"
        select * from [dbo].[Vendor] where [Id] = @id
    ";

    var row = (await this._db.QueryAsync<LoadVendorRow>(query, new {id})).FirstOrDefault();
    if (row == null) {
        return null;
    }

    return row.Map();
}

In this method I have 2 overhead code: 1. I have to create LoadVendorRow as DTO object; 2. I have to write my own mapping between LoadVendorRow and Vendor:

public static class VendorMapper {
    public static Vendor Map(this LoadVendorRow row) {
        return new Vendor {
            Id = row.Id,
            Title = row.Title,
            Balance = new Money() {Amount = row.Balance, Currency = "RUR"},
            SyncValue = row.SyncValue
        };
    }
}

Perhaps you might suggest that I have to store amount & currency together and retrieve it like _db.QueryAsync<Vendor, Money, Vendor>(...)- Perhaps, you are right. In that case, what should I do if I need to store/retrive Enum (OrderStatus property)?

var order = new Order
{
    Id = row.Id,
    ExternalOrderId = row.ExternalOrderId,
    CustomerFullName = row.CustomerFullName,
    CustomerAddress = row.CustomerAddress,
    CustomerPhone = row.CustomerPhone,
    Note = row.Note,
    CreatedAtUtc = row.CreatedAtUtc,
    DeliveryPrice = row.DeliveryPrice.ToMoney(),
    OrderStatus = EnumExtensions.ParseEnum<OrderStatus>(row.OrderStatus)
};

Could I make this work without my own implementations and save time?

Question 2: What should I do if I'd like to restore data to entities which are slightly more complex than simple single level DTO? OrderItem is beautiful example. This is the technique I am using to retrieve it right now:

public async Task<IList<OrderItem>> Load(long orderId) {
    const string query = @"
            select [oi].*,
                   [i].*,
                   [v].*,
                   [c].*
              from [dbo].[OrderItem] [oi]
              join [dbo].[Item] [i]
                on [oi].[ItemId] = [i].[Id]
              join [dbo].[Category] [c]
                on [i].[CategoryId] = [c].[Id]
              join [dbo].[Vendor] [v]
                on [oi].[VendorId] = [v].[Id]
             where [oi].[OrderId] = @orderId
    ";

    var rows = (await this._db.QueryAsync<LoadOrderItemRow, LoadItemRow, LoadVendorRow, LoadCategoryRow, OrderItem>(query, this.Map, new { orderId }));

    return rows.ToList();
}

As you can see, my question 1 problem forces me write custom mappers and DTO for every entity in the hierarchy. That's my mapper:

private OrderItem Map(LoadOrderItemRow row, LoadItemRow item, LoadVendorRow vendor, LoadCategoryRow category) {
    return new OrderItem {
        Id = row.Id,
        Item = item.Map(category),
        Vendor = vendor.Map(),
        PurchasePrice = row.PurchasePrice.ToMoney(),
        SellingPrice = row.SellingPrice.ToMoney()
    };
}

There are lots of mappers that I'd like to eliminate to prevent unnecessary work.

Answer 1

Is there a clean way to retrive & map Order entity with relative properties like Vendor, Item, Category etc)

You are not showing your Order entity but I'll take your OrderItem as an example and show you that you don't need a mapping tool for the specific problem (as quoted). You can retrieve the OrderItems along with the Item and Vendor info of each by doing the following:

var sql = @"
select oi.*, i.*, v.* 
from OrderItem 
    inner join Item i on i.Id = oi.ItemId
    left join Vendor v on v.Id = oi.VendorId
    left join Category c on c.Id = i.CategoryId";
var items = connection.Query<OrderItem, Item, Vendor, Category, OrderItem>(sql, 
    (oi,i,v,c)=>
    {
      oi.Item=i;oi.Item.Category=c;oi.Vendor=v;
      oi.Vendor.Balance = new Money { Amount = v.Amount, Currency = v.Currency};
      return oi; 
    });

NOTE: The use of left join and adjust it accordingly based on your table structure.