BulkInsertAsync, error when field has a custom type

[Alexey39dev]

Description

Postgre database. The field in the table has a custom type:
Verstamp public.myusertype NOT NULL DEFAULT 0::bigint.
I am performing batch insertion of rows into the table and I get an error from row:
await context.BulkInsertAsync(people); .

Rows are inserted into the table, an error occurs when receiving data from the Verstamp field.

Exception

Exception message:

System.NotSupportedException: "The field 'verstamp_zzzinserted' has type 'public.myusertype', which is currently unknown to Npgsql. You can retrieve it as a string by marking it as unknown, please see the FAQ."

Stack trace:

at Npgsql.Internal.TypeHandlers.UnknownTypeHandler.Read(NpgsqlReadBuffer buf, Int32 byteLen, Boolean async, FieldDescription fieldDescription)
at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler.d__41.MoveNext() at System.Threading.Tasks.ValueTask1.get_Result()
at Npgsql.Internal.TypeHandling.NpgsqlTypeHandler1.<ReadAsObject>d__2.MoveNext() at System.Threading.Tasks.ValueTask1.get_Result()
at Npgsql.NpgsqlDataReader.GetValue(Int32 ordinal)
at Npgsql.NpgsqlDataReader.GetValues(Object[] values)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at .(DbCommand , BulkOperation , Int32 )
at .( , DbCommand )
at .Execute(List1 actions) at .(List1 )
at Z.BulkOperations.BulkOperation.Execute()
at Z.BulkOperations.BulkOperation.BulkInsert()
at .BulkInsert[T](DbContext this, IEntityType entityType, IEnumerable1 list, Action1 options, SavingSelector savingSelector, Boolean forceSpecificTypeMapping, Boolean isOptimized)
at .BulkInsert[T](DbContext this, IEnumerable1 entities, Action1 options, Boolean isBulkSaveChanges, Boolean isOptimized)
at DbContextExtensions.BulkInsert[T](DbContext this, IEnumerable1 entities, Action1 options)
at DbContextExtensions.1.() at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location --- at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at .1.MoveNext()
at EFCoreBulkInsertExample.Program.

d__0.MoveNext() в \EFCoreBulkInsertExample\Program.cs:строка 25

Generated SQL command:

  -- Executing Command:
  INSERT INTO "public"."persons" AS DestinationTable
  ("id", "age", "name")
  SELECT  "id", "age", "name"
  FROM    (
  SELECT @0_0 AS "id", @0_1 AS "age", @0_2 AS "name", @0_3 AS ZZZ_Index
  UNION ALL SELECT @1_0 AS "id", @1_1 AS "age", @1_2 AS "name", @1_3 AS ZZZ_Index
  UNION ALL SELECT @2_0 AS "id", @2_1 AS "age", @2_2 AS "name", @2_3 AS ZZZ_Index
  ) AS StagingTable
  ORDER BY StagingTable.ZZZ_Index
  RETURNING 'INSERT' AS "$action", DestinationTable."verstamp" AS "verstamp_zzzinserted"
  ;
  -- @0_0: 1 (Type = Int64, Size = 0)
  -- @0_1: 10 (Type = Int32, Size = 0)
  -- @0_2: Name1 (Type = String, Size = 0)
  -- @0_3: 0 (Type = Int32, Size = 0)
  -- @1_0: 2 (Type = Int64, Size = 0)
  -- @1_1: 20 (Type = Int32, Size = 0)
  -- @1_2: Name2 (Type = String, Size = 0)
  -- @1_3: 1 (Type = Int32, Size = 0)
  -- @2_0: 3 (Type = Int64, Size = 0)
  -- @2_1: 30 (Type = Int32, Size = 0)
  -- @2_2: Name3 (Type = String, Size = 0)
  -- @2_3: 2 (Type = Int32, Size = 0)
  -- CommandTimeout:0

Project

User type and table definition:

CREATE TYPE public.myusertype AS (
    version bigint
);
CREATE TABLE IF NOT EXISTS public.Persons
(
    Id bigint NOT NULL,
    Name varchar(100) NOT NULL,
    Age int NOT NULL,
    Verstamp public.myusertype NOT NULL DEFAULT 0::bigint,
    CONSTRAINT PK_Persons_Id PRIMARY KEY (Id)
)

Table description class:

[Table("persons", Schema = "public")]
public class Person
{
    [Column("id")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; } = string.Empty;

    [Column("age")]
    public int Age { get; set; }

    [Column("verstamp")]
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public byte[] Verstamp { get; set; }
}

Further technical details

• EF version: [EF Core v6.0.8]
• EF Extensions version: [EFE Core v6.103.2]
• Database Server version: [PostgreSQL 16.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit]
• Database Provider version (NuGet): [Npgsql.EntityFrameworkCore.PostgreSQL v6.0.2]
  Project: EFCoreBulkInsertExample.zip

[JonathanMagnan]

Hello @Alexey39dev ,

Indeed our library doesn't yet support custom type like your, and as you added in your project, you currently need to ignore it with the option IgnoreColumnOutputExpression

We are currently looking if that's possible to add the support.

Best Regards,

Jon