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" notifications@github.com 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
#642, or mute the
thread
https://github.com/notifications/unsubscribe-auth/AABDsLRN9GLK3RG9F_VVMalt7Q6DH6-dks5q_H7HgaJpZM4K1gWz
.

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

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.

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

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

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" notifications@github.com 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
aspnet/Microsoft.Data.Sqlite#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
#642 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsPmlefUHuHnSwOpPuzEzgTDaJc-pks5rAcqIgaJpZM4K1gWz
.

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.gravell@gmail.com 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" notifications@github.com 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
aspnet/Microsoft.Data.Sqlite#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
#642 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsPmlefUHuHnSwOpPuzEzgTDaJc-pks5rAcqIgaJpZM4K1gWz
.

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?

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 notifications@github.com 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
#642 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsPpxp2Fkq3ID-3Ygja95uR_PO0P2ks5rA_7PgaJpZM4K1gWz
.

Regards,

Marc

Sounds good!

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

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.

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

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

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

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

field123
--------
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.

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.

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!

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.

Sorry @mgravell but this issue is still there. Does this comment here in Microsoft.Data.Sqlite help any further?
aspnet/Microsoft.Data.Sqlite#300 (comment)

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)

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);
    }
} `

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