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?

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
– evanmcdonnal, Commented Apr 23, 2014 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
– Francis Ducharme, Commented Apr 23, 2014 at 17:38
EF will never generate sql statements with so-called row constructors. It's just not one of its features. — Gert Arnold
– Gert Arnold, Commented Apr 23, 2014 at 17:44

Gert Arnold · Accepted Answer · 2020-12-11 20:29:45Z

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.

Community · Accepted Answer · 2017-05-23 12:10:15Z

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, 2017 at 12:10

CommunityBot

11 silver badge

answered Apr 23, 2014 at 17:52

mmilleruva

2,17818 silver badges20 bronze badges

Collectives™ on Stack Overflow

How add multiple rows with one Insert query in EF

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related