Repodb: Question: IPropertyHandler for dictionary<string,string>

[kbilsted]

In .Net Core 3 when serializing a dictionary to a column I get the error

System.ArgumentException : No mapping exists from object type System.Collections.Generic.KeyValuePair`2[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] to a known managed provider native type.

   at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)

   at System.Data.SqlClient.MetaType.GetMetaTypeFromType(Type dataType)

   at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()

   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)

   at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)

   at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite, String method)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.ExecuteReader()

   at RepoDb.DbConnectionExtension.ExecuteQueryInternal(IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, String tableName, Boolean skipCommandArrayParametersCheck)

   at RepoDb.DbConnectionExtension.ExecuteQuery(IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache)

Using the following

    public class JsonObjectTypeHandler : IPropertyHandler<string, Dictionary<string, string>>
    {
        public Dictionary<string, string> Get(string input, ClassProperty property)
        {
            return JsonConvert.DeserializeObject<Dictionary<string, string>>(input);
        }


        public string Set(Dictionary<string, string> input, ClassProperty property)
        {
            return JsonConvert.SerializeObject(input);
        }
    }

and trying to register it both as RepoDb.PropertyHandlerMapper.Add<Dictionary<string, string>, JsonObjectTypeHandler>();

and on my model object

    public class Foo
    {
        public Guid Id { get; set; }

        [PropertyHandler(typeof(JsonObjectTypeHandler))] 
        public Dictionary<string, string> Bars { get; set; }
   }

[mikependon]

I can assume this is really not handled by the library - an edge-case TBH. A normal custom type and primitive types are only supported, as we speak.

Are you blocked by this? Otherwise, you can make a small work around if such Dictionay object is wrapped within a custom class.

[kbilsted]

Thanks for the quick reply. We are trying to convert away from dapper due to its missing enum as string handling. And now we run into this. So we need a solution or we are not converting :)

[mikependon]

We need to win you as a user. When do you need the fix? I will be busy this weekend due to a Talk and Demo, but hopefully you can wait on the fix til next week?

[kbilsted]

I'm not sure I think Dictionary<,> is a special case., but sounds great with a release next week.

[mikependon]

@kbilsted - I had tested this scenario and it is working on the latest release of RepoDb.SqlServer v1.1.1. I followed your implementation with my own model and table schema.

Here is the sample project (DictionaryPropertyHandler.zip) for your reference.

If the issue still persists on your side, would you be able to provide more context for us to replicate the issue?

To simplify, you can modify the attached project and trigger the problem. Then, attached it back here. Thanks!

[kbilsted]

Thanks for the quick reply. At home I cannot reproduce the problem. I'll investigate further tomorrow.

I did manage to break the code though. It happens when I do more nested dictionaries.

System.InvalidOperationException
  HResult=0x80131509
  Message=Compiler.Entity/Object.Property: Failed to convert the value expression for property handler 'DictionaryPropertyHandler.PropertyHandlers.JsonObjectTypeHandler'. ClassProperty :: Name = MegaAddress (System.Collections.Generic.Dictionary`2[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Collections.Generic.Dictionary`2[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]], System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]), DeclaringType = DictionaryPropertyHandler.Models.Person

  Source=RepoDb
  StackTrace:
   at RepoDb.Reflection.Compiler.GetEntityInstancePropertyValueExpression(Expression entityExpression, ClassProperty classProperty, DbField dbField)
   at RepoDb.Reflection.Compiler.GetDbParameterValueAssignmentExpression(ParameterExpression parameterVariableExpression, Expression entityExpression, ParameterExpression propertyExpression, ClassProperty classProperty, DbField dbField, IDbSetting dbSetting)
   at RepoDb.Reflection.Compiler.GetParameterAssignmentExpression(ParameterExpression commandParameterExpression, Int32 entityIndex, Expression entityExpression, ParameterExpression propertyExpression, DbField dbField, ClassProperty classProperty, ParameterDirection direction, IDbSetting dbSetting)
   at RepoDb.Reflection.Compiler.GetPropertyFieldExpression(ParameterExpression commandParameterExpression, ParameterExpression entityExpression, FieldDirection fieldDirection, Int32 entityIndex, IDbSetting dbSetting)
   at RepoDb.Reflection.Compiler.CompileDataEntityDbParameterSetter[TEntity](IEnumerable`1 inputFields, IEnumerable`1 outputFields, IDbSetting dbSetting)
   at RepoDb.Reflection.FunctionFactory.CompileDataEntityDbParameterSetter[TEntity](IEnumerable`1 inputFields, IEnumerable`1 outputFields, IDbSetting dbSetting)
   at RepoDb.FunctionCache.DataEntityDbParameterSetterCache`1.Get(String cacheKey, IEnumerable`1 inputFields, IEnumerable`1 outputFields, IDbSetting dbSetting)
   at RepoDb.FunctionCache.GetDataEntityDbParameterSetterCompiledFunction[TEntity](String cacheKey, IEnumerable`1 inputFields, IEnumerable`1 outputFields, IDbSetting dbSetting)
   at RepoDb.Contexts.Providers.InsertExecutionContextProvider.CreateInternal[TEntity](IDbConnection connection, IEnumerable`1 dbFields, String tableName, IEnumerable`1 fields, String hints, IDbTransaction transaction, IStatementBuilder statementBuilder)
   at RepoDb.Contexts.Providers.InsertExecutionContextProvider.Create[TEntity](IDbConnection connection, String tableName, IEnumerable`1 fields, String hints, IDbTransaction transaction, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.InsertInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.InsertInternal[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Insert[TEntity,TResult](IDbConnection connection, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at DictionaryPropertyHandler.Program.DoWork() in C:\Users\fobo\Desktop\DictionaryPropertyHandler\DictionaryPropertyHandler\Program.cs:line 73
   at DictionaryPropertyHandler.Program.Main() in C:\Users\fobo\Desktop\DictionaryPropertyHandler\DictionaryPropertyHandler\Program.cs:line 15

  This exception was originally thrown at this call stack:
    System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(System.Linq.Expressions.ExpressionType, System.Linq.Expressions.Expression, System.Type) in UnaryExpression.cs
    System.Linq.Expressions.Expression.Convert(System.Linq.Expressions.Expression, System.Type, System.Reflection.MethodInfo) in UnaryExpression.cs
    System.Linq.Expressions.Expression.Convert(System.Linq.Expressions.Expression, System.Type) in UnaryExpression.cs
    RepoDb.Reflection.Compiler.ConvertExpressionToTypeExpression(System.Linq.Expressions.Expression, System.Type)
    RepoDb.Reflection.Compiler.ConvertExpressionToPropertyHandlerSetExpression(System.Linq.Expressions.Expression, RepoDb.ClassProperty, System.Type)
    RepoDb.Reflection.Compiler.GetEntityInstancePropertyValueExpression(System.Linq.Expressions.Expression, RepoDb.ClassProperty, RepoDb.DbField)

Inner Exception 1:
InvalidOperationException: No coercion operator is defined between types 'System.Collections.Generic.Dictionary`2[System.String,System.Collections.Generic.Dictionary`2[System.String,System.String]]' and 'System.Collections.Generic.Dictionary`2[System.String,System.String]'.

    public class Person
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public Dictionary<string, string> Address { get; set; }
        public Dictionary<string, Dictionary<string,string>> MegaAddress { get; set; }
        public DateTime CreatedDateUtc { get; set; }
    }

using DictionaryPropertyHandler.Models;
using DictionaryPropertyHandler.PropertyHandlers;
using Microsoft.Data.SqlClient;
using RepoDb;
using System;
using System.Collections.Generic;

namespace DictionaryPropertyHandler
{
    class Program
    {
        static void Main()
        {
            Initialize();
            DoWork();
        }

        static void Initialize()
        {
            SqlServerBootstrap.Initialize();

            // Property Level
            //FluentMapper
            //    .Entity<Person>()
            //    .PropertyHandler<JsonObjectTypeHandler>(e => e.Address);

            // Type Level
            PropertyHandlerMapper.Add<Dictionary<string, string>, JsonObjectTypeHandler>();
            PropertyHandlerMapper.Add<Dictionary<string, Dictionary<string,string>>, JsonObjectTypeHandler>();
        }

        static void DoWork()
        {
            var sql =
                @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=DemoRepoDB;Integrated Security=True;Connect Timeout=30;";
            using (var connection = new SqlConnection(sql))
            {

                var address = new Dictionary<string, string>
                {
                    { "Street","Springbanen" },
                    { "ZipCode","2820" },
                    { "Municipality","Gentofte" },
                    { "Country","Denmark" }
                };

                var mega = new Dictionary<string, Dictionary<string, string>>()
                {
                    {
                        "home", new Dictionary<string, string>()
                        {
                            {"street", "vestergade"},
                            {"phone", "123"}
                        }

                    },
                    {
                        "work", new Dictionary<string, string>()
                        {
                            {"street", "bunkevej"},
                            {"phone", "567"}
                        }
                    }
                };

                var entity = new Person
                {
                    Name = "John Doe",
                    Address = address,
                    MegaAddress = mega,
                    CreatedDateUtc = DateTime.UtcNow
                };
                var id = connection.Insert<Person, long>(entity);
                var executeQueryResult = connection.ExecuteQuery<Person>("SELECT * FROM [dbo].[Person];");
                var queryResult = connection.Query<Person>(id);
                var queryAllResult = connection.QueryAll<Person>();
                connection.Truncate<Person>();
            }
        }
    }
}

and sql
[MegaAddress] [nvarchar](max) NOT NULL,

[mikependon]

I tested this one and the issue is not happening even with the MegaAddress. Here, I updated the project for your reference. Please see the attached zip file again. DictionaryPropertyHandler.zip

Note: Please be reminded that you have to map the Property Handler as a targeted one. Do not map it on a Type-Level for this kind of purpose.

[kbilsted]

I think my example was too hasty and in fact it works with nesting as well

            PropertyHandlerMapper.Add<Dictionary<string, string>, JsonObjectTypeHandler<Dictionary<string,string>>>();
            PropertyHandlerMapper.Add<Dictionary<string, Dictionary<string,string>>, JsonObjectTypeHandler<Dictionary<string,Dictionary<string,string>>>>();

and

public class JsonObjectTypeHandler<T> : IPropertyHandler<string, T>
    {
        public T Get(string input, ClassProperty property)
        {
            return JsonConvert.DeserializeObject<T>(input);
        }

        public string Set(T input, ClassProperty property)
        {
            return JsonConvert.SerializeObject(input);
        }
    }

let me try to recreate the problem I originally logged..

[mikependon]

Yeah. It is good if you can replicate it and just extend the attached small project to simplify my debugging.
Again, please be aware of the impact of Type-Level property handler, multiple columns might be affected by it for as long the type is the same.

[mikependon]

@kbilsted - we are about to release the next minor version if we will receive no reports for the next 5 days. We are happy to include this one on that release or issue the next beta version with the fix for this issue and revalidate for the next 2 weeks.

Would you be able to help us replicate this? Otherwise, this will not be included.

[kbilsted]

Sorry for taking this long.

I can reproduce in your example code when I insert the data using SQL

change the code from the zip file

                //var id = connection.Insert<Person, long>(entity);
                CreateManually(connection, entity);

and

   private static void CreateManually(SqlConnection connection, Person entity)
        {
            var sql = @"INSERT INTO [dbo].[Person]
           ([Name]
           ,[Address]
           ,[MegaAddress]
           ,[CreatedDateUtc])
     VALUES
           (@Name
           ,@Address
           ,@MegaAddress
           ,@CreatedDateUtc)";

            connection.ExecuteNonQuery(sql, entity);
        }

[mikependon]

Oh, it seems to me again is because of the non-schema-based operations shinanigans. I will do check this and will get back to you right away. Thanks for replicating it.

[mikependon]

I just confirmed this now, I can replicate and will issue a fix for this. When do you need the fix for this?