PostgreSQL: Failes to BulkInsert entity with JSONb column configured with recommended Owned Entity Mapping

[r-Larch]

PostgreSQL: Failes to BulkInsert entity with JSONb column configured with recommended Owned Entity Mapping.

It looks like EFCore.BulkExtensions handles the Jsonb columns like a relation navigation property but it is configured as Owned Entity with conversion to JSON.

The property is configured with: builder.OwnsOne(_ => _.Metadata, _ => _.ToJson()); docs:npgsql.org

This code throws the exception:

var config = new BulkConfig {
    SetOutputIdentity = false,
    LoadOnlyIncludedColumns = false,
};
await db.BulkInsertAsync(judgments, config, cancellationToken: token);

Exception: (Metadata is a Jsonb column not a relation)

42703: column "Metadata" of relation "judgments" does not exist
Npgsql.PostgresException (0x80004005): 42703: column "Metadata" of relation "judgments" does not exist
   at Npgsql.Internal.NpgsqlConnector.ReadMessageLong(Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder`1.StateMachineBox`1.System.Threading.Tasks.Sources.IValueTaskSource<TResult>.GetResult(Int16 token)
   at Npgsql.NpgsqlBinaryImporter.Init(String copyFromCommand, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlConnection.BeginBinaryImport(Boolean async, String copyFromCommand, CancellationToken cancellationToken)
   at EFCore.BulkExtensions.SqlAdapters.PostgreSql.PostgreSqlAdapter.InsertAsync[T](DbContext context, IEnumerable`1 entities, TableInfo tableInfo, Action`1 progress, Boolean isAsync, CancellationToken cancellationToken)
   at EFCore.BulkExtensions.SqlAdapters.PostgreSql.PostgreSqlAdapter.InsertAsync[T](DbContext context, IEnumerable`1 entities, TableInfo tableInfo, Action`1 progress, Boolean isAsync, CancellationToken cancellationToken)
   at EFCore.BulkExtensions.SqlAdapters.PostgreSql.PostgreSqlAdapter.InsertAsync[T](DbContext context, Type type, IEnumerable`1 entities, TableInfo tableInfo, Action`1 progress, CancellationToken cancellationToken)
   at EFCore.BulkExtensions.SqlBulkOperation.InsertAsync[T](DbContext context, Type type, IEnumerable`1 entities, TableInfo tableInfo, Action`1 progress, CancellationToken cancellationToken)
   at EFCore.BulkExtensions.DbContextBulkTransaction.ExecuteAsync[T](DbContext context, Type type, IEnumerable`1 entities, OperationType operationType, BulkConfig bulkConfig, Action`1 progress, CancellationToken cancellationToken)
   at App.Web.Features.Judgments.Services.JudgmentImportHandler.<>c__DisplayClass7_0.<<Import>g__ImportBatch|0>d.MoveNext() in C:\Projects\LarchSys\App\App.Web\Features\Judgments\Services\JudgmentImportHandler.cs:line 118
--- End of stack trace from previous location ---
   at App.Web.Features.Judgments.Services.JudgmentImportHandler.Import(Spider spider, SpiderLogger logger, CancellationToken token) in C:\Projects\LarchSys\App\App.Web\Features\Judgments\Services\JudgmentImportHandler.cs:line 57
   at App.Web.Features.Judgments.Services.JudgmentImportHandler.Import(Spider spider, SpiderLogger logger, CancellationToken token) in C:\Projects\LarchSys\App\App.Web\Features\Judgments\Services\JudgmentImportHandler.cs:line 56
   at App.Web.Features.Judgments.Services.JudgmentImportHandler.Import(Spider spider, SpiderLogger logger, CancellationToken token) in C:\Projects\LarchSys\App\App.Web\Features\Judgments\Services\JudgmentImportHandler.cs:line 60
  Exception data:
    Severity: ERROR
    SqlState: 42703
    MessageText: column "Metadata" of relation "judgments" does not exist
    File: copy.c
    Line: 777
    Routine: CopyGetAttnums

Entity with configured Jsonb column:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;


namespace App.Data.Entities;

public class Judgment {
    [MaxLength(30)]
    public required string Id { get; set; }

    // ...

    [Column(TypeName = "jsonb")]
    public JudgmentMetadata Metadata { get; set; } = null!;

    // ...
}

public class JudgmentMetadata {
    public string[] Authors { get; set; } = null!;
    public string[] Sources { get; set; } = null!;
    public string[] Tags { get; set; } = null!;
}

public class JudgmentConfiguration : IEntityTypeConfiguration<Judgment> {
    public void Configure(EntityTypeBuilder<Judgment> builder)
    {
        // configure JSON column:
        // see: https://www.npgsql.org/efcore/mapping/json.html#tojson-owned-entity-mapping
        builder.OwnsOne(_ => _.Metadata, _ => _.ToJson());
    }
}

EF Context Setup for PostgreSQL

services.AddDbContext<AppDbContext>(_ => _
    .UseNpgsql(connectionString, _ => _
        .EnableRetryOnFailure()
        .UseVector()
    )
    .UseSnakeCaseNamingConvention()
);

[r-Larch]

In my commit I did a small hackfix to solve the issue for me.

This code probably only works in my case for BulkInsert on PostgreSQL
but it may serve as a starting point to solve the issue.

It looks like the context.Model.GetRelationalModel() contains all the required information.

var table = context.Model.GetRelationalModel().Tables.First(_ => _.Name == TableName);
string column = table.FindColumn(columnName)!;
string columnType = column.StoreType;
string sqlValue = column.StoreTypeMapping.GenerateProviderValueSqlLiteral(value).Trim('\'');

ColumnName can be obtained using:

INavigation jsonProperty = ..
string columnName = (string) jsonProperty.TargetEntityType.GetAnnotation("Relational:ContainerColumnName").Value!;

I won't make a pull request with my commit because it probably breaks other functionality.

Best regards
René

[zakeryooo]

@borisdj Any plans of an official fix for this (not requesting/pressuring, I'm already very grateful for this library, just curious if it was on the radar)? I'm not yet knowledgeable enough to confidently fix it myself or understand the potential side effects of the patch above. Just checking because if there's no fix planned I'll just refactor my model to not use owned jsonb columns for now. Thanks!

[borisdj]

Will try to work it out in the following days.