Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgresql JsonB issue with Update statement with JToken property #636

Open
IGionny opened this issue May 25, 2021 · 0 comments
Open

Postgresql JsonB issue with Update statement with JToken property #636

IGionny opened this issue May 25, 2021 · 0 comments

Comments

@IGionny
Copy link

IGionny commented May 25, 2021

Hi!
I have a strange issue executing an update statement with a POCO that contains a JToken.
(ps: I n

Configuration:
I made a custom Mapper from DefaultMapper like the one described here: #326 to save/read JToken properties as JsonB in Postgresql.
I also add the 'NpgsqlConnection.GlobalTypeMapper.UseJsonNet();' configuration as described here: https://www.npgsql.org/doc/types/jsonnet.html

public class MyNpocoMapper : DefaultMapper
    {
        public override Func<object, object> GetParameterConverter(DbCommand dbCommand, Type sourceType)
        {
            if (sourceType == typeof(JToken) || sourceType == typeof(Newtonsoft.Json.Linq.JObject) ||
                sourceType == typeof(Newtonsoft.Json.Linq.JArray))
            {
                return x => new NpgsqlParameter
                {
                    NpgsqlDbType = NpgsqlDbType.Jsonb,
                    Value = x?.ToString()
                };
            }

            return base.GetParameterConverter(dbCommand, sourceType);
        }


        public override Func<object, object> GetToDbConverter(Type destType, MemberInfo sourceMemberInfo)
        {
            if (destType == typeof(JToken) || destType == typeof(Newtonsoft.Json.Linq.JObject) ||
                destType == typeof(Newtonsoft.Json.Linq.JArray))
            {
                return x => x;
            }

            return base.GetToDbConverter(destType, sourceMemberInfo);
        }
        
        public override Func<object, object> GetFromDbConverter(Type destType, Type sourceType)
        {
           
            if (destType == typeof(JToken) || destType == typeof(Newtonsoft.Json.Linq.JObject) ||
                destType == typeof(Newtonsoft.Json.Linq.JArray))
            {
                return x =>
                {
                    if (x is string serialized)
                    {
                        return JToken.Parse(serialized);
                    }

                    return null;
                };
            }

            return base.GetFromDbConverter(destType, sourceType);
        }
    }

The poco classes and the example of insert

public class MartianJson
    {
        public string Name { get; set; }
        public JToken Data { get; set; }
    }

public class MartianJsonFooClass
    {
        public string Name { get; set; }
        public decimal MoneyBank { get; set; }
        public bool IsGood { get; set; }
        public DateTime BirthDateAt { get; set; }
    }

var item = new MartianJson();
item.Name = "Test";

var fooData = new MartianJsonFooClass();
fooData.Name = "Test";
fooData.IsGood = true;
fooData.MoneyBank = 13424242.43M;
fooData.BirthDateAt = new DateTime(2021, 05, 12, 12, 43, 0, DateTimeKind.Utc);

item.Data = JToken.FromObject(fooData);

await repo.InsertAsync(item).ConfigureAwait(false);

The insert/get statement works perfectly.

When I try to update the same Poco I receive an exception:
The CLR array type Newtonsoft.Json.Linq.JProperty isn't supported by Npgsql or your PostgreSQL.

Diggin in I discover that the issue reside into Sql class on Build method because the Database.UpdateStatements.PreparedUpdateStatement method make this (correct) query:

UPDATE "martianjson" SET "name" = @0, "data" = @1, "createdby" = @2, "updatedby" = @3, "createdat" = @4, "updatedat" = @5 WHERE "id" = @6

But after Sql.Build with ParameterHelper.ProcessParams transform it into:
UPDATE "martianjson" SET "name" = @0, "data" = @1,@2,@3,@4, "createdby" = @5, "updatedby" = @6, "createdat" = @7, "updatedat" = @8 WHERE "id" = @9

as you can see "data" = @1 became "data" = @1,@2,@3,@4: seems that it 'explode' all the properties of the JToken in sub-parameters (of JsonProperty type..)

Someone can give me an help?

Thank you in advance!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants