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.
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
You want to save only a few entities with high-performance operations.
Use an SQL Command and perform an insert using an SQL derived table.
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.
Bulk Insert with SQL Bulk Copy
You want to insert a list containing hundreds, thousands or millions of entities with high-performance operations.
Use SqlBulkCopy. It's the fastest ways to insert multiples entities in SQL Server.
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.).
Bulk Insert with Temporary Table and SqlBulkCopy
You want to insert a lot of entities and return the inserted identity values with high-performance operations.
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.
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.
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
Share your experience
- What's missing in this article?
- Will you create your BulkInsert or use a third party library?