首页 / .NET / 正文

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

11428 1评论 发布于: 2018-01-08 读完约需18分钟

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

版权声明:本作品系原创,版权归码友网所有,如未经许可,禁止任何形式转载,违者必究。

上一篇: .NET[C#]Dapper中如何实现SELECT * FROM TABLE WHERE Id IN (...) 的条件查询?

下一篇: .NET[C#]Dapper中如何使用事务进行数据库的操作?

本文永久链接码友网 » .NET[C#]Dapper中数据表的字段(列)与实体属性不一致时,如何手动配置它们之间的映射?

分享扩散:

发表评论

登录用户才能发表评论, 请 登 录 或者 注册

热门评论

961782886 (2018-05-07 21:53)

你好,文章中第二种方法没看懂,在查询的时候还是不行,查询出来的list个数没问题,但是没数据所有属性都是null或是0。请问是我这边的问题吗
回复 赞(0)