Efficient way to perform an UPSERT with EFCore.BulkExtensions when data does not include primary key

Question

I need to upload bulk data into a database through an API and I am looking at using EFCore.BulkExtensions. Unfortunately, the data will not include the auto-generated primary key (Id).

However, we have a unique composite key that will be included in the data. I do need to keep the Id as the key attribute in the model because I will need that to use as the foreign key to several children (I have been able to BulkInsert parent and children using IncludeGraph, although I have been unable to set this up for BulkUpdate or BulkInsertOrUpdate - see below).

What is the most efficient way to handle updates when I don't have a primary key? Should I use the BulkRead to get the Id based on the unique composite key, selecting all 3 fields and then map this into the list of records? Or is there another way to handle this (maybe similar to ColumnPrimaryKeyExpression for Entity Framework Extensions).

Will there be an issue if the child records are also an Upsert when the parent is an Update? These records will have the same issue with missing primary keys and unique composite keys as I have for the parent.

Thanks in advance!

Are you stuck with `EFCore.BulkExtensions`? I can propose extension which can do that. — Svyatoslav Danyliv, Dec 29 '21 at 11:24
I am stuck with using something free. EFCore.BulkExtensions was recommended by Microsoft, so I chose that. Is there any other free option that can do something similar? — ChristyPiffat, Dec 29 '21 at 14:39
Well, I'll prepared free sample with free library. Also please point me when `EFCore.BulkExtensions` is recommended by Microsoft. — Svyatoslav Danyliv, Dec 29 '21 at 14:42
It's under EF Core Tools & Extensions - https://learn.microsoft.com/en-us/ef/core/extensions/#efcorebulkextensions. — ChristyPiffat, Dec 29 '21 at 15:04
It is not source of truth ;) You can make PR to documentation and your library will be there. — Svyatoslav Danyliv, Dec 29 '21 at 15:16

score 1 · Answer 1 · answered Dec 29 '21 at 15:37

I propose to look at alternatives. For example linq2db.EntityFrameworkCore has everything to work with bulk operations. Note that I'm one of the creators.

How to make Insert or Update of million(s) rows:

var items = ...

using var db = context.CreateLinqToDBConnection();

// temporary table will be crated and items will be inserted as fast as possible
using var temp = db.CreateTempTable(items);

var destinationTable = context.SomeTable.ToLinqToDBTable();
// or
var destinationTable = db.GetTable<SomeTable>();

destinationTable
    .Merge()
    .Using(temp)
    .On((target, source) => target.SomeValue == source.SomeValue)
    .InsertWhenNotMatched(source => new SomeTable
    {
        SomeValue = source.SomeValue
        ... // other fields
    })
    .UpdateWhenMatched((target, source) => new SomeTable
    {
        OtherValue = source.OtherValue 
        ... // other fields
    })
    .Merge();

score 0 · Accepted Answer · answered Jan 07 '22 at 14:51

I have decided to pull back the data I need with a Linq query based on the key using .AsNoTracking() and then match the Id's to the data to be updated. Due to some other restrictions, I need to use a ForEach to run some business rules on each record before running it through the BulkUpdate anyway, so the match can hopefully be done at the same time.

Efficient way to perform an UPSERT with EFCore.BulkExtensions when data does not include primary key

2 Answers2

Linked