Unable to cast object of type 'System.Int64' to type 'System.Double'.

[toolgood]

When querying with sqlite, the returned number type is double for 1.1 and int64 for 1。

Unable to cast object of type 'System.Int64' to type 'System.Double'.

The test data are as follows：

79.323329950348
80.462657803954
80.663827661334
82.19525663697
82.256206062269
82.338107188887
82.645383282368
84.093674963465
84.305141490906
84.362373782035
84.70228220853
84.705113270763
92.065123869284
92.123411039185
92.231554227815
92.288451267223
92.325470785945
92.360404379495
92.422683473803
92.46222673748
92.488785183829
92.49811661308
92.537086545187
92.580533310208
92.611517610454
100

[toolgood]

Problem association code

             else if (Type.IsValueType || Type == typeof(string) || Type == typeof(byte[]))
                {
                    // Do we need to install a converter?
                    var srcType = reader.GetFieldType(0);
                    var converter = GetConverter(mapper, null, srcType, Type);

                    // "if (!rdr.IsDBNull(i))"
                    il.Emit(OpCodes.Ldarg_0); // rdr
                    il.Emit(OpCodes.Ldc_I4_0); // rdr,0
                    il.Emit(OpCodes.Callvirt, fnIsDBNull); // bool
                    var lblCont = il.DefineLabel();
                    il.Emit(OpCodes.Brfalse_S, lblCont);
                    il.Emit(OpCodes.Ldnull); // null
                    var lblFin = il.DefineLabel();
                    il.Emit(OpCodes.Br_S, lblFin);

                    il.MarkLabel(lblCont);

                    // Setup stack for call to converter
                    AddConverterToStack(il, converter);

                    il.Emit(OpCodes.Ldarg_0); // rdr
                    il.Emit(OpCodes.Ldc_I4_0); // rdr,0
                    il.Emit(OpCodes.Callvirt, fnGetValue); // value

                    // Call the converter
                    if (converter != null)
                        il.Emit(OpCodes.Callvirt, fnInvoke);

                    il.MarkLabel(lblFin);
                    il.Emit(OpCodes.Unbox_Any, Type); // value converted

[asherber]

Can you please provide some code that shows your table structure, the POCO you're using, and how you're retrieving values? The following code works fine for me.

CREATE TABLE IF NOT EXISTS Foo (Bar REAL)
INSERT INTO Foo VALUES (1), (1.1)

public class Foo
{
    public double Bar { get; set; }
}

db.Fetch<Foo>();

[toolgood]

        static void Main(string[] args)
        {
            var sql = @"CREATE TABLE IF NOT EXISTS Foo (Bar number);
INSERT INTO Foo VALUES (0.99),(1), (1.1)";

            File.Create("1.db").Close();
            string connectionString = "DataSource=1.db;";
            var db = new Database(connectionString, new SQLiteDatabaseProvider());
            db.Execute(sql);

            var db2 = db.Query<double>("select Bar from Foo").ToList();


        }

[asherber]

That code runs just fine for me, no exceptions. What version of System.DataSQLite are you using? I'm using System.Data.SQLite.Core v1.0.117.

[toolgood]

TargetFramework ：net7.0
Microsoft.Data.Sqlite Version="7.0.1"

[asherber]

Okay, thanks. I can reproduce with that driver; I'll look into it.

[asherber]

Here's what's going on. When you run your select query, PP peeks at the first record in the DataReader and sets up a factory method that can convert the fields in the DataReader to the desired POCO (which, in this case, is just double). It then uses this method to convert each row.

Using System.Data.SQLite.Core, the values come across as decimal, and the converter in the default mapper will call Convert.ChangeType() in order to go from decimal to double.

Using the Microsoft driver, however, the values come across as different types -- 0.99 comes across as double, but 1 comes across as Int64. When PP is setting up the factory method, it sees the 0.99 first, assumes that we're going from double to double and figures that we don't need a converter. But then when it gets the second record, where the value is an int64, it crashes because that's not what it's expecting.

Try changing your code so that the int gets inserted first (VALUES (1), (1.1), (0.99)). Now it works fine, because the factory method sees the int first and installs Convert.ChangeType() as the converter, which is able to handle both int and double.

In short, PP assumes that the values in a column are all of the same type -- which I think is a reasonable assumption, since they're the same type on the database side. In some ways, this is an even more specialized case of the issue discussed here, in which a column returned by a stored proc might legitimately be of different types between invocations (but always a single type for a given invocation).

I would say this is a bug in the MS driver -- or at best a questionable design decision. Your best option is probably to switch to the SQLite driver. You might also be able to work around things by using a custom mapper, descending from ConventionMapper, one that uses Convert.ChangeType() if the source and destination types are any numeric types, even if they're the same type.

[pleb]

Wow. Great breakdown of the issue @asherber ❤️ love your work!

@toolgood Maybe try the MS driver repo for a solution. Or maybe there's a hack to force it with the DB VALUES (1.0) (1.1) etc

[toolgood]

I'm going to change the NUMBER type to REAL type.