How to avoid adding duplicate data to db when using EFCore.BulkExtensions?

Question

I am using EFCore.BulkExtensions in .NET Core 3.1 with Entity Framework Core.

I have a list containing records that already exist in my database, as well as records that are new and do not yet exist in my database.

I want to only add the new records to my database. I am currently managing to add the new records but I am also unfortunately adding the old ones, meaning duplicates are made in the table.

I have a domain object called InstagramInsightDatum that looks like this and a table in my database to reflect it:

public int InstagramInsightDatumId { get; set; }
public int Value { get; private set; }
public DateTime EndTime { get; private set; }
public int InstagramInsightId { get; set; }
public InstagramInsight InstagramInsight { get; set; }

I want to only add new InstagramInsightDatums to the database when they are unique. I will never need to update these records, just add new ones. At the moment my code looks like this:

I've highlighted the line where I add these datums to the table where the duplicates are happening in red - here you can see I am making the bulkInsert with the InstagramInsightDatums, and setting the PropertiesToExclude to InstagramInsightDatumId (I am not 100% how this config is meant to work but I assumed this feature is to allow us to ignore certain properties when comparing data?) - as removing the PK from the properties should reveal any potential duplicates...

Can anyone see why this isn't working?

Answer 1

A bit old Question, but here's how you would do it: Use UpdateByProperties instead of PropertiesToExclude and set PropertiesToIncludeOnUpdate = new List<string> { "" }. If you do like this, the records with an InstagramInsightDatumId that's not already exists in db will be inserted, and nothing will happen with the ones already existing. Example below:

BulkConfig bulkConfig = new BulkConfig
{
    UpdateByProperties = new List<string>() { nameof(InstagramInsightDatum.InstagramInsightDatumId ),
    PropertiesToIncludeOnUpdate = new List<string> { "" }
};

_dataContext.BulkInsertOrUpdate(newFormattedDatums, bulkConfig);

Answer 2

You can try: SqlBulkCopyOptions = SqlBulkCopyOptions.CheckConstraints

_dbContext.BulkInsertOrUpdate(entities, new BulkConfig
  {
    SqlBulkCopyOptions = SqlBulkCopyOptions.CheckConstraints
  });