Using int type as identity key results in an error (library always try to convert the value to long)

[spetz]

Hey,

I've found out the following issue when using int as a key and then trying to insert the record into the Postgres table (which is configured to be of identity type - so it's automatically incremented value by DB).

System.ArgumentException: Object of type 'System.Int64' cannot be converted to type 'System.Int32'.
   at System.RuntimeType.TryChangeType(Object value, Binder binder, CultureInfo culture, Boolean needsSpecialCast)

This is the sample entity:

public class MyType
{
    public int Id { get; set; }
}

and a mapping which doesn't really do anything

internal sealed class MyTypeMapping : ClassMapper<MyType>
{
    public MyTypeMapping()
    {
        Map(x => x.Id).Type(DbType.Int32).Key(KeyType.Identity);
        AutoMap();
    }
}

Seems that it all boils down to the following line - which always does assume that the ID will be of long type, even though it is int which is a valid type for the column (e.g. smallint, integer, biginteger).

Dapper-Extensions/DapperExtensions/DapperImplementor.cs

Line 624 in 6482074

result = connection.Query<long>(sql, dynamicParameters, transaction, false, commandTimeout, CommandType.Text);

[valfrid-ly]

We have to use long as these identities can get very big.

If you do not apply "Type" to the mapping it should work normally.

[spetz]

No, it doesn't work properly with or without mapping in any configuration possible. It's a bug which does prevent from using any other type than long for primary keys. I don't know why it was closed, as the issue was not resolved at all?

[valfrid-ly]

I'll check it out

I am running into this issue as well.

So it seems as if this problem might be even deeper than just those lines. I went through and changed the supplied type parameter to dynamic and then pulled the value out of the resulting DapperRow and it was still set to long. From that I noticed this:

Dapper-Extensions/DapperExtensions/Sql/SqlServerDialect.cs

Lines 21 to 24 in 6482074

	public override string GetIdentitySql(string tableName)
	{
	return string.Format("SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS [Id]");
	}

So it looks like it was even being cast at the SQL level. I even tried to remove this cast and keep the rest of the code changes and it still ran into an issue as it was then a System.Decimal. I am not sure how to move forward with this, as with the current structure I believe the only solution would be to have dynamic type parameters to pass into Dapper in the Insert method and I don't know a way to implement this or if that would even be possible.

[ALMMa]

I found a workaround.

If you change this line from DapperImplementor as well as this other line too to be like below, it would work out of the box.

if (triggerIdentityColumn != null)
{
    keyValue = Convert.ChangeType(InsertTriggered(connection, entity, transaction, commandTimeout, sql, triggerIdentityColumn, dynamicParameters), keyColumn.MemberType);
}
else
{
    keyValue = Convert.ChangeType(InsertIdentity(connection, transaction, commandTimeout, classMap, sql, dynamicParameters), keyColumn.MemberType);
}

So far I have been able to move without further issues, but I can't submit a PR so fast as I'm still validting the new version. But it's working on my end.

To have this working I am customizing the InstanceFactory as follows:

DapperExtensions.DapperExtensions.InstanceFactory = MyFactory;
private IDapperImplementor MyFactory(IDapperExtensionsConfiguration config) => new MyCustomImplementor(new MyCustomSqlGenerator(config));

Just copy DapperImplementor (MyCustomImplementor) and SqlGenerator (MyCustomSqlGenerator) and add fix this straight on the MyCustomImplementor. You don't need to copy the generator, but it would help to troubleshoot, in case something else comes in...

---

EDIT: Found a better place to put the conversion which also fixes the returned value from the expression, and not only the key value when populating back the model.