score: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/

score:1

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.


Related Articles