CODEWITHSUNDEEP
c#sqlsql-serverentity-framework-core
Hirasawa Yui
Hirasawa Yui

Reputation: 1296

Insert values using EF core generates a complicated query

I've created an app that inserts about 100k records into a database. I'm using EF core, like:

context.Users.AddRange(users);
await context.SaveChangesAsync();

I expected it to generate a simple insert query, like insert into users (...) values (...). But, this is the monstrosity it generated instead:

SET NOCOUNT ON;
DECLARE @inserted0 TABLE (..., [_Position] [int]);
MERGE [dbo].[Users] USING (
VALUES (@p0, @p1, @p2, @p3, @p4, 0),
(@p5, @p6, @p7, @p8, @p9, 1),
(@p10, @p11, @p12, @p13, @p14, 2)

...

) AS i (..., _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT (...)
VALUES (...)
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

It did not even include all the items in one request. It ran the query multiple times, inserting about 3k records at a time. Obviously, this code is really slow. I found a similar question that suggested setting MaxBatchSize or UseRelationalNulls, but none of that helped in my case. Even if I decrease MaxBatchSize to 1 (that would make it run 100k queries), EF still generates the same weird code. And in case I set MaxBatchSize to 100k, it still generates batches with about 3k items at a time. So, it looks like MaxBatchSize doesn't actually work in this case.

So, is there any way to make EF Core generate a faster insert code?

Upvotes: 0

Views: 1162

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27416

EF Core is trying to generate insert query and return back generated ID's and other fields which are initialized by database, _Position in your case. An there is no way to generate insert and forget statements via ChangeTracker.

I would suggest to use linq2db.EntityFrameworkCore (Disclaimer: I'm one of the creators)

await context.BulkCopyAsync(users);

100K records will be inserted approximately in 1 second.

Upvotes: 1

Related Questions