Mapping a custom object to multiple columns by type handler in Dapper

Question

I'm facing a problem which has been discussed here multiple times, but unfortunately none of the answers or hints work for me.

I want to use different custom types (for example money or recordlink).

In the database I would store the different attributes of these custom types into multiple columns (example for Money: YearlyIncome.Amount, YearlyIncome.CurrencyCode).

All the hints I've found try to solve the problem using "spliton" within the query. But I would prefer a solution using a type handler, so I don't have to manually add it to every query.

I already tried to store the information in a single database column using a kind of separator between the properties. This basically works fine using a custom type handler - but the database at the end looks "ugly".

public class Recordlink
{
    public Guid Id { get; set; }
    public string Type { get; set; }
    public string Name { get; set; }
    ...
}

public class Money
{
    public decimal Amount { get; set; }
    public string CurrencyCode { get; set; }
    ...
}

public class Contact
{
    Guid Id {get;set;}
    Money YearlyIncome {get;set;}
    Recordlink Company {get;set;}
}

I would like to achieve that when querying a list of contacts, I would just be able to access for example the property contacts[0].Company.Id (of course with a previous NULL check).

Because of so many different hints and answers to similar questions, I'm not sure if this is possible or not.

But even if this is not possible as I want to - I would prefer to know this instead of searching for ages for the solution.

Thanks and Regards

Markus

Answer 1

To the best of my knowledge you cannot do this in the way you are outlining here.

A standard custom typehandler looks like this:

public class MyTypeHandler : SqlMapper.TypeHandler<MyType>
{
    public override MyType Parse(object value)
    {
        return ...;
    }

    public override void SetValue(System.Data.IDbDataParameter parameter, MyType value)
    {
        parameter.Value = ...;
    }
}

So, the Parse method maps one item from the result row to your custom type. The SetValue method maps one instance of your custom type to one database parameter. Mapping multiple items of the result row to one object is not part of the possibilities.

In your shoes, instead of inventing my own property representation, I would serialize Money to JSON and save that in one column in the table. You could make a custom handler for that.

That could look something like this:

public class MoneyTypeHandler : SqlMapper.TypeHandler<Money>
{
    public override Money Parse(Type destinationType, object value)
    {
        return JsonConvert.DeserializeObject(value.ToString(), destinationType);
    }

    public override void SetValue(IDbDataParameter parameter, Money value)
    {
        parameter.Value = (value == null) ? (object)DBNull.Value : JsonConvert.SerializeObject(value);
        parameter.DbType = DbType.String;
    }
}