SQLite In-Memory BulkInsert/BulkSaveChanges/BulkMerge fails with Entity type with composite key

[ristomoilanen]

Description

I have Entity type with composite key (DateTime + int) and when Bulk-Inserting them to SQLite In-memory database it fails with SQLite error 19: "NOT NULL constraint failed"

This has worked before with earlier version (like 2.7.105 ) but with newest version 2.8.33 this fails. I cannot check exact version which broke this since I'm using free version on unit tests and have license only to SQL Server provider.

Exception

Exception message: Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'NOT NULL constraint failed: TimeSeriesValues.TimeSeriesHeaderId'.
Stack trace:
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
   at .(DbCommand , BulkOperation , Int32 )
   at .( , DbCommand )
   at .Execute(List`1 actions)
   at Z.BulkOperations.BulkOperation.Execute()
   at .BulkInsert[T](DbContext this, IEntityType entityType, IEnumerable`1 list, Action`1 options, Boolean forceSpecificTypeMapping)
   at .BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options, Boolean isBulkSaveChanges)
   at .(DbContext this, List`1 , Action`1 )
   at .(DbContext this, StateManager , IReadOnlyList`1 , Action`1 )
   at .(DbContext this, StateManager , IReadOnlyList`1 , Action`1 )
   at .(DbContext this, Action`1 , DbContext )

Log snippet

I got following output from BulkOperation.Log. It seems that insert clause does not have key values given (TimeSeriesHeaderId and TimeStamp). Maybe this is the problem?

UPDATE "TimeSeriesValues" SET "LastModified" = @u_0_0, "Status" = @u_0_1, "Value" = @u_0_2 WHERE ("TimeStamp" = @pk_0_0 OR ("TimeStamp" IS NULL AND @pk_0_0 IS NULL)) AND ("TimeSeriesHeaderId" = @pk_0_1 OR ("TimeSeriesHeaderId" IS NULL AND @pk_0_1 IS NULL));
INSERT INTO "TimeSeriesValues" ( "TimeStamp", "LastModified", "Status", "Value" ) SELECT @i_0_0, @i_0_1, @i_0_2, @i_0_3
WHERE NOT EXISTS (SELECT 1 FROM "TimeSeriesValues" WHERE ("TimeStamp" = @pk_0_0 OR ("TimeStamp" IS NULL AND @pk_0_0 IS NULL)) AND ("TimeSeriesHeaderId" = @pk_0_1 OR ("TimeSeriesHeaderId" IS NULL AND @pk_0_1 IS NULL)));


-- @i_0_0: 1.10.2019 4.00.00 (Type = String, Size = 0)
-- @i_0_1: 27.4.2021 11.10.04 (Type = String, Size = 0)
-- @i_0_2: 4 (Type = Int32, Size = 0)
-- @i_0_3: 11,001 (Type = Decimal, Size = 0)
-- @u_0_0: 27.4.2021 11.10.04 (Type = String, Size = 0)
-- @u_0_1: 4 (Type = Int32, Size = 0)
-- @u_0_2: 11,001 (Type = Decimal, Size
 = 0)
 -- @pk_0_0: 1.10.2019 4.00.00 (Type = String, Size = 0)
 -- @pk_0_1: 1 (Type = Int32, Size = 0)
 -- @ZZZ_Index_0: 0 (Type = Int32, Size = 0)
 -- CommandTimeout:120
 -- Executing at 27.4.2021 14.10.04

Further technical details

• EF version: Core 2.2.2
• EF Extensions version: 2.8.33
• Database Provider: SQLite In-memory

[JonathanMagnan]

Hello @ristomoilanen ,

Thank you for reporting, I indeed remember a change from one of my developers he did recently that could cause exactly this issue.

I will ask him to look at it.

Best Regards,

Jon

[JonathanMagnan]

Hello @ristomoilanen ,

The v5.1.34 has been released (v2.8.34 in your case).

Could you look at it and let us know if everything has been fixed correctly?

Best Regards,

Jon

[ristomoilanen]

Thanks for your response. I tested new version v2.8.34 and there is no more exception thrown. Which is great!

But there is still one problem. BulkMerge performance is now very poor. BulkMerge operation (entity with composite key) that previously took ~1 sec with v2.7.105 now takes 5 to 10 minutes with v2.8.34.

BulkInsert seem to perform like before with my quick testing.

[JonathanMagnan]

Wow that's a big difference,

We will sure look at this performance issue.

[ristomoilanen]

Is there any possibility to use older version of the library (like 2.7.105) in trial mode while this problem is being fixed? Currently older versions gives us license error.

We have SQL Server license purchased, but we have started using trial version & SQLite provider in development/unit testing.

[JonathanMagnan]

Hello @ristomoilanen ,

Please contact us directly [email protected] with your current license name.

We will provide you one compatible with SQLite

[ristomoilanen]

Thank you very much!

And ask me if there is anything I can help you with this current performance issue, log dump etc..