Improve Entity Framework Insert Performance

Introduction

Problem

You insert hundreds, thousands, or millions entities, and your application suffers from performances issues.

# Example

using (var ctx = new CustomerContext())
{
List<Customer> customers = new List<Customer>();
foreach(var line in lines)
{
var customer = new Customer();
// …code…
customers.Add(customer);
}
ctx.Customers.AddRange(customers);
ctx.SaveChanges();
}

Stack Overflow – Related Questions

  • Question 1
  • Question 2


Solution

  • Use BulkInsert (Recommanded)
  • Use SqlBulkCopy

Use BulkInsert (Recommanded)

Entity Framework Extensions add the BulkInsert extension method to the context. By default, identity value are populated to make it even easier to use.

# Performance Comparisons

Operations 1,000 Entities 2,000 Entities 5,000 Entities
SaveChanges 1,000 ms 2,000 ms 5,000 ms
BulkInsert 6 ms 10 ms 15 ms


# How?

1. CREATE a list

2. ADD entity to the list

3. USE BulkInsert

4. Done!


# Example

using (var ctx = new CustomerContext())
{
// 1. CREATE a list
List<Customer> customers = new List<Customer>();
foreach(var line in lines)
{
var customer = new Customer();
// …code…
// 2. ADD entity to the list
customers.Add(customer);
}
// 3. USE BulkInsert
ctx.BulkInsert();
// 4. Done!
}

# Free Third Party Library Alternative?

There are some free third party library alternative to Entity Framework Extensions, they are not hard to find but we don’t recommand them since they work with simple scenario but fail at supporting complex type, inheritance and association.

Use SqlBulkCopy

Using SqlBulkCopy (for SQL Server) is without a doubt the fastest solution (very slighty faster than Entity Framework Extensions) but also the longer to implement.

Be careful, this solution add some code complexity for the maintenance team.

# How?

1. CREATE a list

2. ADD entity to the list

3. CONVERT the list to a DataTable

4. CREATE a SqlBulkCopy instance

5. MAP the SqlBulkCopy to database

6. Use WriteToServer

7. Done!