Back

TechnologyNov 24, 2014

Entity Framework Batch Operations Using EF Utilities

Denis Stetsenko

Entity Framework (EF) is a great data access technology that enables .NET developers to work with relational data using domain objects. It’s easy to implement basic CRUD (create, read, update, delete) functionality using EF and its performance is fast enough for most scenarios. However, performance takes a hit when working with a large list of domain objects. The performance can be significantly improved with EF Utilities batch CUD (create, update, delete) operations.

In this post, I will provide a quick overview of the EF Utilities library and compare its performance to the traditional EF approach when dealing with large lists of entities. Spoiler alert: Performance test results are stunning!

ef utilities

EntityFramework.Utilities, created by @MikaelEliasson, provides batch operations for inserting, updating, and deleting many entities on a SQL server database. Batch operations are much faster than the traditional EF operations. To install EF Utilities, run the following command in the VS Package Manager console:

Install-Package EFUtilities

Then, add the following using statement where appropriate:

using EntityFramework.Utilities

Now you are ready to take advantage of the following batch operations:

Batch Insert: Inserts a list of entities into the database. Example:

EFBatchOperation.For(context, context.Books).InsertAll(booksList);

Update by Query: Updates a property of a specific entity in the database. Example:

EFBatchOperation.For(context, context.Books).Where(b => b.Title == "An American Tragedy").Update(b => b.NumberOfPages, b => b.NumberOfPages + 100);

Delete by Query: Deletes specified entities from the database. Example:

EFBatchOperation.For(context, context.Books).Where(b => b.Genre == Genres.Biography).Delete();

There a number of limitations and caveats to be aware of when using these operations, since they run batch operations directly against the database, circumventing various EF conventions in a way that doesn’t really align with DBContext. EF Utilities also includes other helpful methods, such as a faster include. Visit GitHub for more information.

performance test

To compare the performance of the EF Utilities batch operations with the traditional EF operations, I created the following simple domain object:

public class Book    {        public int Id { get; set; }        public string Title { get; set; }        public string Author { get; set; }        public string Description { get; set; }        public Genre Genre { get; set; }        public DateTime PublishDate { get; set; }        public int NumberOfPages { get; set; }    }

Then, I added code to perform the following operations:

  1. Create 50,000 books and add them to the database.

  2. Update all books by incrementing NumberOfPages by 100.

  3. Delete 10,000 Biography books from the database.

The test was run on my Dell E7440 Ultrabook (Core i7, 16Gb RAM, SSD) against a LocalDB SQL server instance.

test results

Here are the test results showing complete execution time in seconds:

Screen-Shot-2014-11-10-at-11.11.03-AM

The results are stunning! The reason for such a performance increase is because traditional EF InsertRange method runs a separate INSERT SQL statement for each item in the list, while the batch insert operation provided by EF Utilities runs one INSERT BULK SQL statement utilizing the SqlBulkCopy class. Updates and deletes also work directly against the database.

Keep in mind that even though batch operations are much faster than traditional EF operations, due to their limitations they may not be the right tool for the job in all scenarios. For instance, if my Book domain object had a navigational property called Author pointing to an Author domain object, then the batch insert operation would set all Author_Id values to NULL in the database.

conclusion

Batch operations are extremely useful for specific scenarios where a large number of entities have to be inserted, updated, or deleted from the database and performance is important. This is ideal for initial data seeds and certain data migration situations. Hopefully future versions of Entity Framework will include some batch operation support.

Explore Our Microsoft Consulting Services  →