System.InvalidCastException - Dapper 1.50.4

[rhfrench]

My team and I have discovered what seems to be a bug in Dapper v1.50.4

Exception thrown: 'System.InvalidCastException' in Dapper.dll
Exception thrown: 'System.Data.DataException' in Dapper.dll

The bug is simple, if a field (INT for example) is created and if the first value inserted into this field is Null and the second value inserted is an INT. The program will crash unless than value is cast to the correct type.

This program has been built to show how to duplicate the issue:
https://github.com/rhfrench/DapperTest

We were also having trouble nullable int values even when the values are cast to the correct type. The first image is the actual error I'm running into. The second image shows the output of the query that is being run. We are using Microsoft.Data.Sqlite 2.0.1.

Here is the class:

[mgravell]

And this (sqlite) is why we can't have nice things :)

Basically, the sqlite provider lies to us - or rather: it has a very limited selection of data types, and it does the best it can to guess what the actual data type is, based on the declared SQLite datatype and the data it knows about at the time.

What dapper wants to do, for performance is:

using(var reader = ExecuteQuery(...))
{
    // for each grid:
    Func<DataReader, T> materializer = InspectReaderSchemaAndGetMaterializer<T>(reader);
    while(reader.Read()) {
        var model = materializer(reader);
        // ...do something with model
    }
}

Unfortunately, SQLite changes its mind abut what the schema is per row. This is ... to put it mildly, "inconvenient". It might thing that a column is going to be a long initially, then decide that actually it is an int, or whatever. This breaks materializer.

We have two choices here, to compensate for SQLite's indecisiveness:

• re-check the full schema for every row - getting a different materializer if required
• be far more forgiving in our materializer implementation - essentially doing much more dynamic type conversion at runtime

Both have performance implications.

So: that's the "why". As for fixing it: my preference is probably on the second option, because simply querying the schema is horribly expensive. It just needs a huge rewrite of some very complex IL-generation code :)

[dknr]

I was able to work around this with a wrapper type and an implementation of SqlMapper.ITypeHandler

public class Startup {
    public void OnStartup() {
        // register type handler
        SqlMapper.AddTypeHandler(typeof(DbDecimal), new DbDecimalTypeHandler ());
    }
}

public class Foo
{
    // use wrapper type instead of decimal
    // public decimal? Value { get; set; }
    public DbDecimal? Value { get; set; }
}

public struct DbDecimal
{
    public static implicit operator decimal(DbDecimalv) => v.Value ;
    public static implicit operator DbDecimal(decimal v) => new DbDecimal(v);
    
    public decimal Value { get; set; }

    public DbDecimal(decimal? v) => Value = v;
}

internal class DbDecimalTypeHandler : SqlMapper.ITypeHandler
{
    public void SetValue(IDbDataParameter parameter, object value)
    {
        parameter.Value = value;
    }

    public object Parse(Type destinationType, object value)
    {
        if(value == DBNull.Value || value == null)
            return null;
        
        switch(value)
        {
            case decimal decimalValue:
                return new DbDecimal(decimalValue);
            case int intValue:
                return new DbDecimal(Convert.ToDecimal(intValue));
            case double doubleValue:
                return new DbDecimal(Convert.ToDecimal(doubleValue));
            default:
                throw new DataException();
        }
    }
}

[kyledarrenhanson]

Hi,

I'm having similar invalid cast issue and I did a similar wrapper class. What I'd like to know is if I'm able to replace the 'default' type handlers in Dapper?

I tried doing SqlMapper.RemoveTypeMap(typeof(Int32); and then SqlMapper.AddTypeHandler(typeof(Int32), new Int32Handler()); but I'm still getting invalid cast, it's as if the RemoveTypeMap() isn't having any effect.

Much appreciated,
Kyle

[NickCraver]

@kyledarrenhanson If you call AddTypeHandlerImpl, it's ultimately replacing the type handler. And yes, you're able to replace any of the defaults this way.

[kyledarrenhanson]

@NickCraver much appreciated but I still can't seem to make it work.

below is some of the code I've been trying, some of which is commented out, but shows you things I've been attempting:

        IDbConnection cxn = CreateIngresConnection();

// SqlMapper.AddTypeHandler(typeof(TinyInt), new TinyIntHandler());
// SqlMapper.RemoveTypeMap(typeof(Int32));
// SqlMapper.ITypeMap iTypeMap = SqlMapper.GetTypeMap(typeof(Int32));
// SqlMapper.AddTypeHandler(typeof(Int32), new Int32Handler());
// SqlMapper.ITypeMap inewTypeMap = SqlMapper.GetTypeMap(typeof(Int32));

     SqlMapper.AddTypeHandlerImpl(typeof(TinyInt), new TinyIntHandler(), true);  // i tried both true/false but didn't work
     SqlMapper.AddTypeHandlerImpl(typeof(Int32), new Int32Handler(), true);  // i tried both true/false but didn't work

        var permits = cxn.Query<Model.SecurityPermit>("select * from scrty_permits");

at this point on the query, it fails with:
- System.Data.DataException: 'Error parsing column 2 (display_permit=0 - Int32)'
- InvalidCastException: Specified cast is not valid.

my type handler:

internal class Int32Handler : SqlMapper.ITypeHandler
{
public object Parse(Type destinationType, object value)
{
return new Int32(Convert.ToInt32(value));
}

public void SetValue(IDbDataParameter parameter, object value)
{
throw new NotImplementedException();
}
}

[pimbrouwers]

It's worth mentioning that people are likely to run into this during an empty when there are null values for columns that aren't of type text.

The simplest solution, of course is to set those values to null using a regular ol' UPDATE statement post-import.

[codephined]

Was this ever resolved?
I'm getting some strange behavior on one nullable field, while others of the same type don't throw this error.
Unable to cast object of type 'System.Double' to type 'System.Int64'

I solved this issue @ #1002 (comment) for those who are having this problem.

[1Cor125]

We are evaluating Dapper for use as a SQLite micro ORM and we have hit up against this issue when querying nullable INTEGER columns. Are there plans to fix this in the near future? We would rather not start off writing type handlers for such a common use case in our domain.

@mgravell posted the following on this issue some time ago:

We have two choices here, to compensate for SQLite's indecisiveness:

• re-check the full schema for every row - getting a different materializer if required
• be far more forgiving in our materializer implementation - essentially doing much more dynamic type conversion at runtime

Both have performance implications.

So: that's the "why". As for fixing it: my preference is probably on the second option, because simply querying the schema is horribly expensive. It just needs a huge rewrite of some very complex IL-generation code :)

[mmaterowski]

I was able to solve issue by adding custom mapper for Int32
SqlMapper.AddTypeHandlerImpl(typeof(Int32), new Int32Handler(), true);

 internal class Int32Handler : SqlMapper.ITypeHandler
    {
        public object Parse(Type destinationType, object value)
        {
            if ((long)value == 0)
            {
                int intDefaultValue = default(int);
                return intDefaultValue;
            }
            int.TryParse(value.ToString(), out int parsedIntValue);
            return parsedIntValue;
        }

        public void SetValue(IDbDataParameter parameter, object value)
        {
            throw new NotImplementedException();
        }
    }

[jbteague]

Dec 2020, still has this problem.
i've been trying to get a custom handler added to no avail either. gonna be sad when i rip it all out and go with straight ADO to fix this 3rd party library =/