DEV Community

Victorio Berra
Victorio Berra

Posted on

6

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();
Enter fullscreen mode Exit fullscreen mode

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());
});
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

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?

Image of Bright Data

Scale Your Data Needs Effortlessly – Expand your data handling capacities seamlessly.

Leverage our scalable solutions to meet your growing data demands without compromising performance.

Scale Effortlessly

Top comments (2)

Collapse
 
stefan_uzunov_690ff4467fd profile image
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;
    }
Enter fullscreen mode Exit fullscreen mode
Collapse
 
rmaurodev profile image
Ricardo

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

Image of Bright Data

Ensure Data Quality Across Sources – Manage and normalize data effortlessly.

Maintain high-quality, consistent data across multiple sources with our efficient data management tools.

Manage Data

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay