CODEWITHSUNDEEP
c#sqlitedapper
turbo
turbo

Reputation: 1273

Dapper Cast Exception on NULL value in column

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.

Upvotes: 8

Views: 6088

Answers (3)

vitidev
vitidev

Reputation: 980

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());

Upvotes: 2

turbo
turbo

Reputation: 1273

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.

Upvotes: 2

GaelSa
GaelSa

Reputation: 600

If you create your table with "INTEGER", Sqlite will create int32, however you have a long in your model, I guess this is trowing the invalid cast exception, BigInt would have to be used to give a long.

So you should either change your request to :

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

Or use a model that match your request :

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

I guess your request with "IFNULL(parent_id, 0)" is working because it include a cast, as 0 can be considered as int32.

Upvotes: 0

Related Questions