Entity Framework Insert Multiple Entities Discover to Improve Insert

How to Insert Multiple Entities at Once?

When you want to insert hundreds, thousands, or millions of entities in one go.

StackOverflow Related Questions

• Entity Framework 6 DbSet AddRange vs IDbSet Add - How Can AddRange be so much faster?
• Bulk insert using AddRange in Entity framework [closed]

Answer

AddRange

The AddRange method is used for adding multiple objects to the database in one method call. The following code inserts a large number of records into the database using AddRange.

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();
}

Try it online

AddRange adds the given collection of entities into context underlying the set with each entity being put into the Added state such that it will be inserted into the database when SaveChanges is called.

But when you look at the query generated by EF, you will see multiple insert statements for each record to be inserted of a single query that will be sent to a database.

The only possibility to get 1 single statement that does all the inserts is to use 3rd party library such as Entity Framework Extensions.

BulkInsert

There are many solutions to insert many records in the fastest way, but the most significant and recommended solution is BulkInsert provided by Entity Framework Extensions library. By default, the identity value is populated to make it even easier to use.

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(customers);
    
    // 4. Done!
}

Try it online

Alternative to BulkInsert

There is some free third-party libraries alternative to Entity Framework Extensions, they are not hard to find, but we don't recommend them since they work with the simple scenario but fail at supporting complex type, inheritance, and association.

SqlBulkCopy

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

Be careful; this solution adds some code complexity for the maintenance team.