1

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?

DenisMicheal.k
  • 197
  • 2
  • 12
user6408649
  • 1,227
  • 3
  • 16
  • 40

0 Answers0