Dapper C#: A parameterless default constructor or one matching signature error-CodePudding

I would like to get list of partitions for a given table.

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}';");
    }
}

But my code gives an error like

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
    ...

How can I fix this?

I tried to use UInt64 instead of int for the type of OrdinalPosition, but the same error occured.

My Environment:

C# .NET Core Console Application (net6.0)
Dapper 2.0.123
MySQL 8.0

CodePudding user response：

Types and names must match (or use a custom mapping). You can use aliases to have "nicer" names in 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)

You should also deal with the potential SQL injection issue you have by building a string with the WHERE-clause.

Dapper makes this easy enough:

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