一、前言
Dapper是.NET下一个micro的ORM,它和Entity Framework或Nhibnate不同,属于轻量级的,并且是半自动的。Dapper只有一个代码文件,完全开源,你可以放在项目里的任何位置,来实现数据到对象的ORM操作,体积小速度快。 使用ORM的好处是增、删、改很快,不用自己写sql,因为这都是重复技术含量低的工作,还有就是程序中大量的从数据库中读数据然后创建model,并为model字段赋值。这些ORM都可以轻松给你搞定。ORM给我们开发带来便利时,性能也是一个让我们不得不考虑的问题。一般的ORM性能和直接写原生的sql比都差不少,但是Dapper性能还很错,甚至和DbHelperSQL方式性能高出很多。
二、问题
在使用Dapper做查询的时候,数据库字段名和Model属性名一一对应时,直接只用dapper方法是没有问题的,比如:
public class UserModel
{
public int UserId { get; set; }
public string Mobile { get; set; }
public string UserName { get; set; }
}
但通常数据库的一些规范中,字段名中的单词之间是通过下划线_来拼接的,如
create_time
那么,在C#程序中,使用Dapper做查询时,如何配置数据表字段(列)和实体类属性之间的映射呢?
若属性名和数据库字段不一致(不区分大小写)则查询不出数据,如果使用EF则可以通过Column特性,建立属性和数据表字段之间的映射关系,Dapper则不行,如何让Dapper也支持通过Column特性映射。
在网上看到其他比较好的解决方案,就是通过SqlMapper.ITypeMap自己手动映射
但是还是没有实现自动映射,于是基于别人的手动映射我做了一套自动映射的方案,放在文章下方,我们先看一下手动映射的方案:
以下内容来自博主 Zdelta
Dapper数据库字段和model属性映射_Zdelta的博客-CSDN博客_dapper映射
Dapper虽然有colmun的特性,但是并不能完成字段-属性映射;
我们需要手动拓展一个colmun特性出来,以完成如EF的绑定。
1,添加一个类ColumnAttributeTypeMapper,用于字段-属性映射:
using Dapper; using System; using System.Collections.Generic; using System.Linq; using System.Reflection; namespace 命名空间 { /// <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); } } }
2,再添加一个类ColumnMapper,用于添加映射关系:
using Dapper; using 引入需要的.Models; namespace 项目命名空间 { public class ColumnMapper { public static void SetMapper() { //数据库字段名和c#属性名不一致,手动添加映射关系 SqlMapper.SetTypeMap(typeof(Books), new ColumnAttributeTypeMapper<Books>()); //每个需要用到[colmun(Name="")]特性的model,都要在这里添加映射 } } }
3,在starup.cs类的中方法注册:
public void ConfigureServices(IServiceCollection services) { services.AddMvc(); services.AddSession(); //调用前面的静态方法,将映射关系注册 ColumnMapper.SetMapper(); }
4,最后就可以在model的属性名上添加特性来映射到数据库字段名了:
using 引入新加的类.Helper; public class Books { [Column(Name = "create_time")] public DateTime CreateTime { get; set; } }
这样我们就可以在所有的不与数据库对应的model中,方便的添加映射关系了!
虽然怎样就可以实现了Dapper数据库字段和model属性映射了,但是每个需要用到[colmun(Name="")]特性的model,都需要ColumnMapper.SetMapper()方法中去增加映射,开发起来还是极其的不爽的,有没有办法可以支持自动注入映射呢
三、我的方案
在程序启动的时候,通过反射查找出所有的用到[colmun(Name="")]特性的model,自动增加映射
话不多说直接看代码
1,添加一个类ColumnAttributeTypeMapper,用于字段-属性映射:
using Dapper;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Reflection;
namespace 命名空间
{
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))
})
{
}
}
/// <summary>
/// 我自定义的映射
/// </summary>
public class ColumnAttributeTypeMapper : FallbackTypeMapper
{
public ColumnAttributeTypeMapper(Type type)
: base(new SqlMapper.ITypeMap[]
{
new CustomPropertyTypeMap(
type,
(type, columnName) =>
type.GetProperties().FirstOrDefault(prop =>
prop.GetCustomAttributes(false)
.OfType<ColumnAttribute>()
.Any(attr => attr.Name == columnName)
)
),
new DefaultTypeMap(type)
})
{
}
public ColumnAttributeTypeMapper(Type type, IEnumerable<PropertyInfo> propertyInfos)
: base(new SqlMapper.ITypeMap[]
{
new CustomPropertyTypeMap(type, (type, columnName) =>
propertyInfos.FirstOrDefault(prop =>
(prop.GetCustomAttribute(typeof(ColumnAttribute),false) as ColumnAttribute)?.Name == columnName
)
),
new DefaultTypeMap(type)
})
{
}
}
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);
}
}
}
2,再添加一个类ColumnMapper,用于通过反射查找出所有的用到[colmun(Name="")]特性的model,自动增加映射
using Dapper;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Reflection;
namespace 命名空间
{
/// <summary>
/// 用于通过反射查找出所有的用到[colmun(Name="")]特性的model,自动增加映射
/// </summary>
public class ColumnMapper
{
/// <summary>
/// 用于通过反射查找出所有的用到[colmun(Name="")]特性的model,自动增加映射
/// </summary>
/// <param name="assemblyName">类型的所在程序集</param>
/// <param name="namespaceName">类型命名空间前缀</param>
public static void RegisterColumnAttributeTypeMapper(string assemblyName, string namespaceName)
{
if (!string.IsNullOrEmpty(assemblyName) && !string.IsNullOrEmpty(namespaceName))
{
//二选其一
//1
//var typeList = FindCustomAttributesTypes(assemblyName, dataMapperNamespace);
//typeList.AsParallel().ForAll(type => SqlMapper.SetTypeMap(type, new ColumnAttributeTypeMapper(type)));
//2
var properties= FindCustomAttributesPropertyInfos(assemblyName, namespaceName);
properties.AsParallel().ForAll(item => SqlMapper.SetTypeMap(item.type, new ColumnAttributeTypeMapper(item.type, item.propertyInfos)));
}
}
/// <summary>
/// 查找所有类型
/// </summary>
/// <param name="assemblyName">类型的所在程序集</param>
/// <param name="namespaceName">类型命名空间前缀</param>
/// <returns></returns>
public static IEnumerable<Type> FindCustomAttributesTypes(string assemblyName, string namespaceName)
{
var assembly = Assembly.Load(assemblyName);
if (assembly == null)
{
throw new ArgumentNullException("FindTypes assembly");
}
var types = assembly.GetTypes()
.Where(type => !string.IsNullOrEmpty(type.Namespace) && (type.Namespace.Equals(namespaceName) || type.Namespace.StartsWith(namespaceName + ".")))
.Where(item =>
{
var propertyInfoList = item.GetProperties();
return propertyInfoList.Any() && propertyInfoList.Any(p => p.GetCustomAttribute(typeof(ColumnAttribute)) != null);
});
if (!types.Any())
{
throw new ArgumentNullException("FindTypes types");
}
return types;
}
/// <summary>
/// 查找所有属性
/// </summary>
/// <param name="assemblyName">类型的所在程序集</param>
/// <param name="namespaceName">类型命名空间前缀</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException"></exception>
public static IEnumerable<(Type type, IEnumerable<PropertyInfo> propertyInfos)> FindCustomAttributesPropertyInfos(string assemblyName, string namespaceName)
{
ConcurrentBag<(Type type, IEnumerable<PropertyInfo>)> properties = new ConcurrentBag<(Type type, IEnumerable<PropertyInfo>)> { };
var assembly = Assembly.Load(assemblyName);
if (assembly == null)
{
throw new ArgumentNullException("FindTypes assembly");
}
assembly.GetTypes()
.Where(type => !string.IsNullOrEmpty(type.Namespace) && (type.Namespace.Equals(namespaceName) || type.Namespace.StartsWith(namespaceName + ".")))
.AsParallel().ForAll(type =>
{
var propertyInfoList = type.GetProperties().Where(p => p.GetCustomAttribute(typeof(ColumnAttribute), false) != null);
if (propertyInfoList.Any())
{
properties.Add((type, propertyInfoList));
SqlMapper.SetTypeMap(type, new ColumnAttributeTypeMapper(type, propertyInfoList));
}
});
return properties;
}
}
}
3,在Program.cs类或starup.cs 的中注册:
namespace Qxq.Framework.Repository.PostgreSql.Test
{
internal class Program
{
static async Task Main(string[] args)
{
ColumnMapper.RegisterColumnAttributeTypeMapper("类型的所在程序集", "类型命名空间前缀");
}
}
}
public void ConfigureServices(IServiceCollection services)
{
ColumnMapper.RegisterColumnAttributeTypeMapper("类型的所在程序集", "类型命名空间前缀");
}
4,最后就可以在model的属性名上添加特性来映射到数据库字段名了:
public class UserModel
{
[Column("user_id")]
public int UserId { get; set; }
public string Mobile { get; set; }
[Column("create_date")]
public DateTime CreateDate { get; set; }
}
没有用到[colmun(Name="")]特性的属性会根据属性名称匹配映射(不区分大小写)
用到[colmun(Name="")]特性的属性会根据colmun中定义的Name去匹配映射
这样我们就可以在所有的不与数据库对应的model的属性中,使用[colmun(Name="")]标记数据库字段明,就可以很方便的添加映射关系了!