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 ISqlAdapter
s) 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 ISqlAdapter
s) 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.