Dapper Cast Exception on NULL value in column

Question

I'm using SQLite and it's closure extension to store a hierarchy. The non-closure table is created as

_connection.Execute(@"CREATE TABLE IF NOT EXISTS category (
    id INTEGER NOT NULL PRIMARY KEY,
    name TEXT,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES category (id)
);");

The root node is inserted with parent_id set to NULL. The class for Dapper to cast to and from is

public class TestRecord
{
    public long id;
    public string name;
    public long? parent_id;
}

In my mind, Dapper shouldn't have any problems reading root or non-root nodes, as the column in question is clearly marked as nullable. However, querying all entries like so:

_connection.Query<TestRecord>(@"SELECT * FROM category;");

will throw on the root node, because it can't cast something (which is weird, because there are no 32bit ints involved anywhere):

Unhandled Exception: System.Data.DataException: 
Error parsing column 2 (parent_id=1 - Int64) ---> 
System.InvalidCastException: Unable to cast object of type 'System.Int64' to type 
  'System.Int32'.

A workaround that returns correct results is

.Query<TestRecord>(@"SELECT id, IFNULL(parent_id, 0), name FROM category;");

But this is a no-go for several reasons. I specifically don't want to list all columns in the query, nor do I want to introduce the parent_id special case.

Not using Dapper and mapping it manually works just fine with the original query, same goes for the sqlite CLI of course.

So, how can I get Dapper to accept and map the proper entries?

Edit: I'm using Dapper 1.50.4 and dotnet core 2.0.

Answer 1

Per Marc's comment this should not happen and it warrents a library fix. The issue is being tracked here and also affects other people.

Answer 2

Solution for sqlite nullable types. link

public class NullableLongHandler : SqlMapper.TypeHandler<long?>
{
    public override void SetValue(IDbDataParameter parameter, long? value)
    {
        if (value.HasValue)
            parameter.Value = value.Value;
        else
            parameter.Value = DBNull.Value;
    }

    public override long? Parse(object value)
    {
        if (value == null || value is DBNull) return null;
        return Convert.ToInt64(value);
    }
}

and

SqlMapper.AddTypeHandler(new NullableLongHandler());