neisbut / npgsql.bulk Goto Github PK

I'm getting an error when trying to use an ambient transaction. Does this library support TransactionScope or am I possibly misusing/misconfiguring something?

Code:

using (var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
using (var context = new DbContext())
{
    var uploader = new NpgsqlBulkUploader(context);
    uploader.Insert(entities, onConflict); 

    //...Other changes with the context
    
    await context.SaveChangesAsync().ConfigureAwait(false);

    scope.Complete();
}

Error:
System.InvalidOperationException: An ambient transaction has been detected. The ambient transaction needs to be completed before beginning a transaction on this connection.
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.EnsureNoTransactions()
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransaction(IsolationLevel isolationLevel)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.<>c__DisplayClass18_0.b__0(DatabaseFacade database)
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.<>c__DisplayClass12_02.b__0(DbContext c, TState s)
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) in C:\projects\npgsql-entityframeworkcore-postgresql\src\EFCore.PG\Storage\Internal\NpgsqlExecutionStrategy.cs:line 28
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, Func2 operation, Func2 verifySucceeded, TState state)
at Microsoft.EntityFrameworkCore.ExecutionStrategyExtensions.Execute[TState,TResult](IExecutionStrategy strategy, TState state, Func2 operation)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.BeginTransaction(DatabaseFacade databaseFacade, IsolationLevel isolationLevel)
at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable1 entities, InsertConflictAction onConflict)

Hi,

I tried bulk updater in a complex datamodel. Unfortunately it fails in the generated WriterForInsertAction with a InvalidProgramException.
I tried to isolate the problem but have not been very successfull yet. It seems to be the problem when I want to insert an entity that might be linked in another entity.

Is there a way to debug this somehow? I tried to work around this a little bit, it seems to be related to the nullable-Path (underlying!=null). But my IL-Skills are not the best unfortunately. I was not able to create a repro case that I might be able to share.

Any ideas? The InvalidProgramException doesn't give more details.

Entity_a494b9d3_73cd_46c9_bb15_dc181ae254c8_1.WriterForInsertAction(Messlokation , NpgsqlBinaryImporter , OperationContext )
NpgsqlBulkUploader.WriteInsertPortion[T](IEnumerable1 list, EntityInfo mapping, NpgsqlConnection conn, String tempTableName, NpgsqlBulkCodeBuilder1 codeBuilder) Zeile 343
NpgsqlBulkUploader.Insert[T](IEnumerable1 entities, InsertConflictAction onConflict) Zeile 281 NpgsqlBulkUploader.Insert[T](IEnumerable1 entities) Zeile 218

Cheers,
Joscha

public class a
{
public  string a { get; set; }
}
public class b:a
{
}

Bulk.Insert(List<b>);

error:Property a not found

The idea of this project is great and much needed! Is it possible to use it without Entity Framework and rely on Npgsql only? The error messages from EF are extremely hard to debug at times, I got the "Sequence contains not matching elements" for some reason and very hard to find root cause by the exception.
Again, thanks for the great work!

Please update the version of the Npgsql.EntityFrameworkCore.PostgreSQL package to the stable version 5.0.0

With a heavy load and a limited number of simultaneously available connections (40 connections to the database), I get the following error:
Npgsql.PostgresException (0x80004005): 42P01: relation "_temp__a9f93f82_03f3_4137_8020_ecedb8cc7612_46" does not exist.
I use the following connection string "Host = ***; Port = ***; Database = ***; Username = ***; ***; Pooling = false; Minimum Pool Size = 0; Maximum Pool Size = 100; CommandTimeout = 600; No Reset On Close = true; "
Pooling = false is used on purpose, the connection to the database goes through pgbouncer.
As I understand it, after creating the temporary table, another connection is used to insert data, can this situation be avoided?

The nuget package, as it exists now, only supplies EF6 support when targeting a .NET Framework executable. EFCore is supported on .NET Framework through .NET Standard. While your package has a .NET Standard library, it cannot be applied to .NET Framework because the .NET Framework library takes precedent, and there is no way to override that action.

Would it be possible to break this into two nuget packages, one for EF6 and one for EFCore? Something along the lines of Npgsql.Bulk.EF6 and Npgsql.Bulk.EFCore? Both could have a .NET Framework and a .NET Standard library (EF6 is coming to .NET Core in 3.0).

NpgsqlBulkCodeBuilder line 158

else if (mi.ReturnType.GetTypeInfo().IsEnum)
{
     ilOut.Emit(OpCodes.Ldc_I4_S, (int)info.NpgsqlType);
     ilOut.Emit(OpCodes.Call, writeMethodFull.MakeGenericMethod(typeof(Int32)));
}

There should be check for enum:long.
Thanks for useful library!

Suppose I have the following entity:

public class BrandConfiguration : IEntityTypeConfiguration<Brand>
{
   public void Configure(EntityTypeBuilder<Brand> builder)
   {
      builder.UseXminAsConcurrencyToken()
         .HasKey(x => x.Id);
   }
}

Then the following code will fail:

var loader = new NpgsqlBulkUploader(_context);

return loader.InsertAsync(
	new[] { new Brand { Id = 1, Name = "test" } },
	InsertConflictAction.DoNothing()
);

Error:

System.InvalidOperationException : Sequence contains no matching element
   at System.Linq.ThrowHelper.ThrowNoMatchException()
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source, Func`2 predicate)
   at Npgsql.Bulk.NpgsqlHelper.<>c__DisplayClass1_0.<GetMetadata>b__1(IProperty x)
   at System.Linq.Enumerable.SelectEnumerableIterator`2.ToList()
   at Npgsql.Bulk.NpgsqlHelper.GetMetadata(DbContext context, Type type)
   at Npgsql.Bulk.NpgsqlBulkUploader.GetMappingInfo(Type type, String tableName)
   at Npgsql.Bulk.NpgsqlBulkUploader.CreateEntityInfo[T]()
   at Npgsql.Bulk.NpgsqlBulkUploader.<GetEntityInfo>b__44_0[T](Type x)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Npgsql.Bulk.NpgsqlBulkUploader.GetEntityInfo[T]()
   at Npgsql.Bulk.NpgsqlBulkUploader.InsertAsync[T](IEnumerable`1 entities, InsertConflictAction onConflict)
   at xxxx.Updating() in D:\xxxx.cs:line 283

I've found that this is caused by 'xmin' internal column, which isn't returned when querying table schema from DB, but is included in EF property list.

Note: this error appears only on EF Core 3.1. It was working properly when used with EF Core 2.2.

PostgreSQL version: 10.11

Sometimes insert operation crashes with exception ERROR Npgsql.PostgresException (0x80004005): 42P07: отношение "_temp_636983599622694647" уже существует

when we use an enum in our entity it generate a migration like this one

migrationBuilder.AlterDatabase().Annotation("Npgsql:Enum:myEnum1", "New,Submitted,Failed,AwaitingCapture") migrationBuilder.CreateTable( name: "mytable", columns: table => new { payer_id = table.Column<myEnum1>() },

this will error
System.NotImplementedException: Column type 'myEnum1' is not supported at NpgsqlDbType Npgsql.Bulk.NpgsqlBulkUploader.GetNpgsqlType(ColumnInfo info) at List<MappingInfo> Npgsql.Bulk.NpgsqlBulkUploader.GetMappingInfo(Type type, string tableName)+(MappingInfo x) => { } at void System.Collections.Generic.List<T>.ForEach(Action<T> action) at List<MappingInfo> Npgsql.Bulk.NpgsqlBulkUploader.GetMappingInfo(Type type, string tableName) at EntityInfo Npgsql.Bulk.NpgsqlBulkUploader.CreateEntityInfo<T>() at EntityInfo Npgsql.Bulk.NpgsqlBulkUploader.GetEntityInfo<T>()+(string x) => { } at TValue System.Collections.Concurrent.ConcurrentDictionary<TKey, TValue>.GetOrAdd(TKey key, Func<TKey, TValue> valueFactory) at EntityInfo Npgsql.Bulk.NpgsqlBulkUploader.GetEntityInfo<T>()
is there any way to map enum ? i've searched for enum in NpgsqlDbType but didn't found anything :/

I'm experiencing random timeouts when trying to insert a large number of entities (50,000+). I've tried increasing the command timeout via Database.SetCommandTimeout, however, NpgSqlBulkUploader does not appear to respect this setting.

Is there a specific way to set the command/connection timeout?

(Also, this library has been a huge help so far, so thank you for all your effort!)

Code:

using (var context = new DbContext())
{
    context.Database.SetCommandTimeout(TimeSpan.FromMinutes(30));

    var uploader = new NpgsqlBulkUploader(context);
    uploader.Insert(entities, onConflict); 
}

Exception:
Npgsql.NpgsqlException
HResult=0x80004005
Message=Exception while reading from stream
Source=Npgsql
StackTrace:
at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass31_0.<g__EnsureLong|0>d.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<g__ReadMessageLong|0>d.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Npgsql.NpgsqlDataReader.d__46.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.d__100.MoveNext()
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable`1 entities, InsertConflictAction onConflict)

Inner Exception 1:
IOException: Unable to read data from the transport connection: Connection timed out.

Inner Exception 2:
SocketException: Connection timed out

1. start 2 instances of the same types but with DIFFERENT schemas
2. bulk insert the 2 instances the same type of entity

BUG:
all the items are being inserted into 1 schema since the cache concurrent dictionary in NpgsqlBulkUploader.cs is blocking on type and not considering that the same type may exists on several schema (but on the same database)

Hi, I have tried Npgsql.Bulk for bulk inserts and everything seems to be great and performant except leaking large portions of memory. I have tried two variants of code and both are consuming 2GB of memory after few minutes.

// variant 1
while (!stoppingToken.IsCancellationRequested) {
    List<Message> messages = GetALotOfData();
    var uploader = new NpgsqlBulkUploader(_context);
    uploader.Insert(messages);
    _context.SaveChanges();
}

// variant 2
// private NpgsqlBulkUploader _uploader = new NpgsqlBulkUploader(_context);

while (!stoppingToken.IsCancellationRequested) {
    List<Message> messages = GetALotOfData();
    _uploader.Insert(messages);
    _context.SaveChanges();
}

Saving single topic and many messages

    public class Topic
    {
        [Key]
        public int TopicId { get; set; }
        public string Name { get; set; }
        public IEnumerable<Message> Messages { get; set; }
    }

    public class Message
    {
        [Key]
        public int MessageId { get; set; }
        public string Key { get; set; }
        public string Content { get; set; }

        public int TopicId { get; set; }
        public Topic Topic { get; set; }
    }

Image: mcr.microsoft.com/vscode/devcontainers/dotnetcore:0-5.0
Version: Npgsql.Bulk 0.9.0
Version: Npgsql.EntityFrameworkCore.PostgreSQL 5.0.2

When I use HiLo entity keys like

builder.HasSequence("EFHiLoSequence100").IncrementsBy(100);
builder.UseHiLo("EFHiLoSequence100");

Then I manually set keys massively
But when I try

uploader.Insert(entityList)

this will error

Npgsql.PostgresException (0x80004005): 23502: null value in column "id" violates not-null constraint
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<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.ExecuteReader(CommandBehavior behavior)
at Npgsql.Bulk.NpgsqlBulkUploader.InsertPortion[T](IEnumerable1 list, List1 insertParts, NpgsqlConnection conn, NpgsqlBulkCodeBuilder1 codeBuilder, String tempTableName, Object ignoreDuplicatesStatement, InsertConflictAction onConflict) at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable1 entities, InsertConflictAction onConflict)
at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable`1 entities)

Suppose I have the following entity:

public class TestEntity
{
    public int Id { get; set; }

    public decimal Value { get; set; }

    public decimal ValueWithDefault { get; set; }
}

and the context:

public class TestDbContext : DbContext
{
    public DbSet<TestEntity> TestEntities { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql("Host=localhost;Port=5432;Database=npgsql-bulk-test;Username=postgres;Password=postgres;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TestEntity>()
            .HasKey(x => x.Id);

        modelBuilder.Entity<TestEntity>()
            .Property(x => x.Id)
            .ValueGeneratedNever();

        modelBuilder.Entity<TestEntity>()
            .Property(x => x.ValueWithDefault)
            .HasDefaultValue(123);
    }
}

This call will always fill the ValueWithDefault field with its default value, even if different one is specified (both for new entity and for updated one):

await new NpgsqlBulkUploader(context)
    .InsertAsync(new[]
        {
            new TestEntity {Id = id, Value = 100, ValueWithDefault = 200},
            new TestEntity {Id = id + 1, Value = 101, ValueWithDefault = 201}
        },
        InsertConflictAction.UpdateProperty<TestEntity>(on => on.Id,
            i => i.Value,
            i => i.ValueWithDefault));

Here is the repro: Npgsql.Bulk.Test.zip

Hi,
we are using NpgsqlBulkUploader.Insert to add entities with database generated serial id. The ids for created rows is not being set in entity objects. Are there plans to add this feature?

Br,
Szymon

Hi,
I tried to add a bulk of entities with many-to-many relationships but it failed and I got this message "23502: null value in column "Id" of relation "Entity" violates not-null constraint"

why it doesn't auto-increments ?

Some of the NpgsqlDbTypes are missing. I was using TimeSpan in my entity model which automatically maps to the "Interval"-type. On bulk upload of that entity I got "object reference not set to an instance of an object". I got around it my adding [Column(TypeName="time")] to the TimeSpan property, but it would be nice if NpgsqlDbType.Interval was supported as well.

Awesome lib btw!

On this line:

Perhaps it should be:

IsDbGenerated = x.ValueGenerated != ValueGenerated.Never && !x.GetAnnotations().Any(a => a.Name == "ValueGeneratorFactory")

When a Property includes this DBContext configuration.

.HasValueGenerator<MyOwnValueGenerator>() .ValueGeneratedOnAdd()

It is excluded from Insert statements because IsDbGenerated = true (the OnAdd ValueGenerated value). So the insert fails because of NULL value sent to database.

Are you planning to implement async versions of Import, Update and Insert?

Say for instance if there is a conversion on the property

eg: Converting array into Json object in the database like the example below

modelBuilder.Entity()
.Property(p => p.Cc)
.HasConversion(v => JsonConvert.SerializeObject(v),
c => JsonConvert.DeserializeObject<Participant[]>(c));
modelBuilder.Entity()
.Property(p => p.From)
.HasConversion(v => JsonConvert.SerializeObject(v),
c => JsonConvert.DeserializeObject<Participant[]>(c));

this library will crash with exception:
"Can't write CLR type System.String[] to database type text"

Which I know why but can't seems to get reflection code to write these value out

Hi,

First, sorry my Eng's not good.

Second, I have a project using entity framework core 3.0 and running SQL Server 2016 with some properties of entities are json.

Example:

public class Role
{
        public Role()
        {
            Priority = 1;
        }
        public string Name { get; set; }
        public string Description { get; set; }
        public int Priority { get; set; }
        public Object Property { get; set; }
}

and DbContext:

 builder.Entity<Role>(entity =>
            {
                entity.Property(role => role.Property).HasConversion(
                    role => JsonConvert.SerializeObject(role, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }),
                    role => JsonConvert.DeserializeObject<Object>(role, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore })
                    );
                entity.HasIndex(role => new { role.Id, role.Name }).Include<Role>(role => new { role.Description, role.Property }).IsUnique();
            });

And when i execute bulk insert have error: Can't write CLR type Newtonsoft.Json.Linq.JObject with handler type TextHandler.

I want model and dbcontext can switch between SQL Server 2016 and postgresql

Update: when i use,

dbContext.Roles.AddRange(entitites); 
dbContext.SaveChanges();

It's work.

Thanks for your support

I have two same tables (so one class in C#) in different schemas. And having two instances of NpgsqlBulkUploader (for differents schemas), both of them insert values to one table.
The problem will be solved, if this static dictionary's key in NpgsqlBulkUploader contains also schema name.

  private EntityInfo GetEntityInfo<T>()
        {
            var key = $"{context.GetType().FullName}-{typeof(T).FullName}";
            if (Cache.TryGetValue(key, out EntityInfo info))
            {
                return info;
            }

We faced with exception when we do things in following sequence:

1. Invoke

IEnumerable<NewsletterReceiver> data = ...;
 var uploader = new NpgsqlBulkUploader(_db);
 await uploader.InsertAsync(data);

2. Invoke

IEnumerable<NewsletterReceiver> data = ...;
var uploader = new NpgsqlBulkUploader(db);
await uploader.UpdateAsync(dataToUpdate, x => x.Completed, x => x.Status);

3. Then invoke first piece of code again and InsertAsync throws

The given key "NewsletterReceiver"."NewsletterId" was not present in the dictionary.
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at Npgsql.Bulk.ValueHelper`1.Get[TResult,TClr](T model, String propName, OperationContext opContext, TClr localValue)
   at NewsletterReceiver_551b9176_cacd_4ffc_adae_15a02116f856_1.WriterForInsertAction(NewsletterReceiver , NpgsqlBinaryImporter , OperationContext )
   at Npgsql.Bulk.NpgsqlBulkUploader.WriteInsertPortion[T](IEnumerable`1 list, EntityInfo mapping, NpgsqlConnection conn, String tempTableName, NpgsqlBulkCodeBuilder`1 codeBuilder)
   at Npgsql.Bulk.NpgsqlBulkUploader.InsertAsync[T](IEnumerable`1 entities, InsertConflictAction onConflict)

As I investigated, after first piece of code, the EntityInfo class was created by method CreateEntityInfo method with full list of properties and everything works well. Then library recreate another EntityInfo with CreateEntityInfo<T> overload with partial list of properties and caches it as first one. But during this two creations you redifine internal static property ValueHelper<T>.MappingInfos. Firstly with full list of properties (6 props), then with partial (3 props) and at 3rd invoking it uses cached EntityInfo and does not redefine ValueHelper<T>.MappingInfos again and can't find one of properties in partial list.

It happens in version 0.9.0, in previous 0.7.7.1 we used it works well. But we can't use it now because of migrating to net5.
Can you fix it, please?

My context is set up using fluent configuration, so the POCOs doesn't have any TableAttributes.

Hello, in version 0.8.0 on Core 3.1 I have an exception after "Insert".

Invalid cast from 'System.DBNull' to 'System.Nullable`1[[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]'.

It happens if I have a nullable navigation property:

public class Consumer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Transgas Transgas { get; set; }
    public int? TransgasId { get; set; }
}

public class Transgas
{
    public int Id { get; set; }
    public string Name { get; set; }
}

If I hide property "public Transgas Transgas { get; set; }" all will be okay

Test context:

   public class MyDb : DbContext
    {
        public DbSet<Consumer> Consumers { get; set; }
        public DbSet<Transgas> Transgases { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {   
         optionsBuilder.UseNpgsql("Host=127.0.0.1;Port=5432;Database=TestBulk;Username=postgres;Password=123;Timeout=120;Command Timeout=300");
        }
    }

Entry point:

  class Program
    {
        static void Main(string[] args)
        {
            var data = GetConsumers().ToArray();

            var db = new MyDb();
            var bulk = new NpgsqlBulkUploader(db);
            bulk.Insert(data); // Invalid cast from 'System.DBNul
        }

        static IEnumerable<Consumer> GetConsumers()
        {
            yield return new Consumer
            {
                Name = "My test name"
            };
        }
    }

0.7.6.1 lists netstandard2.0/2.1 with listed dependencies, but only the 2.0 library is included in the package.

Edit: this is regarding the NpgsqlBulk.EFCore package.

The GetNpgsqlType method in NpgsqlBulkUploader returns a NpgsqlDbType.
The list of Npgsql DB types doesn't include an enum type, although PostgreSQL does support enums.
https://www.npgsql.org/doc/api/NpgsqlTypes.NpgsqlDbType.html

So it seems obvious why Npgsql.Bulk doesn't support Enum columns at the moment.
But I'm wondering if there is a way Enum columns might be supported nonetheless?

Hello,
I'm getting an issue with an Insert operation against a semi-basic schema (23502: null value in column "Id" violates not-null constraint).

Here is my data structure:

public class Event
	{
		[Key]
		public Guid Id { get; set; }

		public Geometry Location { get; set; }

		public JsonElement ExtensionData { get; set; }
	}

Hi,

I don't know if EF Core 3.0 is supported by this library, but I'm trying to upgrade a project to that version and I'm getting this exception when using the BulkUploader :

System.TypeLoadException: Could not load type 'Microsoft.EntityFrameworkCore.RelationalMetadataExtensions' from assembly 'Microsoft.EntityFrameworkCore.Relational, Version=3.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'.

Here is the full stack trace :

System.TypeLoadException: Could not load type 'Microsoft.EntityFrameworkCore.RelationalMetadataExtensions' from assembly 'Microsoft.EntityFrameworkCore.Relational, Version=3.0.0.0, Culture=neutral, PublicKeyToken=adb9793829ddae60'.
   at Npgsql.Bulk.NpgsqlHelper.GetTableName(DbContext context, Type t)
   at Npgsql.Bulk.NpgsqlBulkUploader.CreateEntityInfo[T]()
   at Npgsql.Bulk.NpgsqlBulkUploader.<GetEntityInfo>b__40_0[T](Type x)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Npgsql.Bulk.NpgsqlBulkUploader.GetEntityInfo[T]()
   at Npgsql.Bulk.NpgsqlBulkUploader.InsertAsync[T](IEnumerable`1 entities, InsertConflictAction onConflict)

Other (non-bulk) database operations work without problems.

Anything obvious I should check on my side? Thanks.

I am getting error ERROR: current transaction is aborted, commands ignored until end of transaction block on command STATEMENT: INSERT INTO "foobar" ("Foo", "DateTime", "Baz", "Bar") SELECT foobar_foo, foobar_datetime, foobar_baz, foobar_bar FROM _temp_636645699748660653 RETURNING Id

var uploader = new NpgsqlBulkUploader(context);
var data = value.Bar.Select(u => new Fobar{ ... }).ToList();
uploader.Insert(data);

Any idea what I am doing wrong?

Hello again :)

I'm trying to use a new property for tsrange type.

    [Required]
    public NpgsqlRange<Instant> Duration { get; set; }

First I added

            case "tsrange":
                return NpgsqlDbType.Range;

after

But there is an error I cannot debug on.

It just jump to catch section and the Exception says:

System.InvalidProgramException
HResult=0x8013153A
Message=Common Language Runtime detected an invalid program.
Source=Dwell_636891960223685689
StackTrace:
at Dwell_636891960223685689.ClientDataWriter(Dwell , NpgsqlBinaryImporter )
at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable1 entities, InsertConflictAction onConflict) in C:\work\tos\external\Npgsql.Bulk\src\Npgsql.Bulk\NpgsqlBulkUploader.cs:line 214 at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable1 entities) in C:\work\tos\external\Npgsql.Bulk\src\Npgsql.Bulk\NpgsqlBulkUploader.cs:line 146

Currently the code supports arrays of text types like so:

case "_text":
    return NpgsqlDbType.Array;

I had a case when I store an array of uuid, not texts, and it was not being mapped.

Possible solution: Consider the column type an array if it starts with "_" (also check for null when testing ColumnTypeExtra):

if (string.Equals(info?.ColumnTypeExtra, "array", StringComparison.OrdinalIgnoreCase) || info.ColumnType.StartsWith("_"))
    return NpgsqlDbType.Array;

My project targets to 4.8 and we are using ef-core 3.0.
When i link the library i get compilation error

[CS1503]  Argument 1: Cannot convert from"MyDataContext" into "System.Data.Entity.DbContext".

Usage:

new NpgsqlBulkUploader(new MyDataContext());

Npgsql.Bulk Version=0.9.0

hello,
for example, my entity contains a column with nullable datetime
but when i use this sdk, it throw an exception,can you relove or fix it? thank you

Unhandled Exception: System.InvalidCastException: Can't write CLR type System.Nullable1[System.DateTime] to database type timestamp at Npgsql.TypeHandling.NpgsqlSimpleTypeHandler1.ValidateAndGetLength[TAny](TAny value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter) in C:\Users\kenan19\Downloads\npgsql-dev\src\Npgsql\TypeHandling\NpgsqlSimpleTypeHandler.cs:line 171
at Npgsql.NpgsqlParameter1.ValidateAndGetLength() in C:\Users\kenan19\Downloads\npgsql-dev\src\Npgsql\NpgsqlParameter.cs:line 96
at Npgsql.NpgsqlBinaryImporter.Write[T](T value, NpgsqlParameter param) in C:\Users\kenan19\Downloads\npgsql-dev\src\Npgsql\NpgsqlBinaryImporter.cs:line 244

New contributors have to install .NET Framework 4.5 Developer Pack to build this project. But .NET Framework 4.5 Developer Pack is no longer available from MS downloads page, so they would spend time to find it. somewhere else.

I suggest to Increase target framework version to 4.5.2, so user can easily download Developer Pack and begin to contribute.

Currently the Insert method calls entities.ToList(), is it possible to provide one without it (for large data sets)? It's okay if it doesn't use a temp table and has no the returning clause, just for fast inserts.

EF Core 3.1 was released with .NET Core 3.1 yesterday, and with it, EF Core 3.1 now only requires .NET Standard 2.0 instead of 2.1 as from EF Core 3.0.

It would be great if the next package reflects the .NET Standard 2.0 requirement instead of the 2.1 library. I'm creating a ticket because it might not be obvious that the downgrade is available otherwise.

Hello I'm getting an error: "null value in column "Id" violates not-null constraint." when I try to bulk insert instances of following entity:

public class SomeClass
{
        public Guid Id { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime? ModifiedOn { get; set; }
        public Guid SomeRefId { get; set; }
        public Guid SomeOtherRefId { get; set; }
        public Guid ReportId { get; set; }
        public Guid? AssignationAuthorId { get; set; }
        public DateTime? AssignationDateTime { get; set; }
        public double? SomeValue { get; set; }
        public bool SomeBoolValue { get; set; }
}

Here is how the exception looks like

{Npgsql.PostgresException (0x80004005): 23502: null value in column "Id" violates not-null constraint
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1032
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.<NextResult>d__46.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 467
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 340
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__100.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1231
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1130
   at Npgsql.Bulk.NpgsqlBulkUploader.Insert[T](IEnumerable`1 entities, InsertConflictAction onConflict)
   at Core.Infrastructure.Storage.EntityFramework.DbContextBulkOperationsExtensions.BulkInsert[T](DbContext context, IList`1 entities) in .... line 16}```

Exception details:
Failing row contains (null, 2019-03-27 11:16:32.534, null, d1292198-df7c-4d6d-a08b-822d0206c5e6, 000006ac-81b8-47dd-b243-0a9e36b961ef, dfdb2783-996c-47c9-b086-7608c7e3c2b1, 0e00cd16-f163-4645-88fe-7c2977f0355b, 2019-01-04 10:28:21, 15.0358169334254, t)."

Id type in my datatabase is uuid

EDIT:

I think the problem happens when data are copied from temporary table to destination table. I don't see id in Insert command and query which selects from temporary table. Does it assuemes that id is autogenerated and ommits it? If so can I somehow force copying of id?

I have app witch configured as net472;netcoreapp3.1
I use Npgsql 4.*(try any vertions) for work with DB
INSERT\UPDATE worked fine, but when I try use bulk operation then on start app under net472 have this exception
"Void Npgsql.Bulk.NpgsqlBulkUploader..ctor(Microsoft.EntityFrameworkCore.DbContext)".

For bulk operation I try like this:

using (var ctx = new MyContext(NpgsqlDbContextOptionsExtensions.UseNpgsql(new DbContextOptionsBuilder(), _connectionStr).Options))
{
var d = new List();

        var uploader = new NpgsqlBulkUploader(ctx);
        uploader.Insert(d, Npgsql.Bulk.InsertConflictAction.Update<UserByRole>(........));
    }

Call stack:
at PGTestCommand.Test.Run() in C:...\Program.cs:line 45
at PGTestCommand.Program.Main(String[] args) in C:...\Program.cs:line 13

Exception Message:
Method not finde: "Void Npgsql.Bulk.NpgsqlBulkUploader..ctor(Microsoft.EntityFrameworkCore.DbContext)".

This problem only when start app under NET472. Same code under core work fine.

Have any some advise?

P.S. I download master, build project (only build Npgsql.Bulk), connect Npgsql.Bulk.dll to my test app and run. Evrething work in both mode(core and net). Maybe some problem in nuget packages?

I am trying to import 6 GiB of data using ImportAsync like this:

var bulkUploader = new NpgsqlBulkUploader(_context);
await bulkUploader.ImportAsync(messages);

Each message in messages is 10 KiB. When I run that code with 6 GiB of messages I quickly receive an OutOfMemoryException.

I analyzed the memory usage of the program and noticed that every message is being stored in memory. The only references to each message are from classes in EF Core. Specifically, these two classes are keeping references to every message:

• Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalClrEntityEntry
• Microsoft.EntityFrameworkCore.ChangeTracking.Internal.EntityReferenceMap

I put a breakpoint in EF Core's StateManager to confirm entities were being added by Npgsql.Bulk. I put the breakpoint on this line of code: https://github.com/dotnet/efcore/blob/b970bf29a46521f40862a01db9e276e6448d3cb0/src/EFCore/ChangeTracking/Internal/StateManager.cs#L337

The breakpoint showed this call stack:

Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.UpdateReferenceMaps(Microsoft.EntityFrameworkCore.ChangeTracking.Internal.InternalEntityEntry entry, Microsoft.EntityFrameworkCore.EntityState state, Microsoft.EntityFrameworkCore.EntityState? oldState) Line 315
at /_/src/EFCore/ChangeTracking/Internal/StateManager.cs(315)

Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.GetOrCreateEntry(object entity) Line 231
at /_/src/EFCore/ChangeTracking/Internal/StateManager.cs(231)

Npgsql.Bulk.dll!Npgsql.Bulk.ValueHelper<B.Message>.Get<System.DateTime, System.DateTime>(B.Message model, string propName, Microsoft.EntityFrameworkCore.DbContext context, System.DateTime localValue)

Message_72305025_8cb2_4d6d_92ed_f736f64690d5_1!Message_72305025_8cb2_4d6d_92ed_f736f64690d5_1.WriterForInsertAction(B.Message value, Npgsql.NpgsqlBinaryImporter value, Microsoft.EntityFrameworkCore.DbContext value)

Npgsql.Bulk.dll!Npgsql.Bulk.NpgsqlBulkUploader.ImportAsync<B.Message>(System.Collections.Generic.IEnumerable<Bt.Outbox.OutboxMessage> entities)

That call stack lead me to these lines of code in Npgsql.Bulk:

If I'm reading that correctly, it means every object sent to ImportAsync will eventually be stored in the DbContext's state tracker as a detached entity. Those entities are not cleaned up until the DbContext is disposed.

I was surprised by that behavior because this comment in NpgsqlBulkUploader implies that ImportAsync can be used with large data sets:

Is there any way to avoid storing every object in the DbContext's state tracker? If not, is there some way to clear out the state tracker periodically while ImportAsync is importing rows?

As a workaround I can import rows in batches, but that creates other problems due to variations in the size of data that I am importing. It's not as easy as batching by the number of messages because certain message streams have small messages while others have very large messages. I'd prefer if Npgsql.Bulk could simply not store every message in memory so that I don't have to implement batching based on observed memory usage.

Hi,

I receive this error in console during installation:

GET https://api.nuget.org/v3-flatcontainer/npgsql.entityframeworkcore.postgres/index.json
NotFound https://api.nuget.org/v3-flatcontainer/npgsql.entityframeworkcore.postgres/index.json 669ms
Install-Package : Unable to find package Npgsql.EntityFrameworkCore.Postgres. No packages exist with this id in source(s): Microsoft Visual Studio Offline Packages, nuget.org

It looks like a typo in dependency package name "Npgsql.EntityFrameworkCore.Postgres" should be "Npgsql.EntityFrameworkCore.PostgreSQL".

when I call the following method

        public async Task<int> Ingest(IEnumerable<T> source, CancellationToken cancel)
        {
            var list = source.GroupBy(r => r.GetKeyValue()).Select(g => g.First()).ToList();
            var count = list.Count;

            if (list.Count > 0)
            {
                logger.LogInformation($"adding {typeof(T).Name}...");
                var stopwatch = Stopwatch.StartNew();

                var uploader = new NpgsqlBulkUploader(dbContext);
                await uploader.InsertAsync(list);
                logger.LogInformation($"it took {stopwatch.Elapsed} to injest {count} records");
            }

            return count;
        }

error occurred on line with code await uploader.InsertAsync(list);:

[20:45:43 INF] adding ElectricalDeviceForEquipment...
[20:48:08 INF] Executed DbCommand (227ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TEMP TABLE _temp__35c46611_47c6_4ba6_80f6_4fb0596b9115_2 ON COMMIT DROP AS SELECT "ElectricalDevicesForEquipment"."Id" AS electricaldevicesforequipment_id, "ElectricalDevicesForEquipment"."ArgusRuleTemplate" AS electricaldevicesforequipment_argusruletemplat
e, "ElectricalDevicesForEquipment"."ChannelDescription" AS electricaldevicesforequipment_channeldescription, "ElectricalDevicesForEquipment"."ChannelTypeName" AS electricaldevicesforequipment_channeltypename, "ElectricalDevicesForEquipment"."CreatedBy" AS electric
aldevicesforequipment_createdby, "ElectricalDevicesForEquipment"."CreationTime" AS electricaldevicesforequipment_creationtime, "ElectricalDevicesForEquipment"."DataPoint" AS electricaldevicesforequipment_datapoint, "ElectricalDevicesForEquipment"."DataPointTag" AS
 electricaldevicesforequipment_datapointtag, "ElectricalDevicesForEquipment"."DcCode" AS electricaldevicesforequipment_dccode, "ElectricalDevicesForEquipment"."DcName" AS electricaldevicesforequipment_dcname, "ElectricalDevicesForEquipment"."DeviceFamily" AS elect
ricaldevicesforequipment_devicefamily, "ElectricalDevicesForEquipment"."DeviceName" AS electricaldevicesforequipment_devicename, "ElectricalDevicesForEquipment"."DevicePath" AS electricaldevicesforequipment_devicepath, "ElectricalDevicesForEquipment"."DeviceState"
 AS electricaldevicesforequipment_devicestate, "ElectricalDevicesForEquipment"."DeviceTags" AS electricaldevicesforequipment_devicetags, "ElectricalDevicesForEquipment"."DeviceType" AS electricaldevicesforequipment_devicetype, "ElectricalDevicesForEquipment"."Hier
archy" AS electricaldevicesforequipment_hierarchy, "ElectricalDevicesForEquipment"."HierarchyId" AS electricaldevicesforequipment_hierarchyid, "ElectricalDevicesForEquipment"."LowerDeadBand" AS electricaldevicesforequipment_lowerdeadband, "ElectricalDevicesForEqui
pment"."ModificationTime" AS electricaldevicesforequipment_modificationtime, "ElectricalDevicesForEquipment"."ModifiedBy" AS electricaldevicesforequipment_modifiedby, "ElectricalDevicesForEquipment"."PanelName" AS electricaldevicesforequipment_panelname, "Electric
alDevicesForEquipment"."PrimaryParents" AS electricaldevicesforequipment_primaryparents, "ElectricalDevicesForEquipment"."QueryPanel" AS electricaldevicesforequipment_querypanel, "ElectricalDevicesForEquipment"."Rating" AS electricaldevicesforequipment_rating, "El
ectricalDevicesForEquipment"."Reason" AS electricaldevicesforequipment_reason, "ElectricalDevicesForEquipment"."RedundantDeviceInformation" AS electricaldevicesforequipment_redundantdeviceinformation, "ElectricalDevicesForEquipment"."RedundantDeviceNames" AS elect
ricaldevicesforequipment_redundantdevicenames, "ElectricalDevicesForEquipment"."RuleId" AS electricaldevicesforequipment_ruleid, "ElectricalDevicesForEquipment"."Source" AS electricaldevicesforequipment_source, "ElectricalDevicesForEquipment"."TS" AS electricaldev
icesforequipment_ts, "ElectricalDevicesForEquipment"."UpperDeadBand" AS electricaldevicesforequipment_upperdeadband, "ElectricalDevicesForEquipment"."Validate" AS electricaldevicesforequipment_validate FROM "ElectricalDevicesForEquipment" LIMIT 0
[20:48:15 INF] Executed DbCommand (37ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE _temp__35c46611_47c6_4ba6_80f6_4fb0596b9115_2 ADD COLUMN __index integer
[20:48:24 ERR] Failed sync: Arithmetic operation resulted in an overflow.
System.OverflowException: Arithmetic operation resulted in an overflow.
   at Npgsql.TypeHandlers.NumericHandlers.Int32Handler.Write(Double value, NpgsqlWriteBuffer buf, NpgsqlParameter parameter)
   at Npgsql.TypeHandling.NpgsqlSimpleTypeHandler`1.WriteWithLengthInternal[TAny](TAny value, NpgsqlWriteBuffer buf, NpgsqlLengthCache lengthCache, NpgsqlParameter parameter, Boolean async)
   at Npgsql.NpgsqlParameter`1.WriteWithLength(NpgsqlWriteBuffer buf, Boolean async)
   at Npgsql.NpgsqlBinaryImporter.Write[T](T value, NpgsqlParameter param, Boolean async)
   at Npgsql.NpgsqlBinaryImporter.Write[T](T value, NpgsqlDbType npgsqlDbType)
   at ElectricalDeviceForEquipment_198bd14d_36be_4bb4_9672_8f754439f90d_1.WriterForInsertAction(ElectricalDeviceForEquipment , NpgsqlBinaryImporter , DbContext )
   at Npgsql.Bulk.NpgsqlBulkUploader.WriteInsertPortion[T](IEnumerable`1 list, EntityInfo mapping, NpgsqlConnection conn, String tempTableName, NpgsqlBulkCodeBuilder`1 codeBuilder)
   at Npgsql.Bulk.NpgsqlBulkUploader.InsertAsync[T](IEnumerable`1 entities, InsertConflictAction onConflict)
   at DataCenterHealth.Repositories.EntityRepo`1.Ingest(IEnumerable`1 source, IngestMode ingestMode, CancellationToken cancel) in E:\work\my\dq\src\DataQuality\src\Models\DataCenterHealth.Repositories\EntityRepo.cs:line 269

The blog/article link is broken. http://tsherlock.tech/2017/10/11/solving-some-problems-with-bulk-operations-in-npgsql. Could you please fix this issue

In a few mounts the .Net 6 will be released.
We use already .Net 6 Preview for many our project.

Npgsql.Bulk is not compatible with .Net 6 and it will be great if someone can fix it.

If an entity has a property based on List, the type name is "_text" and it throws an Exception.

So, I added this:

case "_text": return NpgsqlDbType.Array;

And it works... don't know what happens with List properties, perhaps in some days :)

System.InvalidOperationException: The configured execution strategy 'NpgsqlRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.