EFFICIENT BULK UPSERT METHOD.

To upsert means to update if it exists else add to the existing records. This upsert operation can be achieved in many ways such as checking each data and comparing with existing data using EF but this is not always good, especially for large records. Using store procedures stands to be the best choice for me. Using the Merge Method in SQL makes this easily achievable.

Recently I have not only been concerned about what works but much more about team members understanding the code and maintenance. Not everybody is good at writing raw SQL so having maintenance in mind warrants the choice of method or approach to use.

One of the recent methods I can recommend for use is EFCore.BulkExtensions. I am using it and is highly recommended by Microsoft for complex operations — Microsoft

It is very easy to use and has the same accuracy as when you use a stored procedure. It also comes with a comprehensive documentation. Under the hood of this method for upsert is SqlBulkCopy for SQL.

First, to use BulkExtensions for bulk upsert you have to install EFCore.BulkExtensions from your nugget manager. After installation, the method to use is BulkInsertOrUpdateAsync.

The BulkConfig parameter has a lot of properties you can use in configuring your upsert. One very important one is UpdateByProperties — This is used to check the column for conditions that make updates happen. In the screenshot above, I am checking if column1, column2 and column3 match any record, if yes, then it should update the record accordingly, else it should just create a new record. The BatchSize is the number of records to save at once, in normal coding we use the chunk size method but this simplifies it for us.

The code snippet will successfully perform an upsert on 1 million records and is highly maintainable. You can place the code inside a transaction in case of possible failure.

_dbContext is the connection to access the database. This can have a different name based on your application.

I like this approach because it is maintainable and simple to use. There are other methods inside BulkExtension you can use for specific use cases.

Please read, like, comment, and share till it gets to those it might help.

You can read more here https://github.com/borisdj/EFCore.BulkExtensions