Why is dapper returning every number column as Int64?

Question

I have the following query:

SELECT
  [Person].[Id] AS [Person_Id],
  [Person].[Name] AS [Person_Name],
  [Address].[Id] AS [Address_Id],
  [Address].[Number] AS [Address_Number],
  [Address].[Street] AS [Address_Street],        
FROM [Person]
LEFT JOIN [Address] ON [Person].[addressId] = [Address].[Id]

Which is used to query a SQLite in-memory DB as:

var rows = _database.Query(query);

However when I try to read the values as Int32 I get InvalidCastException.

foreach (IDictionary<string, object> row in rows)
{
    var someId = (int)row["Person_Id"];
    var someNumber = (int)row["Address_Number"];
}

The reason why I am using dapper like so is that as a side project I am building some features on top of dapper to map the values to a POCO (I am aware of the similar projects e.g. rainbow, dapper.extensions etc).

So something along the lines of:

var rowVal = row[fieldName];
propInfo.SetValue(obj, rowVal, null);    

Again, ignore the performance cost associated with the reflection call.

In the example above the property type of the POCO is int32 and because of this problem I cannot assign the value to the obj.

Any help or ideas are very much appreciated.

Answer 1

It's not Dapper, it's SQLite. See Datatypes In SQLite Version 3 (assuming you're actually using v3):

The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. This makes a difference on disk. But as soon as INTEGER values are read off of disk and into memory for processing, they are converted to the most general datatype (8-byte signed integer). And so for the most part, "storage class" is indistinguishable from "datatype" and the two terms can be used interchangeably.