DapperLib/Dapper.Contrib

ISqlAdapter uses Convert.ChangeType for inserted Id which limits type conversion extensibility

andrewjsaid opened this issue · 3 comments

Scenario described originally in andrewlock/StronglyTypedId#43

To summarize I have a custom struct to represent an Id field of an entity. The struct has a TypeConverter as well as a DapperTypeHandler to handle conversions to/from int but due to the implementation of MySqlAdapter (and other ISqlAdapters) this Id field can't be used naturally with Dapper.Contrib's InsertAsync.

The problem arises as mysql's LAST_INSERT_ID() returns a ulong and Dapper.Contrib uses Convert.ChangeType to convert the resulting ulong into the target field.

One solution would be to use Dapper's existing mechanisms for mapping fields (TypeHandler) but that doesn't seem to have a public API so I'm not sure how that would be achieved.

Alternatively, the MySqlAdapter (and other ISqlAdapters) can be changed to use something like the following

var converter = TypeDescriptor.GetConverter(typeof(idp.PropertyType));
var newValue = converter.ConvertFrom(id);
idp.SetValue(entityToInsert, newValue, null);

I'm running into this issue as well, and I have not found a work around yet. Is any progress being made on this issue?

@crazyfox55 In our project we have just started adding a MyId property which also helps smoothen the migration for existing objects with an int Id property.

    [Computed] // Workaround for https://github.com/DapperLib/Dapper.Contrib/issues/123
    public UserId MyId
    {
        get => new(Id);
        set => Id = value.Value;
    }

This is my solution after spending hours digging through dapper code. First I set the defaults of the StronglyTypedId through the StronglyTypedIdDefaults. I put this code in some central file, I believe any file would work.

[assembly: StronglyTypedIdDefaults(
	backingType: StronglyTypedIdBackingType.Int,
	converters: StronglyTypedIdConverter.TypeConverter | StronglyTypedIdConverter.DapperTypeHandler)]

Then I automate setting up the type handlers for Dapper. aka 'SqlMapper.AddTypeHandler' This covers most of the conversion that Dapper needs.

Then I have to assign my own custom SqlServerAdapter. I need a custom SqlServerAdapter so that I can change how the Id gets set, almost exactly the way you mentioned in your first comment. This is done through some hacky reflection where I access a private static field within Dapper. You're using MySQL so you'll have to set a different name in the "AdapterDictionary".

Then I call them in the main class, this only needs to be called once for setup:

SqlMapperExtensions.AddAllTypeHandlers(typeof(Program).Assembly);
SqlMapperExtensions.SetCustomSqlServerAdapter();
	public static class SqlMapperExtensions
	{
		public static void AddAllTypeHandlers(Assembly assembly)
		{
			var typeHandlerTypes = assembly.GetTypes()
				.Where(type => !type.IsAbstract && !type.IsInterface && type.BaseType != null && type.BaseType.IsGenericType && type.BaseType.GetGenericTypeDefinition() == typeof(SqlMapper.TypeHandler<>));

			var mapping = typeHandlerTypes.Select(type => (type.BaseType!.GenericTypeArguments.First(), type));

			foreach (var (target, handler) in mapping)
			{
				SqlMapper.AddTypeHandler(target, Activator.CreateInstance(handler) as SqlMapper.ITypeHandler);
			}
		}

		public static void SetCustomSqlServerAdapter()
		{
			if (typeof(Dapper.Contrib.Extensions.SqlMapperExtensions)
				.GetField("AdapterDictionary", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static)?.GetValue(null) 
				is Dictionary<string, ISqlAdapter> adapters)
			{
				adapters["sqlconnection"] = new SqlServerAdapter();
			}
		}

		public partial class SqlServerAdapter : ISqlAdapter
		{
			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 idp = propertyInfos[0];
				if (idp.PropertyType.IsAssignableFrom(typeof(int)))
				{
					idp.SetValue(entityToInsert, Convert.ChangeType(id, idp.PropertyType), null);
				}
				else
				{
					var converter = TypeDescriptor.GetConverter(idp.PropertyType);
					var newValue = converter.ConvertFrom(id);
					idp.SetValue(entityToInsert, newValue, null);
				}

				return id;
			}

			public void AppendColumnName(StringBuilder sb, string columnName)
			{
				sb.AppendFormat("[{0}]", columnName);
			}

			public void AppendColumnNameEqualsValue(StringBuilder sb, string columnName)
			{
				sb.AppendFormat("[{0}] = @{1}", columnName, columnName);
			}

			public async Task<int> InsertAsync(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 = await connection.QueryMultipleAsync(cmd, entityToInsert, transaction, commandTimeout).ConfigureAwait(false);

				var first = await multi.ReadFirstOrDefaultAsync().ConfigureAwait(false);
				if (first == null || first.id == null) return 0;

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

				var idp = pi[0];
				if (idp.PropertyType.IsAssignableFrom(typeof(int)))
				{
					idp.SetValue(entityToInsert, Convert.ChangeType(id, idp.PropertyType), null);
				}
				else
				{
					var converter = TypeDescriptor.GetConverter(idp.PropertyType);
					var newValue = converter.ConvertFrom(id);
					idp.SetValue(entityToInsert, newValue, null);
				}

				return id;
			}
		}
	}

There is some setup that needs to be defined.