本来是测试dapper转换真正的mysql语句后是否能真正使用索引的，不过被一个地方卡住了，在查询的时候一直不能映射强类型的值。找了大概1小时才找到处理的方式，记录下来免得忘记。

先看表设计

image.png

类定义，因为程序如果使用下划线感觉会很突兀，不符合C#类命名规范，所以统一使用了大写开头的驼峰式命名，找了一个通过Attribute的映射到数据库字段的方式，代码在最后贴上

public class PersonInfo

{

[Column(Name = "mainid")]

public long MainId { get; set; }

[Column(Name = "id_card_number")]

public string IdCardNumber { get; set; }

[Column(Name = "name")]

public string Name { get; set; }

[Column(Name = "created_datetime")]

public DateTime CreatedDateTime { get; set; }

}

先插入数据，通过dapper插入数据是没啥问题的，看看数据

image.png

代码也是正常，即使不指定列，只要多传入一个MainId也是能正常插入数据的

static void Main(string[] args)

{

string connectionString = @"server=localhost;port=3306;database=tor_db_test;user=root;Password=123456;CharSet=utf8mb4;Pooling=true;Min Pool Size=0;Max Pool Size=100;";

using (IDbConnection connection = new MySqlConnection(connectionString))

{

var resultInt = connection.Execute("INSERT INTO person_info VALUES (@MainId,@IdCardNumber,@Name,@CreatedDateTime)", new PersonInfo()

{

MainId = 2,

IdCardNumber = "440684200001010000",

Name = "呜呜呜",

CreatedDateTime = DateTime.Now

});

Console.WriteLine(resultInt);

}

Console.WriteLine();

Console.ReadLine();

}

但是查询就有问题了

static void Main(string[] args)

{

string connectionString = @"server=localhost;port=3306;database=tor_db_test;user=root;Password=123456;CharSet=utf8mb4;Pooling=true;Min Pool Size=0;Max Pool Size=100;";

using (IDbConnection connection = new MySqlConnection(connectionString))

{

//var p = connection.Query("SELECT * FROM person_info ");

var p = connection.Query("SELECT mainid,id_card_number,name,created_datetime FROM person_info ");

Console.WriteLine(JsonConvert.SerializeObject(p, Formatting.Indented));

}

Console.WriteLine();

Console.ReadLine();

}

查询结果如下，强类型查询无法自动匹配值了

image.png

试一下动态类型，正常的，所以问题定位在dapper强类型转换没能匹配上

image.png

最后发现有这个配置，主要是忽略下划线再进行匹配

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

加上之后就正常了

image.png

代码如下

static void Main(string[] args)

{

string connectionString = @"server=localhost;port=3306;database=tor_db_test;user=root;Password=123456;CharSet=utf8mb4;Pooling=true;Min Pool Size=0;Max Pool Size=100;";

using (IDbConnection connection = new MySqlConnection(connectionString))

{

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

var p = connection.Query("SELECT mainid,id_card_number,name,created_datetime FROM person_info ");

Console.WriteLine(JsonConvert.SerializeObject(p, Formatting.Indented));

}

Console.WriteLine();

Console.ReadLine();

}

通过Attribute映射代码,网上找的，么得注释

///

/// 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);

}

}

完