.NET[C#]Dapper中数据表的字段(列)与实体属性不一致时，如何手动配置它们之间的映射？

.NET[C#]Dapper中数据表的字段(列)与实体属性不一致时，如何手动配置它们之间的映射？

问题描述

比如有如下的数据表结构：
Person:

person_id  int
first_name varchar(50)
last_name  varchar(50)

以及实体类：
Person:

public class Person 
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

在C#程序中，使用Dapper做查询时，如何配置数据表字段(列)和实体类属性之间的映射呢？

方案一

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

方案二

使用 ColumnAttribute 属性
完整的代码片段：

namespace YourNamespace
{
    /// <summary>
    /// Uses the Name value of the <see cref="ColumnAttribute"/> specified to determine
    /// the association between the name of the column in the query results and the member to
    /// which it will be extracted. If no column mapping is present all members are mapped as
    /// usual.
    /// </summary>
    /// <typeparam name="T">The type of the object that this association between the mapper applies to.</typeparam>
    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))
                })
        {
        }
    }

        [AttributeUsage(AttributeTargets.Property, AllowMultiple = true)]
    public class ColumnAttribute : Attribute
    {
        public string Name { get; set; }
    }

      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
                {
                    ConstructorInfo result = mapper.FindConstructor(names, types);
                    if (result != null)
                    {
                        return result;
                    }
                }
                catch (NotImplementedException)
                {
                }
            }
            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)
                {
                }
            }
            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)
                {
                }
            }
            return null;
        }


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

}

调用方法：

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; }
}

方案三

使用 CustomPropertyTypeMap 自定义属性类型映射类，如：

public class ColumnMap
{
    private readonly Dictionary<string, string> forward = new Dictionary<string, string>();
    private readonly Dictionary<string, string> reverse = new Dictionary<string, string>();

    public void Add(string t1, string t2)
    {
        forward.Add(t1, t2);
        reverse.Add(t2, t1);
    }

    public string this[string index]
    {
        get
        {
            // Check for a custom column map.
            if (forward.ContainsKey(index))
                return forward[index];
            if (reverse.ContainsKey(index))
                return reverse[index];

            // If no custom mapping exists, return the value passed in.
            return index;
        }
    }
}

配置列映射关系

var columnMap = new ColumnMap();
columnMap.Add("Field1", "Column1");
columnMap.Add("Field2", "Column2");
columnMap.Add("Field3", "Column3");

SqlMapper.SetTypeMap(typeof (MyClass), new CustomPropertyTypeMap(typeof (MyClass), (type, columnName) => type.GetProperty(columnMap[columnName])));

方案四

查询时使用LINQ

 var sql = @"select top 1 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;
    }