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 lt Person gt  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   gt  PersonId  the column names with object properties

User · Answer

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

User · Answer

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 lt Person gt  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

User · Answer

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   gt                        return new CustomPropertyTypeMap typeof CreateChatRequestResponse                    t  columnName    gt  t GetProperties   FirstOrDefault prop   gt                                                return prop Name    columnName    prop GetCustomAttributes false  OfType lt ColumnAttribute gt                                        Any attr   gt  attr Name    columnName                                                                  Pretty simple   Not sure what issues I ll run into yet as I just wrote this  but it works

User · Answer

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    gt              type GetProperties   FirstOrDefault prop   gt                  prop GetCustomAttributes false                       OfType lt ColumnAttribute gt                         Any attr   gt  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 lt SqlMapper ITypeMap gt   mappers       public FallbackTypeMapper IEnumerable lt SqlMapper ITypeMap gt  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   gt  mapper FindExplicitConstructor                 FirstOrDefault result   gt  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 lt T gt    FallbackTypeMapper       public ColumnAttributeTypeMapper             base new SqlMapper ITypeMap                                 new CustomPropertyTypeMap                     typeof T                       type  columnName    gt                         type GetProperties   FirstOrDefault prop   gt                             prop GetCustomAttributes false                                  OfType lt ColumnAttribute gt                                    Any attr   gt  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 lt MyModel gt        Here s a Gist to the full source code

User · Answer

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    gt                      PropertyInfo pi   t GetProperties   FirstOrDefault prop   gt                              prop GetCustomAttributes false                                   OfType lt ColumnAttribute gt                                     Any attr   gt  attr Name    columnName               return pi    null   pi   t GetProperties   FirstOrDefault prop   gt  prop Name    columnName

User · Answer

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 lt string  string gt  forward   new Dictionary lt string  string gt         private readonly Dictionary lt string  string gt  reverse   new Dictionary lt string  string gt          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    gt  type GetProperty columnMap columnName

User · Answer

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    gt  type GetProperties   FirstOrDefault prop   gt      prop GetCustomAttributes false  OfType lt ColumnAttribute gt    Any attr   gt  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 lt Section gt  sqlStatement  AsList

User · Answer

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 lt T A gt    FallbackTypeMapper where A   ColumnAttribute       public ColumnAttributeTypeMapper             base new SqlMapper ITypeMap                                 new CustomPropertyTypeMap                     typeof T                       type  columnName    gt                         type GetProperties  BindingFlags NonPublic   BindingFlags Public   BindingFlags Instance  FirstOrDefault prop   gt                             prop GetCustomAttributes true                                  OfType lt A gt                                    Any attr   gt  attr Name    columnName                                                                     new DefaultTypeMap typeof T

User · Answer

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

User · Answer

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 lt Person gt  person   conn Query lt dynamic gt  sql                                     Select item   gt  new Person                                                                             PersonId   item person id                                        FirstName   item first name                                        LastName   item last name                                                                        ToList             return person

User · Answer

Note that Dapper object mapping isn t case sensitive  so you can name your properties like this  public class Person        public int Person Id   get  set        public string First Name   get  set        public string Last Name   get  set       Or keep the Person class and use a PersonMap    public class PersonMap                        public int Person Id   get  set                public string First Name   get  set                public string Last Name   get  set                public Person Map                  return new Person                  PersonId   Person Id                  FirstName   First Name                  LastName   Last Name                                                          And then  in the query result  var person   conn Query lt PersonMap gt  sql  Select x  gt x Map    ToList

User · Answer

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    gt              type GetProperties   FirstOrDefault prop   gt                  prop GetCustomAttributes false                       OfType lt ColumnAttribute gt                         Any attr   gt  attr Name    columnName     prop Name    columnName

User · Answer

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 lt Person gt  Person Select    ToList        return person

User · Answer

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

User · Answer

Taken from the Dapper Tests which is currently on Dapper 1 42      custom mapping var map   new CustomPropertyTypeMap typeof TypeWithMapping                                         type  columnName    gt  type GetProperties   FirstOrDefault prop   gt  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

User · Answer

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   gt                 create fallback default type map        var fallback   new DefaultTypeMap type          return new CustomPropertyTypeMap type   t  column    gt                      var property   t GetProperties   FirstOrDefault prop   gt                 prop GetCustomAttributes typeof ColumnAttribute                       Cast lt ColumnAttribute gt                        Any attr   gt  attr Name    column                   if no property matched - fall back to default type map            if  property    null                              property   fallback GetMember column   Property                           return property

User · Answer

For some time  the following should work   Dapper DefaultTypeMap MatchNamesWithUnderscores   true

[dapper] Manually map column names with class properties

Examples related to dapper