Inserting multiple records at once in Entity Framework Core 6-CodePudding

I am working with a .NET Core 6 minimal api with a PostgreSQL 13 database. The packages I'm using are Microsoft.EntityFrameworkCore 6.0.0 and Npgsql.EntityFrameworkCore.PostgreSQL. I have things set to LogLevel.Information so that I can view the SQL queries Entity Framework Core creates as it talks to the database.

I notice that when I insert records through my endpoint that it does a series of single insert statements instead of the expected (VALUES (<row1 values>),(<row2 values>)) format. For example, it is logging:

INSERT INTO <mytable> (<columns>) VALUES (<row1 values>);
INSERT INTO <mytable> (<columns>) VALUES (<row2 values>);

This is my code for the insert:

context.Items.AddRange(list);
await context.SaveChangesAsync();

Is there a way for it to combine all of the values into a single INSERT query?

CodePudding user response：

It is how EF works. You cannot insert/update/delete anything without Change Tracker. And it is not performant if there are a lot of records. PostgreSQL has COPY for inserting a bulk of records and if you are worrying about performance you can use such ability which is provided by Npgsql.

While there is a fast way for inserting records, it is very low level mechanism which requires a lot of efforts and accurate mapping to make it work.

So, there are a lot of EF Core extensions which can make it work with minimum efforts. One of them is linq2db.EntityFrameworkCore, note that I'm one of the creators and my opinion can be subjective.

After installing library you can just call BulkCopy to insert a batch of records.

await context.BulkCopyAsync(items);

There are also tweaking parameters which can improve performance.

CodePudding user response：

@svyatoslav-danyliv answer isn't accurate.

EF Core batches all inserts/updates: when you execute SaveChanges on your DbContext, all changes that have been accumulated are sent via a single command, in a single roundtrip (you can use a packet sniffer such as wireshark to observe this).

Note that this can be more efficient than a single INSERT query in some situations, since the multiple INSERTs statements are parameterized and have identical SQL, so the same prepared statement can be used repeatedly.

Having said that, if you're bulk-importing a huge amount of records, it may still be worth it to use Npgsql's low-level COPY support. I highly recommend writing up a quick benchmark with BenchmarkDotNet and comparing the various solutions with your own scenario.