In this Video, I am going to discuss BulkInsert Extension Method in Entity Framework using Z.EntityFramework.Extensions with Examples. Please read our previous Video where we discussed Bulk Insert, Update, and Delete in Entity Framework. At the end of this Video, you will understand how to bulk insert using the BulkInsert extension method in Entity Framework with Examples.
How to perform BulkInsert Extension Method in Entity Framework?. The Complete Microsoft Entity Framework Developer Course 2023 [Videos].
Note: We are going to work with the same example that we created in our Introduction to Entity Framework Database First Approach Video. Please read our introduction to Entity Framework Database First Video before proceeding to this Video.
In this Video, I am going to show you how to use Z.EntityFramework.Extensions package and perform Bulk Insert, Update, and Delete Operations. First, open the NuGet Package Manager window and search Z.EntityFramework.Extensions. Select Z.EntityFramework.Extensions and then select the Project and choose the latest version and finally click on the Install button as shown in the below image.
Once you click on the Install button it will take some time and install Z.EntityFramework.Extensions DLL into your project.
Note: Entity Framework Extensions extends your DbContext with high-performance bulk operations such as BulkSaveChanges, BulkInsert, BulkUpdate, BulkDelete, BulkMerge, and more. It supports SQL Server, MySQL, Oracle, PostgreSQL, SQLite, and more!
Advantages of using Entity Framework Extensions:
- Easy to use
- Increase performance
- Increase application responsiveness
- Reduce database load
- Reduce database round-trips
BulkInsert Extension Method:
The BulkInsert and BulkInsertAync methods extend our DbContext which allows us to insert a large number of entities into the database with one go. The syntax to use the Bulk Insert extension method as follows:
Example: BulkInsert example using Entity Framework Extensions
Performance Comparison Between SaveChanges and BulkInsert in Entity Framework:
Let see the performance benchmark between the saveChanges and the BulkInsert method. We are going to insert the same 1000 students using both the approach and will see the time taken to complete the execution. For better understanding please have a look at the following example. In the below example, dont consider the FirstTimeExecution method for performance testing as we know when we execute something for the first time it will take some more time.
Output: You can see in the below output, the SaveChanges method took 647 ms time to insert 1000 rows into the database while the BulkInsert extension method took only 15 ms to insert the same 1000n records into the database.
Note: A lot of factors need to be considered which affect the benchmark time such as index, column type, latency, throttling, etc.
Why BulkInsert Extension Method is faster than SaveChanges?
Inserting 1000 entities for an initial load or a file importation is a typical scenario. The SaveChanges method makes it quite slow/impossible to handle this kind of situation due to the number of databases round-trips required. The SaveChanges perform one database round-trip for every entity to insert. So, if you need to insert 10,000 entities, 10,000 database round-trips will be performed which is make it slow.
The BulkInsert in counterpart requires the minimum number of database round-trips possible. For example, under the hood for SQL Server, a SqlBulkCopy is performed to insert 10,000 entities which is the fastest way available.
BulkInsert Extension Method with Options in Entity Framework:
The options parameter in the BulkInsert extension method allows us to use a lambda expression to customize the way entities are going to be inserted. In the following example, we use the Option parameter to set the Batch Size.
Insert and keep identity value
In real-time applications, your entity has an identity property i.e. auto-incremented property. But for some reason, you want to insert a specific value instead of the auto-incremented value. You can do the same using the InsertKeepIdentity option which allows you to keep the identity value of your entity.
Insert only if the entity does not already exist
In real-time scenarios, you want to insert entities in the database only if those entities do not exist in the database.
- InsertIfNotExists: This option allows us to insert entities that dont already exist.
- PrimaryKeyExpression: This option allows us to customize the key to use to check if the entity already exists or not.
Insert with related child entities (Include Graph)
It is a common scenario in real-time applications to insert entities along with their related child entities automatically.
- IncludeGraph: This option allows us to automatically insert all entities part of the graph.
- IncludeGraphBuilder: This option allows us to customize how to insert entities for a specific type.