Dapper: Sqlite: InvalidCastException if the first value in a column is NULL followed by any other value

[CheeseSucker]

It seems like Dapper uses the first value in a column to decide what type the rest of the values will have.
If the first value is NULL, this fails spectacularly.

I have set up a repository demonstrating this bug:
https://github.com/CheeseSucker/DapperSqliteBug

Tested dapper versions: 1.50.0, 1.50.2, 1.50.3-beta1

Relevant code:
```C#
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.Execute("CREATE TABLE MyTable (MyValue INTEGER)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (4)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (NULL)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (4)");

// This is fine
var result1 = connection.Query<MyModel>("SELECT * FROM MyTable");

// This is also fine
var result2 = connection.Query<MyModel>("SELECT * FROM MyTable ORDER BY MyValue IS NULL ASC");

// This will fail because NULL is the first value in the column
var result3 = connection.Query<MyModel>("SELECT * FROM MyTable ORDER BY MyValue IS NULL DESC");

// InvalidCastException has been encountered before this line
connection.Close();

}

```C#
class MyModel
{
    public long MyValue;
}

[mgravell]

This sounds horribly familiar, and iirc when I got to the bottom of it last
time, it was essentially a provider bug. Let me see if I can find the more
detailed chain.

On 17 Nov 2016 4:20 pm, "CheeseSucker" [email protected] wrote:

It seems like Dapper uses the first value in a column to decide what type
the rest of the values will have.
If the first value is NULL, this fails spectacularly.

I have set up a repository demonstrating this bug:
https://github.com/CheeseSucker/DapperSqliteBug

Tested dapper versions: 1.50.0, 1.50.2, 1.50.3-beta1

Relevant code:

using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.Execute("CREATE TABLE MyTable (MyValue INTEGER)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (4)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (NULL)");
connection.Execute("INSERT INTO MyTable (MyValue) VALUES (4)");

// This is fine
var result1 = connection.Query("SELECT * FROM MyTable");

// This is also fine
var result2 = connection.Query("SELECT * FROM MyTable ORDER BY MyValue IS NULL ASC");

// This will fail because NULL is the first value in the column
var result3 = connection.Query("SELECT * FROM MyTable ORDER BY MyValue IS NULL DESC");

// InvalidCastException has been encountered before this line
connection.Close();
}

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/dapper-dot-net/issues/642, or mute the
thread
https://github.com/notifications/unsubscribe-auth/AABDsLRN9GLK3RG9F_VVMalt7Q6DH6-dks5q_H7HgaJpZM4K1gWz
.

[CheeseSucker]

329 looks a bit similar. Could these problems be related?

[mgravell]

Actually the one I was thinking of is #552, but that is mysql; if this is sqlite, I need to investigate from scratch. It could be similar, it could be unrelated. For the record, dapper doesn't do _anything_ with the first row; instead, it asks the reader what the column types are. If the _reader_ (provider-specific) does something stupid, that's when we get trouble.

[hakon-knowit]

I think it is indeed the same issue.

Just like in the MySQL issue, SQlite will return a different value for GetFieldType() while iterating a result set:
https://github.com/aspnet/Microsoft.Data.Sqlite/issues/300

Unfortunately, this is by design and so is unlikely to change.

[mgravell]

Dammit. That's extremely vexing. I need to think on this. There is no
perfect fix.

On 21 Nov 2016 4:44 pm, "Håkon Trandal" [email protected] wrote:

I think it is indeed the same issue.

Just like in the MySQL issue, SQlite will return a different value for
GetFieldType() while iterating a result set:
aspnet/Microsoft.Data.Sqlite#300
https://github.com/aspnet/Microsoft.Data.Sqlite/issues/300

Unfortunately, this is by design and so is unlikely to change.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/dapper-dot-net/issues/642#issuecomment-261994026,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsPmlefUHuHnSwOpPuzEzgTDaJc-pks5rAcqIgaJpZM4K1gWz
.

[mgravell]

The best I can figure off the top of my head is a global
AssumeColumnsAreStronglyTyped property that defaults to true but that which
can be elected to false (and we'd emit guidance to do so in the invalid
cast scenario); if false, all value conversions go via a Read{type} method
that converts between a wide range of known primitives. So if you have a
float, for example, it could work from any int* types, double, decimal,
string, etc. With the fist line "if(val is float) return (float)val". Or
perhaps more generally:

obj.Prop = val is TheType ? (TheType)val : ReadTheType(val);

Thoughts?

On 21 Nov 2016 6:10 pm, "Marc Gravell" marc.[email protected] wrote:

Dammit. That's extremely vexing. I need to think on this. There is no
perfect fix.

On 21 Nov 2016 4:44 pm, "Håkon Trandal" [email protected] wrote:

I think it is indeed the same issue.

Just like in the MySQL issue, SQlite will return a different value for
GetFieldType() while iterating a result set:
aspnet/Microsoft.Data.Sqlite#300
https://github.com/aspnet/Microsoft.Data.Sqlite/issues/300

Unfortunately, this is by design and so is unlikely to change.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/dapper-dot-net/issues/642#issuecomment-261994026,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsPmlefUHuHnSwOpPuzEzgTDaJc-pks5rAcqIgaJpZM4K1gWz
.

[CheeseSucker]

That sounds fine to me. The setting should probably be tied to a DbConnection in case more than one database is used.

What are the drawbacks to doing this?

[mgravell]

The drawback is that it will be slightly slower. I'm loathe to try to tie
it to the connection type, because there isn't really a good API for that
which reliably exposes the actual provider, especially when tools exist
that work as decorators (meaning: GetType() isn't a good option). In this
context, I'd rather just have all the connections pay the slight
check/conversion overhead.

Marc

On 23 November 2016 at 08:52, CheeseSucker [email protected] wrote:

That sounds fine to me. The setting should probably be tied to a
DbConnection in case more than one database is used.

What are the drawbacks to doing this?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/dapper-dot-net/issues/642#issuecomment-262460263,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsPpxp2Fkq3ID-3Ygja95uR_PO0P2ks5rA_7PgaJpZM4K1gWz
.

Regards,

Marc

[CheeseSucker]

Sounds good!

[canton7]

Is there any progress on this? As far as I can make out, this is stopping me from using Dapper in a netcore app.

[enantiomer2000]

I also am experiencing this problem with the Microsoft.Data.Sqlite library and Dapper. I switched to Microsoft.Data.Sqlite because the System.Data.Sqlite library doesn't work with linux without recompiling the Interop.Sqlite dll which is quite troublesome. I fix for this would be awesome. My datasets are not very large so a slight performance hit is not so huge a deal for me.

[mlaily]

Well, it seems I have the same issue.
Any news?
I see there is a pull request (https://github.com/StackExchange/Dapper/pull/720), but it does not merge anymore... :/

[DaveInCaz]

This question on SO seems to be a result of this issue: https://stackoverflow.com/questions/39980840/sqlite-using-dapper-error-parsing-column-unable-to-cast-object-of-type-system

[DaveInCaz]

Little example of why this is a problem in my case.

In one sqlite table, a column has values like this:

--------
70.1
70
69.8
...

Since sqlite will only treat the value "70" as a integer, never a floating point (even if I inserted the value "70.0") this exposes this Dapper casting problem. There does not appear to be any workaround in the DB.

[DaveInCaz]

If you use a SQL CAST() on the column having the problem, you can work around this issue. This has been mentioned elsewhere but not yet in this issue, FYI for future readers.

[aidanw]

I am having this issue and casting did not help.

With my current data I can sort so I don't get nulls in the first row... but it is a shaky solution!

[kardkovacsi]

I think it would be better to state on the website that Dapper DOES NOT support SQLite so people will not invest time and money into a non-working package.

[abbfmst]

Sorry @mgravell but this issue is still there. Does this comment here in Microsoft.Data.Sqlite help any further?
https://github.com/aspnet/Microsoft.Data.Sqlite/issues/300#issuecomment-402779146

Otherwise the suggestion from @kardkovacsi and @hsorbo seems to be fair, as I'm hit with this issue while developing a quick and simple Web API using SQLite and Dapper.
(DECIMAL column parsed as INT by error)

[ravimpatel]

I created a type handler for double? , it seems to work. Assumption here is that if user has specified type as double, it should attempt to convert it to double. As a user I should not be type a memo field as double of course. Is this an acceptable workaround?

`

    public class NullableDoubleHandler : SqlMapper.TypeHandler<double?>{
    protected NullableDoubleHandler() {}
    public static readonly NullableDoubleHandler Default = new NullableDoubleHandler();
    public override void SetValue(IDbDataParameter parameter, double? value)
    {
        if (value.HasValue)
        {
            parameter.Value = value.Value;
        }
        else
        {
            parameter.Value = DBNull.Value;
        }
    }
    public override double? Parse(object value)
    {
        if (value == null || value is DBNull) return null;

        return Convert.ToDouble(value);
    }
} `

[vitidev]

The error still exists and seems never to be fixed. Thanks @ravimpatel for a good solution.