I'm using EFCore.BulkExtensions for importing some data from Elasticsearch
to MsSql
. Importing table contains following fields.
TABLE [dbo].[oem_catalog](
[Event] [varchar](100) NULL,
[IsInternalUser] [bit] NULL,
[IsMobile] [bit] NULL,
[MemberId] [int] NULL,
[Timestamp] [datetime] NULL,
[VinNumber] [nvarchar](max) NULL,
[DateIndex] [int] NULL,
[HitId] [varchar](100) NOT NULL,
[From] [nvarchar](max) NULL,
[Catalog] [nvarchar](max) NULL,
[CarName] [nvarchar](max) NULL,
CONSTRAINT [PK__oem_cata__3214EC07DA410015] PRIMARY KEY CLUSTERED
(
[HitId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
HitId is the PK that's mapped from Hit _id in ElasticSearch
and it contains a Unique value.
The method that imports data is very simple. Also I try to import the data using an async method.
public void BulkInsert(List<OemCatalog> oemCatalog)
{
using var tr = _context.Database.BeginTransaction();
try
{
_context.BulkInsertOrUpdate(oemCatalog,
new BulkConfig
{
UseTempDB = true,
BatchSize = 10000,
UpdateByProperties = new List<string>() { nameof(OemCatalog.HitId) },
PropertiesToIncludeOnUpdate = new List<string> { "" }
});
tr.Commit();
}
catch (Exception ex)
{
tr.Rollback();
}
}
This method must continue inserts data if the import process is broken or stopped. I don't need to update any fields because they are not updated in Elastic
. That's why I use this configuration. Found this example on stack...
I'm sure the field is unique and how I understand if a row with this HitId is already in database this row must ignored. But everytime an exception is raised.:
Error: Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK__oem_cata__3214EC07DA410015'. Cannot insert duplicate key in object 'dbo.oem_catalog'. The duplicate key value is (y6wjDYMBZVf_Zur_Yshb).
I wrapped it in a try...catch and if there is a lot of data, then the exception may occur several times, but after several runs of the method, everything will be imported.
What is wrong?