具有NaN值的Dapper查询

Question

我有一个具有数字列类型的表，该表可能包含NaN值。使用Dapper运行查询时，如果字段包含NaN值，则会得到异常Unknown message code: 0。

示例模式

CREATE TABLE test (id Serial NOT NULL, val Numeric NOT NULL );
insert into test(val) VALUES(1.5),('Nan');

以下是抛出异常的查询

public class MyClass
{
public int ID { get; set; }
public double Val { get; set; }
}
public static async Task<IEnumerable<MyClass>> GetTest()
{
var sql = "select * from test";
using (var connection = Postgres.GetConnection())
{
try
{
var results = await connection.QueryAsync<MyClass>(sql);
return results;
}
catch (Exception ex)
{
return new List<MyClass>();
}
}
}

正在引发的异常。(不包括捕获的变量(

Unknown message code: 0

这是堆栈跟踪。

at Npgsql.PGUtil.ValidateBackendMessageCode(BackendMessageCode code) in C:projectsnpgsqlsrcNpgsqlPGUtil.cs:line 88
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:projectsnpgsqlsrcNpgsqlNpgsqlConnector.cs:line 956
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext() in C:projectsnpgsqlsrcNpgsqlNpgsqlDataReader.cs:line 383
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.<Consume>d__64.MoveNext() in C:projectsnpgsqlsrcNpgsqlNpgsqlDataReader.cs:line 703
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.<Close>d__68.MoveNext() in C:projectsnpgsqlsrcNpgsqlNpgsqlDataReader.cs:line 740
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.Close() in C:projectsnpgsqlsrcNpgsqlNpgsqlDataReader.cs:line 714
at Npgsql.NpgsqlDataReader.Dispose(Boolean disposing) in C:projectsnpgsqlsrcNpgsqlNpgsqlDataReader.cs:line 709
at System.Data.Common.DbDataReader.Dispose()
at Dapper.SqlMapper.<QueryAsync>d__33`1.MoveNext() in /_/Dapper/SqlMapper.Async.cs:line 459
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at MyClass.<GetTest>d__31.MoveNext() in C:MyClass.cs:line 367

Answer 1

Postgres中的Numeric类型确实能够包含NaN。

您可以插入double.NaN。

await connection.ExecuteAsync("INSERT INTO test(val) VALUES(@val)", new { val = double.NaN });

或者像这样：(注意，NaN文字周围需要单引号(

INSERT INTO test(val) VALUES('NaN');

然而，问题是Numeric类型与C#中不支持NaN的十进制类型匹配。显然，Dapper内部尝试将十进制属性设置为NaN，即使我的类上的属性是双倍的。这就是导致错误的原因。

解决方案是将数据库中的数据类型更改为双DOUBLE PRECISION或REAL

或者，如果不能更改模式，则可以在查询中显式转换为DOUBLE PRECISION。

SELECT id, val::DOUBLE PRECISION FROM test