Can I specify DB column names for dapper-dot-net mappings?

Question

Is there a way with dapper-dot-net to use an attribute to specify column names that should be used and not the property name?

public class Code
{
    public int Id { get; set; }
    public string Type { get; set; }
    // This is called code in the table.
    public string Value { get; set; }
    public string Description { get; set; }
}

I'd like to be able to name my properties whatever I choose. Our database has no consistent naming convention.

If not with dapper, are there any additional similar options?

`Our database has no consistent naming convention.` that's a good recipe for a disaster! :) Sooner or later... — walther, Aug 29 '12 at 17:43

Void Ray · Accepted Answer · 2015-11-18T21:06:39.807

26

You can also check out Dapper-Extensions.

Dapper Extensions is a small library that complements Dapper by adding basic CRUD operations (Get, Insert, Update, Delete) for your POCOs.

It has an auto class mapper, where you can specify your custom field mappings. For example:

public class CodeCustomMapper : ClassMapper<Code>
{
    public CodeCustomMapper()
    {
        base.Table("Codes");
        Map(f => f.Id).Key(KeyType.Identity);
        Map(f => f.Type).Column("Type");
        Map(f => f.Value).Column("Code");
        Map(f => f.Description).Column("Foo");
    }
}

Then you just do:

using (SqlConnection cn = new SqlConnection(_connectionString))
{
    cn.Open();
    var code= new Code{ Type = "Foo", Value = "Bar" };
    int id = cn.Insert(code);
    cn.Close();
}

Keep in mind that you must keep your custom maps in the same assembly as your POCO classes. The library uses reflection to find custom maps and it only scans one assembly.

Update:

You can now use SetMappingAssemblies to register a list of assemblies to scan:

DapperExtensions.SetMappingAssemblies(new[] { typeof(MyCustomClassMapper).Assembly });

edited Nov 18 '15 at 21:06

answered Aug 29 '12 at 18:45

Void Ray

9,849
4
33
53

I was just about to ask the same thing. I created the ClassMapper, but how do you 'register' it with the system to be used? It's not auto-registering because my constructor never gets called. – Mark A. Donohoe Nov 17 '15 at 22:50
DapperExtensions has no option of handling joins on tables, correct? – Thomas Mulder Nov 04 '16 at 13:01
No, but you can always use Dapper for more complex queries. – Void Ray Nov 07 '16 at 15:07
Dapper has a `CustomPropertyTypeMap` class you can use for this with a custom attribute, see e.g. this answer here https://stackoverflow.com/a/12615036/430885. Also, in the case you have a consistent naming in your db but use underscores instead of Pascal-style naming, there is a handy setting for this: `Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;` (doesn't apply to OP's inconsistently named db, but nevertheless was my reason for ending up on this post... :) ) – Frederik Struck-Schøning Jul 12 '18 at 14:25

score 21 · Answer 2 · answered Aug 29 '12 at 17:41

21

If you are using a select statement directly or in a procedure you can just alias the columns.

SELECT code as Value FROM yourTable

answered Aug 29 '12 at 17:41

Rick

1,755
1
15
22

score 4 · Answer 3 · answered Aug 25 '13 at 14:54

Another approach is to just manually map it with the dynamic result.

var codes = conn.Query<dynamic>(...sql and params here...)
                 .Select<dynamic,Code>(s=>new Code{Id = s.Id, Type = s.Type, Value = s.code, Description = s.Description});

Clearly this introduces type-safety scenarios because you are querying on dynamic. Also, you have to manually map columns which is a bummer.

However, I tend to like this approach because it's so darned transparent. You can cast if need be (as is the case with Enums), and basically just do whatever it is you need to do to go from the db recordset to your properties.

score 2 · Answer 4 · answered Feb 01 '13 at 07:45

For selects, you can add constructors to your classes to perform the mapping. The constructor parameter names must match the table columns.

Below is an example from the source. The table will be correctly mapped to the class.

Table:

CREATE TABLE #Users (Id int, Name varchar(20))

Class:

   class UserWithConstructor
    {
        public UserWithConstructor(int id, string name)
        {
            Ident = id;
            FullName = name;
        }
        public int Ident { get; set; }
        public string FullName { get; set; }
    }

Really, Dapper throws an error complaining about no default constructor that is needed to materialize the values? — Oliver, Jan 18 '16 at 10:04

Can I specify DB column names for dapper-dot-net mappings?

4 Answers4

Linked