A Practical Guide to Manually Mapping Column Names with Class Properties in Dapper

Introduction

When using Dapper for Object-Relational Mapping (ORM), it is common to encounter discrepancies between database column names and C# class property names. For instance, database tables may use snake_case (e.g., person_id), while C# classes adhere to PascalCase (e.g., PersonId). Such mismatches prevent Dapper from automatically binding data, leading to mapping failures. This article systematically presents several effective solutions, illustrated with practical code demonstrations.

SQL Alias Mapping

The most straightforward and efficient solution involves using aliases in SQL queries to explicitly map database column names to class property names. This method requires no modifications to class definitions or Dapper configurations, as mapping is handled directly within the SQL statement. For example, to address the mapping between the Person table and Person class, the following query can be used:

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

In this code, the AS keyword maps the database column person_id to the class property PersonId, with similar mappings for other columns. The advantages of this approach include:

• Simplicity and clarity: No additional configuration or dependencies are required.
• High flexibility: Aliases can be dynamically adjusted based on different query needs.
• Excellent performance: Dapper directly utilizes the aliases for mapping, incurring no runtime overhead.

However, when dealing with multiple queries or complex objects, repeatedly writing aliases may become tedious. In such cases, the following advanced solutions can be considered.

Custom Type Mapper

For scenarios requiring unified mapping rules, a custom type mapper can be created by implementing the ITypeMap interface. Dapper provides the CustomPropertyTypeMap class, which supports mapping based on attribute annotations. For example, properties can be annotated with ColumnAttribute:

public class Person
{
    [Column(Name = "person_id")]
    public int PersonId { get; set; }
    [Column(Name = "first_name")]
    public string FirstName { get; set; }
    [Column(Name = "last_name")]
    public string LastName { get; set; }
}

Subsequently, configure Dapper to use the custom mapper:

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

This method centralizes mapping logic but requires that ColumnAttribute is properly defined, and note that Dapper does not natively depend on this attribute; it must be implemented via a custom mapper.

Fallback Type Mapper

To enhance flexibility further, a fallback mapper can be implemented that prioritizes attribute-based mapping and falls back to default rules if unsuccessful. Below is an example implementation:

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) { }
        }
        return null;
    }

    public ConstructorInfo FindExplicitConstructor()
    {
        return _mappers
            .Select(mapper => mapper.FindExplicitConstructor())
            .FirstOrDefault(result => result != null);
    }
}

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))
            })
    { }
}

Configuration usage:

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

This approach combines the convenience of attribute-based mapping with the compatibility of default mapping, suitable for scenarios with mixed naming conventions.

Using Dynamic Type and LINQ

An alternative method involves using dynamic types (dynamic) combined with LINQ for manual mapping:

var sql = @"SELECT 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;
}

This method explicitly constructs the target object using LINQ's Select method after the query returns dynamic objects. The advantage is that no mapper configuration is needed, but it may sacrifice some performance, and the code volume increases with the number of properties.

Global Underscore Matching Configuration

For common conversions between snake_case and PascalCase, Dapper offers a global configuration option:

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

When enabled, Dapper automatically converts underscore-separated column names (e.g., person_id) to PascalCase properties (e.g., PersonId). This is a lightweight solution ideal for projects with consistent naming conventions.

Conclusion and Recommendations

This article has detailed multiple methods for handling column name and property name mismatches in Dapper:

• SQL Alias Method: Best for simple or ad-hoc mappings, recommended when queries change infrequently.
• Custom Mapper: Suitable for complex projects requiring centralized mapping rule management.
• Dynamic Type and LINQ: Ideal for rapid prototyping or small-scale data transformations.
• Global Underscore Matching: Fits scenarios with uniform naming standards.

In practice, developers should choose the appropriate solution based on project requirements. For most cases, the SQL alias method is preferred due to its simplicity and efficiency. For enterprise-level applications, custom mappers offer better maintainability. By leveraging these techniques effectively, one can maximize Dapper's flexibility and performance benefits.