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(sql).ToList(); return person; }
方案二
使用 ColumnAttribute 属性
完整的代码片段：
namespace YourNamespace
{
///
/// Uses the Name value of the 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. /// /// The type of the object that this association between the mapper applies to. public class ColumnAttributeTypeMapper : FallbackTypeMapper { public ColumnAttributeTypeMapper() : base(new SqlMapper.ITypeMap[] { new CustomPropertyTypeMap( typeof(T), (type, columnName) => type.GetProperties().FirstOrDefault(prop => prop.GetCustomAttributes(false) .OfType() .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 _mappers; public FallbackTypeMapper(IEnumerable 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 forward = new Dictionary(); private readonly Dictionary reverse = new Dictionary(); 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 = conn.Query(sql)
.Select(item => new Person() { PersonId = item.person_id, FirstName = item.first_name, LastName = item.last_name } .ToList(); return person; }
http://2sharings.com/2018/dapper-orm-manually-map-column-names-with-class-properties-in-csharp-application
转播
