NpgsqlConnection does not read jsonb columns for inherited DTO classes

Viewed 220

I have got a minimal repro here https://github.com/PaloMraz/ReadJsonbArraysWithDtoInheritance/blob/master/Program.cs. Basically, I have got a table with jsonb column mapped to an DTO array using custom Dapper TypeHandler like this:

  static async Task Main(string[] args)
  {
    DefaultTypeMap.MatchNamesWithUnderscores = true;
    SqlMapper.AddTypeHandler(new NoteListTypeMapper());

    const string ConnectionString = "Host=localhost;Username=postgres;Port=5432;Password=postgres;Database=postgres";
    using(var connection = new NpgsqlConnection(ConnectionString))
    {
      await connection.OpenAsync(); // make sure all the code runs in the same session...

      await connection.ExecuteAsync("create temporary table product(id serial primary key, name text, notes jsonb);");
      await connection.ExecuteAsync("insert into product (name, notes) values ('ProductA', '[{\"id\": 1, \"content\": \"Note1 - A\"}, {\"id\": 2, \"content\": \"Note2 - A\"} ]'::jsonb);");
      await connection.ExecuteAsync("insert into product (name, notes) values ('ProductB', '[{\"id\": 2, \"content\": \"Note1 - B\"}, {\"id\": 2, \"content\": \"Note2 - B\"} ]'::jsonb);");

      // This loads the Notes jsonb column fine.
      List<Product> products = (await connection.QueryAsync<Product>("select * from product;")).ToList();
      Console.WriteLine(products[0]); // Prints: Product(1, ProductA): Note(1, Note1 - A), Note(2, Note2 - A)
      Console.WriteLine(products[1]); // Prints: Product(2, ProductB): Note(2, Note1 - B), Note(2, Note2 - B)

      // This does NOT load the Notes jsonb colum.
      List<ProductView> productViews = (await connection.QueryAsync<ProductView>("select * from product;")).ToList();
      Console.WriteLine(productViews[0]); // Prints: Product(1, ProductA):
      Console.WriteLine(productViews[1]); // Prints: Product(2, ProductB):
    }
  }

  internal class NoteListTypeMapper : SqlMapper.TypeHandler<IList<Note>>
  {
    public override IList<Note> Parse(object value)
    {
      if (value is string json && !string.IsNullOrEmpty(json))
      {
        return JsonConvert.DeserializeObject<IList<Note>>(json);
      }
      else
      {
        return new List<Note>();
      }
    }


    public override void SetValue(IDbDataParameter parameter, IList<Note> value)
    {
      parameter.Value = JsonConvert.SerializeObject(value);
      if (parameter is NpgsqlParameter postgresParameter)
      {
        postgresParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Jsonb;
      }
    }
  }
}

public class Note
{
  public int Id { get; set; }
  public string Content { get; set; } = "";
  public override string ToString() => $"Note({this.Id}, {this.Content})";
}


public class Product
{
  public int Id { get; set; }
  public string Name { get; set; } = "";
  public IList<Note> Notes { get; } = new List<Note>();

  public override string ToString() => $"Product({this.Id}, {this.Name}): " + string.Join(", ", this.Notes);
}


public class ProductView : Product
{
  public string Manufacturer { get; set; } = "";
}

The problem is that by querying with Product type connection.QueryAsync<Product>, it works (the embedded Notes list is deserialized correctly), but when using ProductView type connection.QueryAsync<ProductView>, it does not work (the Notes field is empty).

Is this supposed to work? Am I missing something?

Thank you for your help!

Regards,

Palo

1 Answers

Discovered the reason; a quick workaround is to make the collection property settable as stated in the comment for the Product.Notes property in the updated GitHub repo https://github.com/PaloMraz/ReadJsonbArraysWithDtoInheritance:

/// <summary>
/// If this is read-only, Dapper will not deserialize the property from DataReader for subclasses,
/// e.g <see cref="ProductView"/>. This is because Dapper internally tries to deserialize the compiler-
/// generated backing field, which is present only in the base class. When the property is settable
/// (even with private setter!), Dapper deserializes it correctly for derived classes also.
/// </summary>
public IList<Note> Notes { get; private set; } = new List<Note>();

Related