Dapper struggles with the ultra dynamic types of sqlite
Using dapper to run this query against sqlite, (and cast it an object with a
Double type property called "TotalPrice")
cnn.Query<SalesDetails>(@" select p.TotalPrice as TotalPrice from Purchases p order by p.ID desc limit 20 ")
TotalPrice column has these values:
...it throws an exception with these details:
? ex.Message "Error parsing column 1 (TotalPrice=24 - Int64)" ? ex.InnerException.Message "Unable to cast object of type 'System.Int64' to type 'System.Double'."
What's happening is that row by row the column is coming back with the most appropriate type - so on row three it's suddenly not a double but an int.
TO overcome it - cast the column to the type you want....
CAST(p.TotalPrice as DOUBLE) as TotalPrice
...and it's all good.
Still -- a nasty problem, because it way not arise during initial testing... only with the right data does the problem occur.