How to map column names to class properties with Dapper

Last week, I used Dapper instead of NHibernate to perform a simple processing on a table in our Oracle database. Since this is an ancient table, the column names are "old-fashioned" and I don't want to end up with overly convoluted property names, even if it's only for a one-shot process.

Let's say my table has the following structure:

Contact_Clients
---------------
Contact_Cli_ID          Number
Clt_ID                  Number
Contact_Cli_Nom         Varchar2(40)
Contact_Cli_Prenom      Varchar2(40)
Contact_Tel_Fixe        Varchar2(24)
Contact_Cli_Fax         Varchar2(24)
Contact_Cli_Mail        Varchar2(127)
Contact_Portable        Varchar2(24)
...

I have to define the C# class below:

public class Contact
{
  public int Contact_Cli_ID { get; set; }
  public int Clt_ID { get; set; }
  public string Contact_Cli_Nom { get; set; }
  public string Contact_Cli_Prenom { get; set; }
  public string Contact_Tel_Fixe { get; set; }
  public string Contact_Cli_Fax { get; set; }
  public string Contact_Cli_Mail { get; set; }
  public string Contact_Portable { get; set; }
}

But I'd prefer to use pretty names for its properties:

public class Contact
{
  public int Contact_ID { get; set; }
  public int Client_ID { get; set; }
  public string Nom { get; set; }
  public string Prenom { get; set; }
  public string Telephone { get; set; }
  public string Telecopie { get; set; }
  public string Mail { get; set; }
  public string Portable { get; set; }
}

Usually I go with SQL aliases to work around this problem. So, instead of doing a regular SELECT * FROM Contact_Clients ..., I end up writing:

var sql = "SELECT Contact_Cli_ID AS Contact_ID,
                  Clt_ID AS Client_ID,
                  Contact_Cli_Nom AS Nom,
                  Contact_Cli_Prenom AS Prenom,
                  Contact_Tel_Fixe AS Telephone,
                  Contact_Cli_Fax AS Telecopie,
                  Contact_Cli_Mail AS Mail,
                  Contact_Portable AS Portable
           FROM   Contact_Clients
           ...";

Pro: I can limit my code to only useful columns, although in this case almost all columns are necessary.

Con: I've been using Dapper for a few years now, it's time to search for some improvments to redefine the names of the columns.

It's a bit of a sensitive subject and probably not really a priority. However, there is a discussion about the right way to do this, by configuration or with attributes: [Column] and [Table] Attributes .

In the meantime, a Stack Overflow question (Manually map column names with class properties) allowed me to find a quite simple solution to implement. Taken from the Dapper Tests code (and thus tested :), it uses the [Description] attribute from System.ComponentModel.

Thanks to this article, I can write the following code:

using System.ComponentModel;
...

class Program
{

  static void Main(string[] args)
  {
    var cnx_string = @"Data Source=XXXX;User ID=YYYY;Password=ZZZZ";
    var db = new OracleConnection(cnx_string);
    db.Open();

    var map = new CustomPropertyTypeMap(typeof(Contact), (type, columnName)
      => type.GetProperties().FirstOrDefault(prop => GetDescriptionFromAttribute(prop) == columnName.ToLower()));
    Dapper.SqlMapper.SetTypeMap(typeof(Contact), map);

    var sql = "SELECT * FROM Contact_Clients WHERE Contact_Cli_ID = 1234";
    var c = db.QueryFirst<Contact>(sql);
    Console.WriteLine(c.Nom + " " + c.Prenom);

    db.Close();
    Console.ReadLine();
  }

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

    var attrib = (DescriptionAttribute)Attribute.GetCustomAttribute(member, typeof(DescriptionAttribute), false);
    return (attrib?.Description ?? member.Name).ToLower();
  }

}

public class Contact
{
  [Description("Contact_Cli_ID")]
  public int Contact_ID { get; set; }
  [Description("Clt_ID")]
  public int Client_ID { get; set; }
  [Description("Contact_Cli_Nom")]
  public string Nom { get; set; }
  [Description("Contact_Cli_Prenom")]
  public string Prenom { get; set; }
  [Description("Contact_Tel_Fixe")]
  public string Telephone { get; set; }
  [Description("Contact_Portable")]
  public string Portable { get; set; }
  [Description("Contact_Cli_Mail")]
  public string Mail { get; set; }
}

Thinking about it, I'm not sure it's more elegant than SQL aliases... But new!

---

This post was originally published on blog.pagesd.info.
Cover image : Demoiselle du téléphone.

Comments

[Evaldas Buinauskas]

I do prefer to do it slightly different by keeping classes clean. This code could still break if someone changes alias and keeps description attribute untouched.

You can assign correct aliases by using nameof(Class.PropertyName) within your query string. This guarantees that column names are mapped correctly and makes refactoring so much easier in case you want to change a property name.

var sql = $@"
    SELECT Contact_Cli_ID AS {nameof(Contact.Contact_ID)},
        Clt_ID AS {nameof(Contact.Client_ID)},
        Contact_Cli_Nom AS {namoef(Contact.Nom)},
        Contact_Cli_Prenom AS {nameof(Contact.Prenom)},
        Contact_Tel_Fixe AS {nameof(Contact.Telephone)},
        Contact_Cli_Fax AS {nameof(Contact.Telecopie)},
        Contact_Cli_Mail AS {nameof(Contact.Mail)},
        Contact_Portable AS {nameof(Contact.Portable)}
    FROM Contact_Clients
    ...";

Then your class can stay as is.
🙂

public class Contact
{
  public int Contact_ID { get; set; }
  public int Client_ID { get; set; }
  public string Nom { get; set; }
  public string Prenom { get; set; }
  public string Telephone { get; set; }
  public string Telecopie { get; set; }
  public string Mail { get; set; }
  public string Portable { get; set; }
}

[Michel]

Yes. I also don't think it's really worth it, especially since I only use Dapper for small conversion application. But it was fun to try new things.

By the way, I love the nameof(Class.PropertyName) thing. Thanks.

[Evaldas Buinauskas]

Might be an overkill, yep.

A few days ago I had to do quite a big refactoring and queries were constructed exactly like this, refactoring was a breeze 🙂

Cheers!