Dapper 下划线

解决方案

设置静态变量
DefaultTypeMap.MatchNamesWithUnderscores = true;

原理

dapper将字段映射实体，会先创建DufaultTypeMap，在该类中处理Sql查询字段映射逻辑：

1. 先查找属性，完全匹配，如果没有匹配到则判断是否进行匹配下划线，最后判断是否存在属性，存在则返回实体成员属性映射关系类；
2. 如果没有找到属性，就找字段，逻辑同上。

/// <summary>
/// Gets member mapping for column
/// </summary>
/// <param name="columnName">DataReader column name(查找的列)</param>
/// <returns>Mapping implementation</returns>
public SqlMapper.IMemberMap GetMember(string columnName)
{
	 //完全匹配
     var property = Properties.Find(p => string.Equals(p.Name, columnName, StringComparison.Ordinal))
        ?? Properties.Find(p => string.Equals(p.Name, columnName, StringComparison.OrdinalIgnoreCase));
	
	 //判断是否需要进行下划线匹配
     if (property == null && MatchNamesWithUnderscores)
     {
         property = Properties.Find(p => string.Equals(p.Name, columnName.Replace("_", ""), StringComparison.Ordinal))
             ?? Properties.Find(p => string.Equals(p.Name, columnName.Replace("_", ""), StringComparison.OrdinalIgnoreCase));
     }

     if (property != null)
         return new SimpleMemberMap(columnName, property);
     // ... 省略代码
}

 /// <summary>
 /// Should column names like User_Id be allowed to match properties/fields like UserId ?
 /// true  User_Id -> UserId ✔
 /// false User_Id -> UserId X
 /// </summary>
 public static bool MatchNamesWithUnderscores { get; set; }

测试

代码

//带下划线
public class UserSimpleWithUnderscores
{                  
   public string User_Id { get; set; }

   public string User_Name { get; set; }
   //测试全字段配置
   public string Sex { get; set; }
}
//不带下划线
public class UserSimpleNotWithUnderscores
{
   public string UserId { get; set; }
   //重命名 不然会抛异常
   //参考 https://blog.csdn.net/yiquan_yang/article/details/107341252
   public string UName { get; set; }

   public string Sex { get; set; }
}
//带下划线和不带 混合
public class UserSimpleMixtureAndRename
{
   public string User_Id { get; set; }

   public string UserName { get; set; }

   public string ReSex { get; set; }
}
[Fact]
public void QueryUnderscores_Not_MatchNamesWithUnderscores()
{
    var Id = "b4600fb2-61c3-4786-8ba1-9fc5387a8d17";

    var userWith = _userRepository.QueryModel<UserSimpleWithUnderscores>("select user_id,user_name,sex from user where user_id=@Id", new { Id });
    //相同查询参数会报错，这里user_name 改为 u_name
    //参考 https://blog.csdn.net/yiquan_yang/article/details/107341252
    //_userRepository是调用dapper不用管
    var userNotWith = _userRepository.QueryModel<UserSimpleNotWithUnderscores>("select user_id,user_name u_name,sex from user where user_id=@Id", new { Id });
    var userMix = _userRepository.QueryModel<UserSimpleMixtureAndRename>("select user_id,user_name,sex as resex from user where user_id=@Id", new { Id });

    Assert.NotNull(userWith.User_Id);
    Assert.NotNull(userWith.User_Name);
    Assert.NotNull(userWith.Sex);

    Assert.Null(userNotWith.UserId);
    Assert.Null(userNotWith.UName);
    Assert.NotNull(userNotWith.Sex);

    Assert.NotNull(userMix.User_Id);
    Assert.Null(userMix.UserName);
    Assert.NotNull(userMix.ReSex);
}
[Fact]
public void QueryUnderscores_Has_MatchNamesWithUnderscores()
{
    var Id = "b4600fb2-61c3-4786-8ba1-9fc5387a8d17";
    DefaultTypeMap.MatchNamesWithUnderscores = true;

    var userWith = _userRepository.QueryModel<UserSimpleWithUnderscores>("select user_id,user_name,sex from user where user_id=@Id", new { Id });
    //相同查询参数会报错，这里user_name 改为 u_name
    var userNotWith = _userRepository.QueryModel<UserSimpleNotWithUnderscores>("select user_id,user_name u_name,sex from user where user_id=@Id", new { Id });
    var userMix = _userRepository.QueryModel<UserSimpleMixtureAndRename>("select user_id,user_name,sex as resex from user where user_id=@Id", new { Id });

    Assert.NotNull(userWith.User_Id);
    Assert.NotNull(userWith.User_Name);
    Assert.NotNull(userWith.Sex);

    Assert.NotNull(userNotWith.UserId);
    Assert.NotNull(userNotWith.UName);
    Assert.NotNull(userNotWith.Sex);

    Assert.NotNull(userMix.User_Id);
    Assert.NotNull(userMix.UserName);
    Assert.NotNull(userMix.ReSex);
}
[Fact]
public void QueryUnderscores_NotThenHas_MatchNamesWithUnderscores()
{
    var Id = "b4600fb2-61c3-4786-8ba1-9fc5387a8d17";

    var userWith = _userRepository.QueryModel<UserSimpleWithUnderscores>("select user_id,user_name,sex from user where user_id=@Id", new { Id });
    //相同查询参数会报错，这里user_name 改为 u_name
    var userNotWith = _userRepository.QueryModel<UserSimpleNotWithUnderscores>("select user_id,user_name u_name,sex from user where user_id=@Id", new { Id });
    var userMix = _userRepository.QueryModel<UserSimpleMixtureAndRename>("select user_id,user_name,sex as resex from user where user_id=@Id", new { Id });

    Assert.NotNull(userWith.User_Id);
    Assert.NotNull(userWith.User_Name);
    Assert.NotNull(userWith.Sex);

    Assert.Null(userNotWith.UserId);
    Assert.Null(userNotWith.UName);
    Assert.NotNull(userNotWith.Sex);

    Assert.NotNull(userMix.User_Id);
    Assert.Null(userMix.UserName);
    Assert.NotNull(userMix.ReSex);

    DefaultTypeMap.MatchNamesWithUnderscores = true;
    userWith = _userRepository.QueryModel<UserSimpleWithUnderscores>("select user_id,user_name,sex from user where user_id=@Id", new { Id });
    userNotWith = _userRepository.QueryModel<UserSimpleNotWithUnderscores>("select user_id,user_name u_name,sex from user where user_id=@Id", new { Id });
    userMix = _userRepository.QueryModel<UserSimpleMixtureAndRename>("select user_id,user_name,sex as resex from user where user_id=@Id", new { Id });

    Assert.NotNull(userWith.User_Id);
    Assert.NotNull(userWith.User_Name);
    Assert.NotNull(userWith.Sex);

    Assert.Null(userNotWith.UserId);
    Assert.Null(userNotWith.UName);
    Assert.NotNull(userNotWith.Sex);

    Assert.NotNull(userMix.User_Id);
    Assert.Null(userMix.UserName);
    Assert.NotNull(userMix.ReSex);
}

测试结果

总结

在Asp.Net Core中在Startup下加入
DefaultTypeMap.MatchNamesWithUnderscores = true;

注：千万不要动态在查询代码中配置，即要求有的查询需要兼容下划线，有的不允许兼容下划线，会非常难控制，参考测试代码第三个例子（将第一个第二个例子实体合并，查询结果是不一样的）。每个实体类进行了缓存

 public static Func<Type, ITypeMap> TypeMapProvider = (Type type) => new DefaultTypeMap(type);

 public static ITypeMap GetTypeMap(Type type)
 {
     if (type == null) throw new ArgumentNullException(nameof(type));
     var map = (ITypeMap)_typeMaps[type];
     if (map == null)
     {
         lock (_typeMaps)
         {  
             map = (ITypeMap)_typeMaps[type];

             if (map == null)
             {
                 map = TypeMapProvider(type);
                 _typeMaps[type] = map;
             }
         }
     }
     return map;
 }

 // use Hashtable to get free lockless reading
 private static readonly Hashtable _typeMaps = new Hashtable();

注：相似实体抛异常：Dapper Object must implement IConvertible.，相同查询字段不要声明多个实体。