Repodb: Feature: Support PropertyHandlerAttribute as requested.

[mikependon]

Description:

This is an easy development, but it is a big feature! But should be easy to be incorporated within the library.

In the IL and compiled-expressions, consider a type handlers/resolvers during the transformation process. With this, we allow the control to the consumer of the library on how are they going to handle specific values of the columns 2-ways (getter, setter).

Usability:

public interface IResolver<T1, TResult>
{
    TResult Resolve(T1 input);
}

public class PersonAddressGetResolver : IResolver<string, Address>
{
    public Address Resolve(string input)
    {
        return JSON.Deserialize<Address>(input);
    }
}

public class PersonAddressSetResolver : IResolver<Address, string>
{
    public string Resolve(Address input)
    {
        return JSON.Serialize(input);
    }
}

public class PropertyHandlerAttribute : Attribute
{
    public ResolveAttribute(IResolver<T1, TResult> getResolver,
        IResolver<T1, TResult> setResolver)
    {}

    public IResolver<T1, TResult> GetResolver { get; }

    public IResolver<T1, TResult> SetResolver { get; }
}

public class Address
{
    public int Id { get; set; }
    public string State { get; set; }
    public int ZipCode { get; set; }
    public string Country { get; set; }
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    [Map("AddressAsJson"), PropertyHandler(typeof(PersonAddressGetResolver), typeof(PersonAddressSetResolver))]
    public Address Address { get; set; }
}

The GetResolver and SetResolver must be compiled AOT so the performance of the ILs and compiled-Expressions must not be affected.

Use Cases/Business Cases

This handlers can solve a lot of Use Cases or Business Scenarios. See some below.

• Handle the property value
• Handle the entity relationships
• Handle the conversion (JSON to Class, Column Value to ENUM, or whatever...)
• Can handle even the auditing/tracing
• And many more

Acceptance Criteria

• Performance should not be affected.
• Unit Tests must be provisioned for (PropertyValue, EntityRelationships, Conversion).
• Integration Tests must be provisioned for (PropertyValue, EntityRelationships, Conversion).

Impact

It will impact the Core implementation (all extended library). Ensure that all Unit Tests and Integration Tests are all running.

Related

There is a relevant information that we can get from https://github.com/mikependon/RepoDb/issues/366 issue.

[mikependon]

@ffandreassoroko - created this feature. I will open this one up as a question to the public for community support and approval. Would you be able to verify before I do so? Thanks!

[mikependon]

@FransBouma @Grauenwolf - Hi Experts, can I gather your support on this feature? Any feedback to this before I dig down implementing it? Thanks!

[Grauenwolf]

I'm willing to help with SQL questions, but you're on your own on this one.

[mikependon]

I'm willing to help with SQL questions, but you're on your own on this one.

Thanks! The feature is more on client-side implementation. I am thinking in a way of asking feedback whether this is something relevant or not. In anyway, thanks for the prompt reply and I will handle this on my own for now.

[FransBouma]

Yeah same as @Grauenwolf here :) It's a relevant feature, especially for e.g. Oracle which lacks a boolean type for instance. It's not easy to do though, so make sure you think things through.

[mikependon]

It's not easy to do though, so make sure you think things through.

Thanks @FransBouma , will do more rethinking before stumbling through it.

[mikependon]

Deployed. Will be available in version >= 1.10.5.

[mikependon]

@ffandreassoroko - would you be able to check this feature? It has been released 3 days ago at version 1.10.5.

To help you start with, you can visit this Wiki page.

Please feel free to revert anytime. Thanks!

[andreas-soroko]

@mikependon

database: postgres

[Map("enum_test_table")]
private class TestModel
{
    [Primary, Map("id")]
    public Guid Id { get; set; }

    [PropertyHandler(typeof(JsonValueConverter<JObject>)), Map("json_data")]
    public JObject JsonData { get; set; }
}

/// JsonValueConverter
public class JsonValueConverter<T> : IPropertyHandler<string, T>
{
    private JsonSerializerSettings _jsonSerializerSettings = JsonConvert.DefaultSettings?.Invoke();

    public JsonValueConverter()
    {
    }

    public JsonValueConverter(JsonSerializerSettings jsonSerializerSettings)
    {
        _jsonSerializerSettings = jsonSerializerSettings;
    }
    public T Get(string input, ClassProperty property)
    {
        if (string.IsNullOrWhiteSpace(input)) return default;
        return JsonConvert.DeserializeObject<T>(input, _jsonSerializerSettings);
    }

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

is there a way to specify the type ?

Npgsql.PostgresException : 42804: column "json_data" is of type jsonb but expression is of type text
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar()
   at RepoDb.DbConnectionExtension.InsertAsyncInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder, Boolean skipIdentityCheck)
   at RepoDb.DbRepository`1.InsertAsync[TEntity](TEntity entity, String hints, IDbTransaction transaction)

some working code

await using var cmd = new NpgsqlCommand("INSERT INTO test_table (id, json_data) VALUES (@p1, @p2)", connection);
    cmd.Parameters.Add(new NpgsqlParameter
    {
        ParameterName = "p1",
        Value = Guid.NewGuid()
    });
    cmd.Parameters.Add(new NpgsqlParameter
    {
        ParameterName = "p2",
        NpgsqlDbType = NpgsqlDbType.Json,
        Value = JsonSerializer.Serialize(new
        {
            testData = "123"
        })
    });
    cmd.ExecuteNonQuery();

[mikependon]

You can actually use the [TypeMap] attribute here - for this special scenario. The problem is, I cannot see the correct conversion of 'NpgslDbType.JsonB' to its equivalent 'DbType' (https://www.npgsql.org/doc/types/basic.html).

Can you play around like below?

[Map("enum_test_table")]
private class TestModel
{
    [Primary, Map("id")]
    public Guid Id { get; set; }

    [PropertyHandler(typeof(JsonValueConverter<JObject>)), Map("json_data"), TypeMap(DbType.String)]
    public JObject JsonData { get; set; }
}

[andreas-soroko]

Can you play around like below?

same exception

[mikependon]

I will get back to you on this, this seems to be a big problem on the data types mapping between different DB providers. I think I cannot control this :) -- unless, I will make a handler to every conversion.

[andreas-soroko]

maybe it make sense pass the *DbParamter into the function - what do you think ?
so the end user can decide a bit better the dbTypes / value without any big logics from you

[mikependon]

Well, NpgsqlDbParameter is a DbParameter. I will analyze how they do conversion of the 'JSONB' DbType. This is a good thing to analyze the difference between SQL Server and PostgreSQL :) -- I will get back to you and hopefully can recommend an alternative.

[mikependon]

I had investigated this one. I do not have the solutions on how to resolve the NpgsqlDbType equation to DbType. The db type of Json != String, whereas if you read it via reader.GetFieldType(ordinal), it returns String. Non sense! :)

This should be a bug in Npgsql, I will contact Shay Rojansky of Npgsql regarding this scenario.

Your issue will be solved if you change the data type of your column from 'Json/JsonB' to 'Text' in the DB.

[andreas-soroko]

Your issue will be solved if you change the data type of your column from 'Json/JsonB' to 'Text' in the DB.

this is not really an option regard to the indexes in the jsonb field

[mikependon]

Make since, I contacted the Npgsql resource about this and will wait for their reply. In RepoDb.Core, I do not have any references to Npgsql libraries. That is why :)

But I will get back to you.

[andreas-soroko]

just as note, ( updated to 1.10.8 ) an now the clr failure is gone but it seems that postgres enums have the same problem

column "pg_type" is of type enum_test but expression is of type text

[mikependon]

Yes, that is correct. I need the replies from Npgsql team first before doing any change on the RepoDb.Core. They might have any alternative solution to this, otherwise, I need to add more codes in the RepoDb compilers.

[mikependon]

Replies from Shay. I think I need to fix it at RepoDb.Core.

GetFieldType returns the default CLR type that is returned for the column (.NET String for PG jsonb). DbType and NpgsqlDbType represent a PG type; DbType.String corresponds to PG text, NpgsqlDbType.Jsonb corresponds to PG jsonb.

[mikependon]

More good explanation by Shay.

DbType and NpgsqlDbType are things that tell Npgsql (the frontend driver) which PG type to specify for parameters it sends to PG. DbType is the database-independent enum from http://ADO.NET, since JSON isn't a standard type it isn't in there (see https://github.com/dotnet/runtime/issues/30677)

Already opened-up since August 2019. See the request here.