Bug: PostgresException on merge in repository with PG

[evilz]

Bug Description

Merging like this

var record = new ProductModel
            {
                Key = CreateKeyFromId(siteId, product.Id),
                Content = new ProductContent(product.Id, product),
                ModificationDate = DateTime.Now,
                ModificationUser = username
            };
            var fields = Field.Parse<ProductModel>(e => new
            {
                e.Content,
                e.ModificationDate,
                e.ModificationUser
            });
            return Merge<string>(record,fields: fields);

throw a exception Npgsql.PostgresException (0x80004005): 42883: operator does not exist: @ text

A clear and concise description of what the issue is. Please give us the details below for fast replication and resolution. The more informative the more weight to be fixed ASAP.

Exception Message:

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: @ text
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar()
   at RepoDb.DbConnectionExtension.MergeInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.MergeInternal[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Merge[TEntity,TResult](IDbConnection connection, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbRepository`1.Merge[TEntity,TResult](TEntity entity, IEnumerable`1 fields, String hints, IDbTransaction transaction)
   at RepoDb.BaseRepository`2.Merge[TResult](TEntity entity, IEnumerable`1 fields, String hints, IDbTransaction transaction)
   at Services.ProductRepository.Save(Int32 siteId, Product product, String username) 
  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: operator does not exist: @ text
    Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
    Position: 224
    File: parse_oper.c
    Line: 726
    Routine: op_error

Command Text

INSERT INTO "vp_flashsales"."product" ( "content", "modificationdate", "modificationuser" ) VALUES ( @content, @modificationdate, @modificationuser ) ON CONFLICT ("key") DO UPDATE SET "content" = @content, "modificationdate" = @modificationdate, "modificationuser" = @modificationuser RETURNING @key AS "Result" ;

Library Version:

RepoDb.PostgreSql v1.1.3

[mikependon]

The merge operation does not support complex type on the property. Therefore, the property content is not supported on this regards.

What is the data type of the content column from your table? And, do you like to save it as a string represented text (i.e. JSON)?

[evilz]

You are right it's a jsonb

[mikependon]

Think of RepoDB as a library that acts as a bridge when passing your class properties to the target parameters when executing a command text.

I am not sure how the Npgsql library interprets the class object (.NET CLR custom type) if it is being pushed to the JSONB (type) columns on the underlying table.

Therefore, I am suspecting that the code below might not be supported.

Content = new ProductContent(product.Id, product),

Or, it might be that you are required to explicitly set the Content property of the ProductModel class into NpgsqlTypes.Jsonb. In RepoDB, you can do it like below. (It is like setting the NpgsqlParameter.NpgsqlType to NpgsqlType.Jsonb)

// Explicit
public class ProductModel
{
    ...

    [NpgsqlType.Jsonb]
    public ProductContent Content { get; set }

    ...
}

// Implicit
FluentMapper
    .Entity<ProductModel>()
    .PropertyValueAttributes(e =>
        e.Content, new NpgsqlTypeAttribute(NpgsqlType.Jsonb));

If either of the recommendation above is not working, we suggest to change the property of the Content property to string and test passing the actual JSON string on the Content property.

[evilz]

I'll give a tr. Thank a lot

[mikependon]

You can use the PropertyHandler if you are setting the property to JSON string to make your code more organize and clean.

[evilz]

Ok it's still not good even with string ^^

Npgsql.PostgresException (0x80004005): 42883: operator does not exist: @ text
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar()
   at RepoDb.DbConnectionExtension.MergeInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.MergeInternal[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Merge[TEntity,TResult](IDbConnection connection, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbRepository`1.Merge[TEntity,TResult](TEntity entity, IEnumerable`1 fields, String hints, IDbTransaction transaction)
   at RepoDb.BaseRepository`2.Merge[TResult](TEntity entity, IEnumerable`1 fields, String hints, IDbTransaction transaction)
   at Services.ProductRepository.Save(Int32 siteId, Product product, String username) in C:\DATA\GIT-VPTECH\boom\src\Services\Storage\ProductRepository.cs:line 58
   at ServicesTests.ProductTests.Should_upsert_product_from_repository() in C:\DATA\GIT-VPTECH\boom\Tests\ServicesTests\ProductRepositoryTests.cs:line 80
  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: operator does not exist: @ text
    Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
    Position: 224
    File: parse_oper.c
    Line: 726
    Routine: op_error

here the model :

using System;
using System.Collections.Generic;
using System.Text.Json;
using FlashsalesServices.ProductModel;
using NpgsqlTypes;
using RepoDb;
using RepoDb.Attributes;
using RepoDb.Interfaces;

namespace Services.Storage
{
    /// no used now 
    public class ProductContentPropertyHandler : IPropertyHandler<string, ProductContent>
    {
        public ProductContent Get(string input, ClassProperty property) =>
            (string.IsNullOrEmpty(input) 
                ? null 
                : JsonSerializer.Deserialize<ProductContent>(input, new JsonSerializerOptions(JsonSerializerDefaults.Web)))!;

        public string Set(ProductContent input, ClassProperty property) => JsonSerializer.Serialize(input, new JsonSerializerOptions(JsonSerializerDefaults.Web));
    }

    [Map("vp_flashsales.product")]
    public class ProductModel
    {

        [Identity]
        [Map("key")]
        public string Key { get; set; }

        // [NpgsqlTypeMap(NpgsqlDbType.Jsonb)]
        // [Map("content")]
        // [PropertyHandler(typeof(ProductContentPropertyHandler))]
        // public ProductContent Content { get; set; }
        [Map("creationdate")]
        public DateTime? CreationDate { get; set; }
        [Map("creationuser")]
        public string CreationUser { get; set; }
        [Map("modificationdate")]
        public DateTime? ModificationDate { get; set; }
        [Map("modificationuser")]
        public string ModificationUser { get; set; }

        [NpgsqlTypeMap(NpgsqlDbType.Jsonb)]
        [Map("content")]
        // [PropertyHandler(typeof(ProductContentPropertyHandler))]
        public string Content { get; set; }

        /// replace the ProductContentPropertyHandler  to try to merge using string from content
        public ProductContent ProductContent
        {
            get =>
                (string.IsNullOrEmpty(Content) 
                    ? null 
                    : JsonSerializer.Deserialize<ProductContent>(Content, new JsonSerializerOptions(JsonSerializerDefaults.Web)))!;

            set => Content = JsonSerializer.Serialize(value, new JsonSerializerOptions(JsonSerializerDefaults.Web));
        }

    }
    public record ProductContent(int Id, FlashsalesServices.ProductModel.Product Data);

}

the generated SQL look not so bad, note that I ask to save only 3 fields : "modificationdate", "modificationuser", "content"

INSERT INTO "vp_flashsales"."product" ( "modificationdate", "modificationuser", "content" ) 
VALUES ( $1, $2, $3 ) 
ON CONFLICT ("key") 
DO UPDATE SET "modificationdate" = $1, "modificationuser" = $2, "content" = $3 
RETURNING @key AS "Result" 

Thank for you help 🙏🏻

[evilz]

😱 My bad, I forgot the key in fields !!!

var fields = Field.Parse<ProductModel>(e => new
            {
                e.Key,    //  <--- add this since it's not auto generated 
                e.Content,
                e.ModificationDate,
                e.ModificationUser
            });

and then PropertyHandler is working perfectly

Sorry for all this