How to use attribute mapping with Dapper?

c# dapper system.reflection

Question

I attempt to use the Column Attributes to map my Id fields, but for some reason it doesn't appear to work, and I'm not sure why. To show what I'm doing, I built up a test project.

I first have my two entities:

Object Table 1

using System.Data.Linq.Mapping;

namespace DapperTestProj
{
    public class Table1
    {
        [Column(Name = "Table1Id")]
        public int Id { get; set; }

        public string Column1 { get; set; }

        public string Column2 { get; set; }

        public Table2 Table2 { get; set; }

        public Table1()
        {
            Table2 = new Table2();
        }
    }
}

, also Table2

using System.Data.Linq.Mapping;

namespace DapperTestProj
{
    public class Table2
    {
        [Column(Name = "Table2Id")]
        public int Id { get; set; }

        public string Column3 { get; set; }

        public string Column4 { get; set; }
    }
}

I have two tables in my database with the names Table1 and Table2. With the exception of Table1 having a column entitled Table2Id and there being a foreign key between Table1.Table2Id and Table2.Id, both tables have columns with names that are identical to the entities.

There is also a single entry in each of the two tables, and that record has the ID 2.

The next thing I do is to run a dapper query, which ought to produce an object of type Table1. The property Table1.Id and Table1.Table2.Id remain zero despite the fact that this works (default integer). I anticipated that the column characteristics would transfer to the Id fields, however this is obviously not the case.

I'm running the following query and mapping in code:

private Table1 TestMethod(IDbConnection connection)
{
    var result = connection.Query<Table1, Table2, Table1>(
        @"SELECT 
             T1.Id as Table1Id, 
             T1.Column1 as Column1,
             T1.Column2 as Column2,
             T2.Id as Table2Id,
             T2.Column3 as Column3,
             T2.Column4 as Column4
          FROM Table1 T1 
          INNER JOIN Table2 T2 ON T1.Table2Id = T2.Id",
        (table1, table2) =>
            {
                table1.Table2 = table2;
                return table1;
            },
        splitOn: "Table2Id"
        ).SingleOrDefault();

    return result;
}

Currently, I have the option of renaming the entities' two Id property columns to Table1Id and Table2Id, but I prefer Id due to the more logical syntax, such as Table1.Id rather than Table1.Table1Id. I thus pondered if what I desire is achievable here and, if so, how.

Edit:

This is what I discovered: manually map the class attributes to the column names

Additionally, using the FallBackTypeMapper class and the ColumnAttributeTypeMapper class from Kaleb Pederson's initial post's code, attributes may be used as necessary. All that must be done is include the necessary classes in the typemapping by using:

SqlMapper.SetTypeMap(typeof(Table1), new ColumnAttributeTypeMapper<Table1>());
SqlMapper.SetTypeMap(typeof(Table2), new ColumnAttributeTypeMapper<Table2>());

But when more entities are added, the list will grow. Additionally, each class has be be added to the list, so I was wondering whether Reflection could automatically and more broadly do this. I discovered a piece of code that can get all the types:

        const string @namespace = "DapperTestProj.Entities";

        var types = from type in Assembly.GetExecutingAssembly().GetTypes()
                    where type.IsClass && type.Namespace == @namespace
                    select type;

The only issue I now have is determining what code fragment I need to have or need to add to the location where the questionmarks are currently located. I can loop through all the kinds.

        typeList.ToList().ForEach(type => SqlMapper.SetTypeMap(type, 
                               new ColumnAttributeTypeMapper</*???*/>()));

Edit:

I continued looking until I discovered the answer to my last issue:

        typeList.ToList().ForEach(type =>
            {
                var mapper = (SqlMapper.ITypeMap)Activator.CreateInstance(
                    typeof(ColumnAttributeTypeMapper<>)
                        .MakeGenericType(type));
                SqlMapper.SetTypeMap(type, mapper);
            });
1
16
5/23/2017 11:46:17 AM

Accepted Answer

I wish to share the code I discovered and put together with others who are interested in completing the answer.

rather than (abusing) the System. Data.Linq.Mapping. Although there is a very little probability that Microsoft will convert the linq to sql ColumnAttribute class, it could make more sense to design our own ColumnAttribute class for ColumnAttribute:

ColumnAttribute.cs

using System;

namespace DapperTestProj.DapperAttributeMapper //Maybe a better namespace here
{
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property)]
    public class ColumnAttribute : Attribute
    {
        public string Name { get; set; }

        public ColumnAttribute(string name)
        {
            Name = name;
        }
    }
}

The classes FallBackTypeMapper and ColumnAttributeTypeMapper may be found in the article I described earlier:

FallBackTypeMapper.cs

using System;
using System.Collections.Generic;
using System.Reflection;
using Dapper;

namespace DapperTestProj.DapperAttributeMapper
{
    public class FallBackTypeMapper : SqlMapper.ITypeMap
    {
        private readonly IEnumerable<SqlMapper.ITypeMap> _mappers;

        public FallBackTypeMapper(IEnumerable<SqlMapper.ITypeMap> mappers)
        {
            _mappers = mappers;
        }

        public ConstructorInfo FindConstructor(string[] names, Type[] types)
        {
            foreach (var mapper in _mappers)
            {
                try
                {
                    var result = mapper.FindConstructor(names, types);

                    if (result != null)
                    {
                        return result;
                    }
                }
                catch (NotImplementedException nix)
                {
                    // the CustomPropertyTypeMap only supports a no-args
                    // constructor and throws a not implemented exception.
                    // to work around that, catch and ignore.
                }
            }
            return null;
        }

        public SqlMapper.IMemberMap GetConstructorParameter(ConstructorInfo constructor, string columnName)
        {
            foreach (var mapper in _mappers)
            {
                try
                {
                    var result = mapper.GetConstructorParameter(constructor, columnName);

                    if (result != null)
                    {
                        return result;
                    }
                }
                catch (NotImplementedException nix)
                {
                    // the CustomPropertyTypeMap only supports a no-args
                    // constructor and throws a not implemented exception.
                    // to work around that, catch and ignore.
                }
            }
            return null;
        }

        public SqlMapper.IMemberMap GetMember(string columnName)
        {
            foreach (var mapper in _mappers)
            {
                try
                {
                    var result = mapper.GetMember(columnName);

                    if (result != null)
                    {
                        return result;
                    }
                }
                catch (NotImplementedException nix)
                {
                    // the CustomPropertyTypeMap only supports a no-args
                    // constructor and throws a not implemented exception.
                    // to work around that, catch and ignore.
                }
            }
            return null;
        }
    }
}

ColumnAttributeTypeMapper.cs

using System.Linq;
using Dapper;

namespace DapperTestProj.DapperAttributeMapper
{
    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(attribute => attribute.Name == columnName)
                            )
                        ),
                        new DefaultTypeMap(typeof(T)) 
                    })
        {
        }
    }
}

and lastly, the initialization of the mapping with the TypeMapper.cs.

using System;
using System.Linq;
using System.Reflection;
using Dapper;

namespace DapperTestProj.DapperAttributeMapper
{
    public static class TypeMapper
    {
        public static void Initialize(string @namespace)
        {
            var types = from assem in AppDomain.CurrentDomain.GetAssemblies().ToList()
                    from type in assem.GetTypes()
                    where type.IsClass && type.Namespace == @namespace
                    select type;

            types.ToList().ForEach(type =>
            {
                var mapper = (SqlMapper.ITypeMap)Activator
                    .CreateInstance(typeof(ColumnAttributeTypeMapper<>)
                                    .MakeGenericType(type));
                SqlMapper.SetTypeMap(type, mapper);
            });
        }
    }
}

TypeMapper launches at startup. Calling initialize is necessary

TypeMapper.Initialize("DapperTestProj.Entities");

Additionally, you may begin utilizing attributes for entity properties.

using DapperTestProj.DapperAttributeMapper;

namespace DapperTestProj.Entities
{
    public class Table1
    {
        [Column("Table1Id")]
        public int Id { get; set; }

        public string Column1 { get; set; }

        public string Column2 { get; set; }

        public Table2 Table2 { get; set; }

        public Table1()
        {
            Table2 = new Table2();
        }
    }
}
21
10/16/2019 4:19:43 PM

Popular Answer

Although Cornelis' response is accurate, I wanted to provide a recent development. You must also apply these changes as of the most recent Dapper version.SqlMapper.ItypeMap.FindExplicitConstructor() . I'm not sure when this modification was made, but I wanted to mention it in case anybody else comes across this problem and doesn't see that portion of the answer.

from zzz-12 to zzz

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

You may also use theColumnAttribute a class situated in theSystem.ComponentModel.DataAnnotations.Schema namespace for a built-in, non-database/orm-specific version rather than creating your own.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow