Dapper mappings, which is best?

Like many, our Database columns look like this customer_id. In CSharp, guidelines suggest using CustomerId for properties.

The first thing people will tell you is to just alias the column in SQL. IE: SELECT customer_id AS CustomerId .... This works great... unless you are calling a SPROC. In many environments, making changes to SPROCS that could be called by other SPROCS or countless downstream apps is completely out of the question.

There are 3 ways I have learned to map.

1. Use dynamic.
2. Use Dapper.FluentMap
3. Use custom [Column] attribute

1. Use dynamic

var queryResult = await connection
    .QueryAsync(
        new CommandDefinition(
            commandText: sql,
            parameters: p,
            commandType: CommandType.StoredProcedure,
            cancellationToken: cancellationToken));

return queryResult?.Select(x => new SponsorGuestRequest
{
    CustomerId = x.customer_id,
}).ToList();

Above, x is dynamic, which is what .QueryAsync() returns if you do not give it a type.

Pros:

No external libs, no additional configuration.

Cons:

Not using the Generic QueryAsync<> means you're manually doing all the mapping. You could forget to map a column. This might as well be a step above ADO.NET if you aren't using Dapper to turn SQL into POCOs.

2. Use Dapper.FluentMap

public class CustomerMap : EntityMap<Product>
{
    public CustomerMap()
    {
        Map(p => p.CustomerId)
            .ToColumn("customer_id");
    }
}

FluentMapper.Initialize(config =>
{
    config.AddMap(new CustomerMap());
});

Pros:

Very familiar syntax to anyone who has written mappers, or used FluentValidation, or used Entity Framework.

Cons:

You have to remember to call .Initialize() and provide all entity maps, you could maybe use Scrutor to scan and auto-register them? Mapping logic is now less discoverable and spread out from the classes themselves. Registration is static and global, not sure how to explain it but AutoMapper has me inject IMapper. Doing any sort of global static setup of dependencies feels dirty in 2024.

3. Use custom [Column] attribute

// Via https://stackoverflow.com/questions/8902674/manually-map-column-names-with-class-properties
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))
            })
    {
    }
}

public class Customer
{
    [Column("customer_id")]
    public int CustomerId { get; set; }
}

Pros:

Highly familiar [Column()] syntax. Highly discoverable, mapping is right on the model.

Cons:

Custom code must now be maintained, and probably tested. Why is this not just in Dapper?

Personally, I like the [Column()] feature. What are you using?

Comments

[Stefan Uzunov]

I use:

public class Model
    {
        private int? request_status_id { get; set; } //db column name
        private int? queue_id { get; set; } //db column name
        public int? RequeStstatusId => request_status_id;
        public int? QueueId => queue_id;
    }

[Ricardo]

Im still using mapping declarative. One by one.
Boring hahaha