How add multiple rows with one Insert query in EF

Question

I have a list of strings of unknown length. I'm adding them to database:

if (somenteNovasClas.Any()) {
    foreach (string item in wsClassificacoes) {
        dc.Classificacaos.Add(new Classificacao { Descricao = item });
    }
    dc.SaveChanges();
}

But EF will generate one INSERT for each row:

exec sp_executesql N'insert [dbo].[Classificacao]([Descricao], [Excluido])
values (@0, @1)
select [CodClassificacao]
from [dbo].[Classificacao]
where @@ROWCOUNT > 0 and [CodClassificacao] = scope_identity()',N'@0 varchar(255),@1 bit',@0='Mercado',@1=0
go
exec sp_executesql N'insert [dbo].[Classificacao]([Descricao], [Excluido])
values (@0, @1)
select [CodClassificacao]
from [dbo].[Classificacao]
where @@ROWCOUNT > 0 and [CodClassificacao] = scope_identity()',N'@0 varchar(255),@1 bit',@0='Concorrência',@1=0
go

I'd like to know how to insert in one time that EF will generate a command like:

INSERT INTO Table (column)
VALUES ([array[0]]), ([array[1]]), ...

instead of one insert foreach.. Any ideas?

using [Express profiler](http://expressprofiler.codeplex.com/) — Andre Figueiredo, Apr 23 '14 at 17:24
Fair enough. Please show your C# code. I suspect you are not writing it as intended by MSFT. I'm quite sure EF can do batch inserts. — evanmcdonnal, Apr 23 '14 at 17:25
I'm not sure what you are trying to achieve. Unless you bulk insert from a SqlBulkCopy or an external data source, this is the only way it can be done AFAIK and the way you are doing it is still pretty fast. — Francis Ducharme, Apr 23 '14 at 17:38
You might want to write an SP or a UDF and use table value parameters to get the data to SQL Server in one hop... http://code.msdn.microsoft.com/Stored-Procedure-with-6c194514 — spender, Apr 23 '14 at 17:43
EF will never generate sql statements with so-called row constructors. It's just not one of its features. — Gert Arnold, Apr 23 '14 at 17:44
ok... just checking, I was hoping that there was one more effective, this is poluting so much the profiler.. it's a lot of rows :) — Andre Figueiredo, Apr 23 '14 at 17:51
possible duplicate of [Fastest Way of Inserting in Entity Framework](http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework) — Erik Philips, Apr 23 '14 at 19:01
Possibly... except that I would like to know if there is a way to generate the script with EF, not just "the fastest" way to insert. — Andre Figueiredo, Apr 23 '14 at 19:23

score 1 · Answer 1 · answered Dec 11 '20 at 20:29

Entity Framework core (3+) has been improved significantly in this area. When there are multiple inserts that have the same shape, for Sql Server it will generate MERGE statements rather than separate INSERT statements.

For example, adding 10 new products:

DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [Products] USING (
VALUES (@p0, @p1, @p2, @p3, @p4, 0),
(@p5, @p6, @p7, @p8, @p9, 1),
(@p10, @p11, @p12, @p13, @p14, 2),
(@p15, @p16, @p17, @p18, @p19, 3),
(@p20, @p21, @p22, @p23, @p24, 4),
(@p25, @p26, @p27, @p28, @p29, 5),
(@p30, @p31, @p32, @p33, @p34, 6),
(@p35, @p36, @p37, @p38, @p39, 7),
(@p40, @p41, @p42, @p43, @p44, 8),
(@p45, @p46, @p47, @p48, @p49, 9)) AS i ([Image], [ProductName], [QuantityPerUnit], [StartDate], [UnitPrice], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([Image], [ProductName], [QuantityPerUnit], [StartDate], [UnitPrice])
VALUES (i.[Image], i.[ProductName], i.[QuantityPerUnit], i.[StartDate], i.[UnitPrice])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [t].[Id], [t].[RowVersion] FROM [Products] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];

The concluding SELECT query is for feeding back the generated Id and RowVersion values into the C# objects.

When there are large numbers of inserts, EF won't hit the maximum parameter threshold, it just generates multiple blocks of MERGE statements.

It's not the same as an insert statement with value constructors, but still a lot better than countless separate inserts.

score 0 · Answer 2 · edited May 23 '17 at 12:10

0

Check out this post: Fastest Way of Inserting in Entity Framework

Entity framework isn't really meant to do this out of the box, but it looks like people have created some extensions: https://efbulkinsert.codeplex.com/

edited May 23 '17 at 12:10

Community

1
1

answered Apr 23 '14 at 17:52

mmilleruva

2,060
17
20

How add multiple rows with one Insert query in EF

2 Answers2