Introduction

The major issue with Entity Framework is without a doubt the bad performance. Making saving operations like inserting hundreds or thousands of entities is long, very long and sometimes take forever!

The reason is simple, Entity Framework doesn’t insert using batch or bulk operations but does instead a database round-trip for every record.

Entity Framework Core has partially fixed this issue by batching data. The performance is better but still far away to be optimal to insert thousands of rows in SQL Server.

Solution

There are two solutions to fix this issue, using a third party library supporting Bulk Insert or creating your own Bulk Insert.

In this article, we will show how to bulk insert entity in SQL Server similar to the library Entity Framework Extensions but way lighter and by using hard coded string/property to make it easier to understand.


Bulk Insert with SQL Command

Problem

You want to save only a few entities with high-performance operations.

Solution

Use an SQL Command and perform an insert using an SQL derived table.

Discussion

SqlBulkCopy is fast but way slower than SQL Command when inserting only a few rows. Entity Framework Extensions use the SQL Command strategy if the list contains less than ten entities. In some case, this technique could be even faster than SqlBulkCopy with a hundred of rows.

Example


Bulk Insert with SQL Bulk Copy

Problem

You want to insert a list containing hundreds, thousands or millions of entities with high-performance operations.

Solution

Use SqlBulkCopy. It's the fastest ways to insert multiples entities in SQL Server.

Discussion

Entity Framework Extensions use SqlBulkCopy when the list contains 10 or more entities, and no configuration require a temporary table (auditing, identity output value, etc.).

Example


Bulk Insert with Temporary Table and SqlBulkCopy

Problem

You want to insert a lot of entities and return the inserted identity values with high-performance operations.

Solution

You need first to create a temporary table and populate this table using SqlBulkCopy. Then you can use a MERGE statement to insert from the temporary table to the destination table.

Discussion

Entity Framework Extensions use this strategy when the list contains 10 or more entities and a configuration like auditing, or identity column output is required.

Example


Conclusion

Is it as simple as this? Performing a custom bulk insert with hard-coded string is very easy and take only a few minutes/hours, however, making a generic library supporting bulk insert is way more complex. You need to support complex type, Enum, TPC, TPH, TPT, code first, database first, property from the association, private property, custom collations, table with the trigger, case insensitive and way more!

Again, you have two choices, spending hours in every project to customize your bulk operations and hope you didn’t make any mistake or use a ready-to-use third party library which makes everything easy.

Entity Framework Extensions is currently the only library supporting everything to Bulk Insert. It’s also the only library supporting in Entity Framework Bulk SaveChanges, Bulk Update, Bulk Delete and Bulk Merge.

You can download the full example here

Example


Share your experience

  • What's missing in this article?
  • Will you create your BulkInsert or use a third party library?