Sqlite using Dapper. Error parsing column . Unable to cast object of type System.Int64 to type System.Double

Question

I'm using fastcrud and dapper to map my query results to my custom type. I investigated the dapper library and i came to the conclusion that i think it's an issue with the Microsoft.Data.Sqlite package. Not sure if it must be fixed over there but this is the problem.

Before parsing each row from your query result, Dapper uses the GetFieldType method from the SqliteDataReader class to get the correct column types.

Imagine the following table:

Id | MinTemp | MaxTemp

1  | 10.5    | 18.2 

2  | 10.6    | 20 

The sqlitedatareader.GetFieldType method will give you the following types:

System.Int64 for Id and System.Double for both MinTemp and MaxTemp This seems to be correct but when it's parsing the results the following error occurs on row 2

Error parsing column 10 (MaxTemp=20 - Int64) Unable to cast object of type 'System.Int64' to type 'System.Double'.

Due to the dynamic type system of SQLite, it changes the type for MaxTemp on row 2 to System.Int64. But because dapper defined the type for MaxTemp to double it causes the error mentioned above.

If i change my MaxTemp value in my sqlite database to 20.00001 the problem is fixed, but this is not correct.

Used packages: "Microsoft.Data.Sqlite": "1.0.0"

Anyone knows how to solve this problem?

Answer 1

This appears to be a bug / flaw in Dapper:

https://github.com/StackExchange/Dapper/issues/642

Because SQLite uses value affinity, the value 20 in the second row of the example is treated like an INT. (Even if it was literally inserted as '20.0' or '20.00' etc.)

Apparently Dapper fails to individually handle the different types which come back for each individual field.

A workaround for this appears to be to use a SQL CAST(). Example:

SELECT CAST(field123 AS REAL) AS field123 FROM ...

This appears to get around the bug.