DapperLib / Dapper.Contrib

Dapper community contributions - additional extensions for Dapper
Other
267 stars 99 forks source link

SqlMapperExtensions doesn't honor SqlMapper.TypeHandler in autogenerated primery keys when Inserting in Sql Server #169

Open yecarri opened 5 months ago

yecarri commented 5 months ago

SqlMapper.TypeHandler is intended to support custom types in entities. It's documented here:

https://medium.com/dapper-net/custom-type-handling-4b447b97c620

(I'm not sure if that is official documentation or not)

But Dapper.Contrib does not honor that. It throws an System.InvalidCastException in line 834 in SqlMapperExtensions:

public int Insert(IDbConnection connection, IDbTransaction transaction, int? commandTimeout, string tableName, string columnList, string parameterList, IEnumerable<PropertyInfo> keyProperties, object entityToInsert)
{
    var cmd = $"insert into {tableName} ({columnList}) values ({parameterList});select SCOPE_IDENTITY() id";
    var multi = connection.QueryMultiple(cmd, entityToInsert, transaction, commandTimeout);

    var first = multi.Read().FirstOrDefault();
    if (first == null || first.id == null) return 0;

    var id = (int)first.id;
    var propertyInfos = keyProperties as PropertyInfo[] ?? keyProperties.ToArray();
    if (propertyInfos.Length == 0) return id;

    var idProperty = propertyInfos[0];
    idProperty.SetValue(entityToInsert, Convert.ChangeType(id, idProperty.PropertyType), null);

    return id;
}

Exception

System.InvalidCastException: Invalid cast from 'System.Int32' to 'DapperInsertKey.Program+Key'.
   at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
   at SqlServerAdapter.Insert(IDbConnection connection, IDbTransaction transaction, Nullable`1 commandTimeout, String tableName, String columnList, String parameterList, IEnumerable`1 keyProperties, Object entityToInsert) in /_/Dapper.Contrib/SqlMapperExtensions.cs:line 834
   at Dapper.Contrib.Extensions.SqlMapperExtensions.Insert[T](IDbConnection connection, T entityToInsert, IDbTransaction transaction, Nullable`1 commandTimeout) in /_/Dapper.Contrib/SqlMapperExtensions.cs:line 377
   at DapperInsertKey.Program.Main(String[] args) in D:\users\yecarri\source\repos\DapperInsertKey\DapperInsertKey\Program.cs:line 31

Code

using Dapper;
using Dapper.Contrib.Extensions;
using Microsoft.Data.SqlClient;
using System.Data;

namespace DapperInsertKey
{
    internal class Program
    {
        static void Main(string[] args)
        {
            SqlMapper.AddTypeHandler(new KeyTypeHandler());

            using (var connection = new SqlConnection("server=.;database=equus;integrated security=true;Encrypt=false"))
            {
                connection.Open();

                connection.Execute(@"
                    If not exists (select TABLE_NAME from information_schema.TABLES t 
                    where t.TABLE_SCHEMA='dbo' AND TABLE_NAME= 'User') 
                        CREATE TABLE dbo.[User](
                            UserId int identity(1,1) primary key clustered, 
                            [UserName] [varchar](120) NOT NULL
                        )");

                connection.Execute("TRUNCATE TABLE dbo.[User]");

                var user = new User() { UserName="OSO" };
                connection.Insert(user);
                connection.Close();
            }
        }

        public struct Key
        {
            private readonly int _value;
            public Key(int value) { _value = value; }
            public static implicit operator Key(int value) { return new Key(value); }
            public static implicit operator int(Key key) { return key._value; }
        }

        [Table("dbo.[User]")]
        public class User
        {
            [Key]
            public Key UserId { get; set; }
            public string UserName { get; set; } = "";
        }

        public class KeyTypeHandler : SqlMapper.TypeHandler<Key>
        {
            public override Key Parse(object value)
            {
                return new Key((int)value);
            }

            public override void SetValue(IDbDataParameter parameter, Key value)
            {
                parameter.Value = (int)value;
            }
        }
    }
}