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.

Answer 2

This works fine:

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

Dapper has no facility that allows you to specify a Column Attribute, I am not against adding support for it, providing we do not pull in the dependency.

Answer 3

For some time, the following should work:

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

Answer 4

I do the following using dynamic and LINQ:

    var sql = @"select top 1 person_id, first_name, last_name from Person";
    using (var conn = ConnectionFactory.GetConnection())
    {
        List<Person> person = conn.Query<dynamic>(sql)
                                  .Select(item => new Person()
                                  {
                                      PersonId = item.person_id,
                                      FirstName = item.first_name,
                                      LastName = item.last_name
                                  }
                                  .ToList();

        return person;
    }

Answer 5

Here is a simple solution that doesn't require attributes allowing you to keep infrastructure code out of your POCOs.

This is a class to deal with the mappings. A dictionary would work if you mapped all the columns, but this class allows you to specify just the differences. In addition, it includes reverse maps so you can get the field from the column and the column from the field, which can be useful when doing things such as generating sql statements.

public class ColumnMap
{
    private readonly Dictionary<string, string> forward = new Dictionary<string, string>();
    private readonly Dictionary<string, string> reverse = new Dictionary<string, string>();

    public void Add(string t1, string t2)
    {
        forward.Add(t1, t2);
        reverse.Add(t2, t1);
    }

    public string this[string index]
    {
        get
        {
            // Check for a custom column map.
            if (forward.ContainsKey(index))
                return forward[index];
            if (reverse.ContainsKey(index))
                return reverse[index];

            // If no custom mapping exists, return the value passed in.
            return index;
        }
    }
}

Setup the ColumnMap object and tell Dapper to use the mapping.

var columnMap = new ColumnMap();
columnMap.Add("Field1", "Column1");
columnMap.Add("Field2", "Column2");
columnMap.Add("Field3", "Column3");

SqlMapper.SetTypeMap(typeof (MyClass), new CustomPropertyTypeMap(typeof (MyClass), (type, columnName) => type.GetProperty(columnMap[columnName])));

Answer 6

An easy way to achieve this is to just use aliases on the columns in your query.

If your database column is PERSON_ID and your object's property is ID, you can just do

select PERSON_ID as Id ...

in your query and Dapper will pick it up as expected.

Answer 7

Taken from the Dapper Tests which is currently on Dapper 1.42.

// custom mapping
var map = new CustomPropertyTypeMap(
                 typeof(TypeWithMapping), 
                 (type, columnName) => 
                        type.GetProperties().FirstOrDefault(prop => 
                                GetDescriptionFromAttribute(prop) == columnName));
Dapper.SqlMapper.SetTypeMap(typeof(TypeWithMapping), map);

Helper class to get name off the Description attribute (I personally have used Column like @kalebs example)

static string GetDescriptionFromAttribute(MemberInfo member)
{
   if (member == null) return null;

   var attrib = (DescriptionAttribute) Attribute.GetCustomAttribute(
                         member,
                         typeof(DescriptionAttribute), false);

   return attrib == null ? null : attrib.Description;
}

Class

public class TypeWithMapping
{
   [Description("B")]
   public string A { get; set; }

   [Description("A")]
   public string B { get; set; }
}

Answer 8

Before you open the connection to your database, execute this piece of code for each of your poco classes:

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

Then add the data annotations to your poco classes like this:

public class Section
{
    [Column("db_column_name1")] // Side note: if you create aliases, then they would match this.
    public int Id { get; set; }
    [Column("db_column_name2")]
    public string Title { get; set; }
}

After that, you are all set. Just make a query call, something like:

using (var sqlConnection = new SqlConnection("your_connection_string"))
{
    var sqlStatement = "SELECT " +
                "db_column_name1, " +
                "db_column_name2 " +
                "FROM your_table";

    return sqlConnection.Query<Section>(sqlStatement).AsList();
}

Answer 9

Messing with mapping is borderline moving into real ORM land. Instead of fighting with it and keeping Dapper in its true simple (fast) form, just modify your SQL slightly like so:

var sql = @"select top 1 person_id as PersonId,FirstName,LastName from Person";

Answer 10

If you're using .NET 4.5.1 or higher checkout Dapper.FluentColumnMapping for mapping the LINQ style. It lets you fully separate the db mapping from your model (no need for annotations)

Answer 11

This is piggy backing off of other answers. It's just a thought I had for managing the query strings.

Person.cs

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

    public static string Select() 
    {
        return $"select top 1 person_id {nameof(PersonId)}, first_name {nameof(FirstName)}, last_name {nameof(LastName)}from Person";
    }
}

API Method

using (var conn = ConnectionFactory.GetConnection())
{
    var person = conn.Query<Person>(Person.Select()).ToList();
    return person;
}

Answer 12

The simple solution to the problem Kaleb is trying to solve is just to accept the property name if the column attribute doesn't exist:

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

Answer 13

The easier way (same as @Matt M's answer but corrected and added fallback to default map)

// override TypeMapProvider to return custom map for every requested type
Dapper.SqlMapper.TypeMapProvider = type =>
   {
       // create fallback default type map
       var fallback = new DefaultTypeMap(type);
       return new CustomPropertyTypeMap(type, (t, column) =>
       {
           var property = t.GetProperties().FirstOrDefault(prop =>
               prop.GetCustomAttributes(typeof(ColumnAttribute))
                   .Cast<ColumnAttribute>()
                   .Any(attr => attr.Name == column));

           // if no property matched - fall back to default type map
           if (property == null)
           {
               property = fallback.GetMember(column)?.Property;
           }

           return property;
       });
   };

Answer 14

for all of you who use Dapper 1.12, Here's what you need to do to get this done:

Add a new column attribute class:

  [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property]

  public class ColumnAttribute : Attribute
  {

    public string Name { get; set; }

    public ColumnAttribute(string name)
    {
      this.Name = name;
    }
  }

Search for this line:

map = new DefaultTypeMap(type);

and comment it out.

Write this instead:

        map = new CustomPropertyTypeMap(type, (t, columnName) =>
        {
          PropertyInfo pi = t.GetProperties().FirstOrDefault(prop =>
                            prop.GetCustomAttributes(false)
                                .OfType<ColumnAttribute>()
                                .Any(attr => attr.Name == columnName));

          return pi != null ? pi : t.GetProperties().FirstOrDefault(prop => prop.Name == columnName);
        });

Answer 15

Kaleb Pederson's solution worked for me. I updated the ColumnAttributeTypeMapper to allow a custom attribute (had requirement for two different mappings on same domain object) and updated properties to allow private setters in cases where a field needed to be derived and the types differed.

public class ColumnAttributeTypeMapper<T,A> : FallbackTypeMapper where A : ColumnAttribute
{
    public ColumnAttributeTypeMapper()
        : base(new SqlMapper.ITypeMap[]
            {
                new CustomPropertyTypeMap(
                   typeof(T),
                   (type, columnName) =>
                       type.GetProperties( BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance).FirstOrDefault(prop =>
                           prop.GetCustomAttributes(true)
                               .OfType<A>()
                               .Any(attr => attr.Name == columnName)
                           )
                   ),
                new DefaultTypeMap(typeof(T))
            })
    {
        //
    }
}

Answer 16

I know this is a relatively old thread, but I thought I'd throw what I did out there.

I wanted attribute-mapping to work globally. Either you match the property name (aka default) or you match a column attribute on the class property. I also didn't want to have to set this up for every single class I was mapping to. As such, I created a DapperStart class that I invoke on app start:

public static class DapperStart
{
    public static void Bootstrap()
    {
        Dapper.SqlMapper.TypeMapProvider = type =>
        {
            return new CustomPropertyTypeMap(typeof(CreateChatRequestResponse),
                (t, columnName) => t.GetProperties().FirstOrDefault(prop =>
                    {
                        return prop.Name == columnName || prop.GetCustomAttributes(false).OfType<ColumnAttribute>()
                                   .Any(attr => attr.Name == columnName);
                    }
                ));
        };
    }
}

Pretty simple. Not sure what issues I'll run into yet as I just wrote this, but it works.

Answer 17

I would suggest solution similar to @liorafar's, but based on dictionaries rather than on dynamics:

using var conn = ConnectionFactory.GetConnection();
var person = conn.Query(sql)
    .Cast<IDictionary<string, object>>()
    .Select(record =>
        new Person
        {
            PersonId = (int)record["person_id"],
            FirstName = (string)record["first_name"],
            LastName = (string)record["last_name"],
        })
    .ToList();

In my opinion, this option is friendlier for refactoring: e.g. you can declare column names as constants or read them from configuration. Additionally, unlike solution with dynamics, it allows to extract method of transforming the dictionary to model instance (instance of Person type) to separate method, which is especially useful for models with many fields.