Introduction

Everyone using Entity Framework and working with more than a few entities has experienced how the performance is ridiculously slow when using the SaveChanges method. Inserting data from an importation can take several minutes or even several hours which is unacceptable.

Why is it so slow? Entity Framework makes a database round-trip for every entities it's saving, so if you need to insert 1000 entities then 1000 sql commands will be executed which is far from being optimal. In fact, it's the worst possible scenario!

So what are the available options to improve insert performance for Entity Framework?

Solution

Creating and executing your own SqlBulkCopy or using a third party library which supports Bulk Insert in Entity Framework. The performance gain is huge, sometimes 5000% over SaveChanges method.

In this article, we will compare the following libraries:

Example


Comparison

Features EF Extensions EF BulkInsert EF Utilities
Price PRO FREE
Support
Performance
Output Identity Value
Complex Type
Enum
All Inheritances (TPC, TPH, TPT)
All Associations
SQL Server
SQL Azure
SQL Compact
SQLite
MySQL
Glimpse
RHinos

Entity Framework Extensions

Disclaimer

I'm the library owner.


Pros

Complete library supporting everything including all associations and inheritances (TPC, TPH and TPT). The customer support is outstanding and bulk insert is only the peak of the iceberg. The library allows to perform Bulk Insert, Bulk Update, Bulk Delete, Bulk Merge and Bulk SaveChanges within Entity Framework very easily with multiple customizations like the batch size and the key to use.

Even Microsoft uses this library to improve the performance over SaveChanges in some of their importations and successfully reduce the time taken from one hour down to one minute.

Cons

The price!

Discussion

More providers will be added soon like PostgreSQL and Oracle and the next major version will allow to batch/chain multiple operations. The library always remains under development for even more flexibility.

The Bulk Operations library is used in background to perform high efficient operations.


EntityFramework.BulkInsert


Pros

Free & Open source and handles perfectly all simple scenarios.

Cons

Limited and without any support. The project owner didn't answer any questions/issues for more than 2 years and has a lot of open issues.

Discussion

Even if the library does a good job, it’s hard to recommend an unsupported library. Sooner or later, you will have a scenario where the library will fail at and no one will be there to help you. In fact, I’m probably the one which supports the most this library on stack overflow with my favorite answer "The library doesn't support this!".


EntityFramework.Utilities


Pros

Free & Open source and handles perfectly all simple scenarios.

Cons

Most limited library and without any support. The project owner has not answered any questions/issues for several months now.

Discussion

Like EntityFramework.BulkInsert, the library is unsupported and contains a lot of problems. By example, the library will fail to read your model if you have complex inheritance like TPC (Table Per Concrete).


SqlBulkCopy

Pros

"Free" & highly customizable for your requirements.

Cons

You need to develop it yourself!

Discussion

Creating your own BulkInsert with SqlBulkCopy in some case may be the best solution if you only need to do it once. However, making it generic is harder than what most people believe. You need to use compiled expressions for performance, find the entity parent in complex associations to get the key value, get entity over proxy, find out the inheritance and perform multiple inserts if needed and a lot of more. The time spent doing this will be way more expensive than purchasing a ready to use library!


Conclusion

If you are looking for a Free & Open source solution, EntityFramework.BulkInsert is a better choice than EntityFramework.Utilities but keep in mind that both libraries are limited and unsupported.

However, if you are looking for a complete library that does more than only performing bulk insert and your company can't afford to purchase a PRO License, you should without a doubt try the Entity Framework Extensions free trial.


Share your experience

  • What features is missing?
  • Which library are you using and why?
  • What's your performance experience?