Manually map column names with class properties

Question

I am new to the Dapper micro ORM. So far I am able to use it for simple ORM related stuff but I am not able to map the database column names with the class properties.

For example, I have the following database table:

Table Name: Person
person_id  int
first_name varchar(50)
last_name  varchar(50)

and I have a class called Person:

public class Person 
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Please note that my column names in the table are different from the property name of the class to which I am trying to map the data which I got from the query result.

var sql = @"select top 1 PersonId,FirstName,LastName from Person";
using (var conn = ConnectionFactory.GetConnection())
{
    var person = conn.Query<Person>(sql).ToList();
    return person;
}

The above code won't work as the column names don't match the object's (Person) properties. In this scenario, is there anything i can do in Dapper to manually map (e.g person_id => PersonId) the column names with object properties?

Answer 1

Dapper now supports custom column to property mappers. It does so through the ITypeMap interface. A CustomPropertyTypeMap class is provided by Dapper that can do most of this work. For example:

Dapper.SqlMapper.SetTypeMap(
    typeof(TModel),
    new CustomPropertyTypeMap(
        typeof(TModel),
        (type, columnName) =>
            type.GetProperties().FirstOrDefault(prop =>
                prop.GetCustomAttributes(false)
                    .OfType<ColumnAttribute>()
                    .Any(attr => attr.Name == columnName))));

And the model:

public class TModel {
    [Column(Name="my_property")]
    public int MyProperty { get; set; }
}

It's important to note that the implementation of CustomPropertyTypeMap requires that the attribute exist and match one of the column names or the property won't be mapped. The DefaultTypeMap class provides the standard functionality and can be leveraged to change this behavior:

public class FallbackTypeMapper : SqlMapper.ITypeMap
{
    private readonly IEnumerable<SqlMapper.ITypeMap> _mappers;

    public FallbackTypeMapper(IEnumerable<SqlMapper.ITypeMap> mappers)
    {
        _mappers = mappers;
    }

    public SqlMapper.IMemberMap GetMember(string columnName)
    {
        foreach (var mapper in _mappers)
        {
            try
            {
                var result = mapper.GetMember(columnName);
                if (result != null)
                {
                    return result;
                }
            }
            catch (NotImplementedException nix)
            {
            // the CustomPropertyTypeMap only supports a no-args
            // constructor and throws a not implemented exception.
            // to work around that, catch and ignore.
            }
        }
        return null;
    }
    // implement other interface methods similarly

    // required sometime after version 1.13 of dapper
    public ConstructorInfo FindExplicitConstructor()
    {
        return _mappers
            .Select(mapper => mapper.FindExplicitConstructor())
            .FirstOrDefault(result => result != null);
    }
}

And with that in place, it becomes easy to create a custom type mapper that will automatically use the attributes if they're present but will otherwise fall back to standard behavior:

public class ColumnAttributeTypeMapper<T> : FallbackTypeMapper
{
    public ColumnAttributeTypeMapper()
        : base(new SqlMapper.ITypeMap[]
            {
                new CustomPropertyTypeMap(
                   typeof(T),
                   (type, columnName) =>
                       type.GetProperties().FirstOrDefault(prop =>
                           prop.GetCustomAttributes(false)
                               .OfType<ColumnAttribute>()
                               .Any(attr => attr.Name == columnName)
                           )
                   ),
                new DefaultTypeMap(typeof(T))
            })
    {
    }
}

That means we can now easily support types that require map using attributes:

Dapper.SqlMapper.SetTypeMap(
    typeof(MyModel),
    new ColumnAttributeTypeMapper<MyModel>());

Here's a Gist to the full source code.