Dapper C#：无参数的默认构造函数或一个匹配的签名错误

Question

我想得到给定表的分区列表。

public record Partition(string PartitionName, int OrdinalPosition);

public class MysqlService
{
    //...

    public IEnumerable<Partition> GetPartitions(string tableName)
    {
        using var conn = new MySqlConnection(_dbConnString);
        conn.Open();
        return conn.Query<Partition>(
            $"SELECT PARTITION_NAME, PARTITION_ORDINAL_POSITION " +
            $"FROM information_schema.partitions WHERE TABLE_NAME = '{tableName}';");
    }
}

但是我的代码给出了一个错误，比如

Unhandled exception. System.InvalidOperationException: A parameterless default constructor or one matching signature (System.String PARTITION_NAME, System.UInt64 PARTITION_ORDINAL_POSITION) is required for Services.Partition materialization
    at Dapper.SqlMapper.GenerateDeserializerFromMap(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing, ILGenerator il) in /_/Dapper/SqlMapper.cs:line 3297
    at Dapper.SqlMapper.GetTypeDeserializerImpl(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 3131
    at Dapper.SqlMapper.TypeDeserializerCache.GetReader(IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.TypeDeserializerCache.cs:line 151
    at Dapper.SqlMapper.TypeDeserializerCache.GetReader(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.TypeDeserializerCache.cs:line 50
    at Dapper.SqlMapper.GetTypeDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 3085
    at Dapper.SqlMapper.GetDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 1835
    at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in /_/Dapper/SqlMapper.cs:line 1105
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 734
    ...

我怎么才能解决这个问题？

我尝试使用UInt64而不是int来表示OrdinalPosition的类型，但是也发生了同样的错误。

我的环境：

• C# .NET核心控制台应用程序.NET 8.0

Answer 1

类型和名称必须匹配(或使用自定义映射)。您可以使用别名在C#中使用“更好”的名称：

SELECT PARTITION_NAME AS PartitionName, PARTITION_ORDINAL_POSITION AS OrdinalPosition
FROM information_schema.partitions WHERE TABLE_NAME ...

public record Partion(string PartitionName, uint64 OrdinalPosition)

您还应该通过使用WHER-子句构建字符串来处理可能存在的SQL注入问题。

Dapper让这件事变得很简单：

public IEnumerable<Partition> GetPartitions(string tableName)
{
    using var conn = new MySqlConnection(_dbConnString);
    conn.Open();
    return conn.Query<Partition>(
        $"SELECT PARTITION_NAME AS PartitionName, PARTITION_ORDINAL_POSITION AS OrdinalPosition " +
        $"FROM information_schema.partitions WHERE TABLE_NAME = @tableName;", 
        new { tableName });
}