search

zzzprojects / Bulk-Operations

C# SQL Bulk Operations | High-performance C# bulk insert, update, delete and merge for SQL Server, SQL Azure, SQL Compact, MySQL, and SQLite.
https://bulk-operations.net
142 stars 36 forks source link

AutoMapOutputIdentity not working with Microsoft.Data.Sqlite #65

Closed BEagle1984 closed 3 years ago

BEagle1984 commented 3 years ago

Trying to leverage BulkInsert in one of our application I stumbled across an issue with AutoMapOutputIdentity and SQLite. The issue is that there are at least two different nuget packages for SQLite: System.Data.SQLite and Microsoft.Data.Sqlite. Things are working fine with the first but not with the latter.

I reproduced the issue here: https://dotnetfiddle.net/59Yulp

As you can see the generated identity value simply isn't copied back to the source DataRow.

As said, the very same code works flawlessly with System.Data.SQLite (proof here: https://dotnetfiddle.net/aRVx9y) but unfortunately we depend on Microsoft.Data.Sqlite, since we use EFCore as well and Microsoft.EntityFrameworkCore.Sqlite depends on it.

(We actually don't use SQLite on production but we do use it in our automated tests and it's a show stopper if that doesn't work.)

JonathanMagnan commented 3 years ago

Hello @BEagle1984 ,

Thank you for reporting, we will look at this.

Best Regards,

Jon

JonathanMagnan commented 3 years ago

Hello @BEagle1984 ,

The issue happens because our library cannot discover that your table has an AUTOINCREMENT column.

You can make it works by specifying it in your table definition:

[CustomerID] [INTEGER] PRIMARY KEY AUTOINCREMENT,

It currently works with the System.Data.SQLite as we have a custom logic when no identity has been found.

We will look to add a similar logic for Microsoft.Data.Sqlite but meanwhile, you have an alternative solution.

Best Regards,

Jon

BEagle1984 commented 3 years ago

Thank you @JonathanMagnan for the quick reply. The workaround works like a charm and we are bulk inserting like crazy. 💪

JonathanMagnan commented 3 years ago

Great to hear!

Don't hesitate to contact us if needed!

Best regards,

Jon

JonathanMagnan commented 3 years ago

Hello @BEagle1984 ,

The v3.0.34 has been released.

In this version, we added the same fix when no identity is found that the System.Data.SQLite. A column with an INTEGER type acts like an identity when this is the only PK column. We added this missing rule to the Microsoft.Data.Sqlite provider.

Best Regards,

Jon