System.Data.SQLite.Core SUM operation always returns as Int64
This most certainly looks like a bug to me. The same query gives a different result when using DB Browser vs the C# System.Data.SQLite.Core wrapper.
The following query:
var sqlCmd = new SQLiteCommand("SELECT SUM(Cost * Quantity) FROM Parts", connection); var value = await sqlCmd.ExecuteScalarAsync();
always returns an Int64 value, despite Cost=numeric and Quantity=integer. Even if you cast the SUM as a numeric type, you still get back an Int64 and it truncates the decimal values. The same query results in a numeric type when using something like DB Browser, and not an Int64. I can't seem to find a workaround.
Can anyone else confirm this?
SQLite itself has no say in that, but Joe Mistachkin who maintains the System.Data.SQLite wrapper is on here too and may have some advice.
You may also get good results asking on a C# forum.
That said, as a matter of professional curiosity, what does your C# return when you do:
var sqlCmd = new SQLiteCommand("SELECT SUM(Cost * Quantity) * 1.0 FROM Parts", connection);