Fast SQL Bulk Inserts With C# and EF Core

Whether you're building a data analytics platform, migrating a legacy system, or onboarding a surge of new users, there will likely come a time when you'll need to insert a massive amount of data into your database.

Inserting the records one by one feels like watching paint dry in slow motion. Traditional methods won't cut it.

So, understanding fast bulk insert techniques with C# and EF Core becomes essential.

In today's issue, we'll explore several options for performing bulk inserts in C#:

• Dapper
• EF Core
• EF Core Bulk extensions
• SQL Bulk Copy
• Entity Framework Extensions

The examples are based on a User class with a respective Users table in SQL Server.

public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PhoneNumber { get; set; }
}

This isn't a complete list of bulk insert implementations. There are a few options I didn't explore, like manully generating SQL statements and using Table-Valued parameters.

What Is EF Core Bulk Insert?

When working with large datasets, standard EF Core patterns quickly become a bottleneck. Using Add and SaveChanges for thousands of records means EF tracks every entity in memory and generates individual SQL INSERT statements, each requiring its own round trip to the database.

EF Core bulk insert techniques solve this by taking one of two approaches: either batching many INSERT statements into a single network round trip (the built-in AddRange), or bypassing EF's change tracker entirely and streaming data using the database's native bulk copy protocol (like SQL Server's SqlBulkCopy). The result can reduce insert time from minutes to seconds for large datasets.

EF Core Simple Approach

Let's start with a simple example using EF Core. We're creating an ApplicationDbContext instance, adding a User object, and calling SaveChangesAsync. This will insert each record to the database one by one. In other words, each record requires one round trip to the database.

using var context = new ApplicationDbContext();

foreach (var user in GetUsers())
{
    context.Users.Add(user);

    await context.SaveChangesAsync();
}

The results are as poor as you'd expect:

EF Core - Add one and save, for 100 users: 20 ms
EF Core - Add one and save, for 1,000 users: 260 ms
EF Core - Add one and save, for 10,000 users: 8,860 ms

I omitted the results with 100,000 and 1,000,000 records because they took too long to execute.

We'll use this as a "how not to do bulk inserts" example.

Dapper Simple Insert

Dapper is a simple SQL-to-object mapper for .NET. It allows us to easily insert a collection of objects into the database.

I'm using Dapper's feature to unwrap a collection into a SQL INSERT statement.

using var connection = new SqlConnection(connectionString);
connection.Open();

const string sql =
    @"
    INSERT INTO Users (Email, FirstName, LastName, PhoneNumber)
    VALUES (@Email, @FirstName, @LastName, @PhoneNumber);
    ";

await connection.ExecuteAsync(sql, GetUsers());

The results are much better than the initial example:

Dapper - Insert range, for 100 users: 10 ms
Dapper - Insert range, for 1,000 users: 113 ms
Dapper - Insert range, for 10,000 users: 1,028 ms
Dapper - Insert range, for 100,000 users: 10,916 ms
Dapper - Insert range, for 1,000,000 users: 109,065 ms

EF Core Add and Save

However, EF Core still didn't throw in the towel. The first example was poorly implemented on purpose. EF Core can batch multiple SQL statements together, so let's use that.

If we make a simple change, we can get significantly better performance. First, we're adding all the objects to the ApplicationDbContext. Then, we're going to call SaveChangesAsync only once.

EF will create a batched SQL statement - group many INSERT statements together - and send them to the database together. This reduces the number of round trips to the database, giving us improved performance.

using var context = new ApplicationDbContext();

foreach (var user in GetUsers())
{
    context.Users.Add(user);
}

await context.SaveChangesAsync();

Here are the benchmark results of this implementation:

EF Core - Add all and save, for 100 users: 2 ms
EF Core - Add all and save, for 1,000 users: 18 ms
EF Core - Add all and save, for 10,000 users: 203 ms
EF Core - Add all and save, for 100,000 users: 2,129 ms
EF Core - Add all and save, for 1,000,000 users: 21,557 ms

Remember, it took Dapper 109 seconds to insert 1,000,000 records. We can achieve the same with EF Core batched queries in ~21 seconds.

EF Core AddRange and Save

This is an alternative to the previous example. Instead of calling Add for all objects, we can call AddRange and pass in a collection.

I wanted to show this implementation because I prefer it over the previous one.

using var context = new ApplicationDbContext();

context.Users.AddRange(GetUsers());

await context.SaveChangesAsync();

The results are very similar to the previous example:

EF Core - Add range and save, for 100 users: 2 ms
EF Core - Add range and save, for 1,000 users: 18 ms
EF Core - Add range and save, for 10,000 users: 204 ms
EF Core - Add range and save, for 100,000 users: 2,111 ms
EF Core - Add range and save, for 1,000,000 users: 21,605 ms

EF Core Bulk Insert with EF Core Bulk Extensions

There's an awesome library called EF Core Bulk Extensions that we can use to squeeze out more performance. You can do a lot more than bulk inserts with this library, so it's worth exploring. This library is open source, and has a community license if you meet the free usage criteria. Check the licensing section for more details.

For our use case, the BulkInsertAsync method is an excellent choice. We can pass the collection of objects, and it will perform an SQL bulk insert.

using var context = new ApplicationDbContext();

await context.BulkInsertAsync(GetUsers());

The performance is equally amazing:

EF Core - Bulk Extensions, for 100 users: 1.9 ms
EF Core - Bulk Extensions, for 1,000 users: 8 ms
EF Core - Bulk Extensions, for 10,000 users: 76 ms
EF Core - Bulk Extensions, for 100,000 users: 742 ms
EF Core - Bulk Extensions, for 1,000,000 users: 8,333 ms

For comparison, we needed ~21 seconds to insert 1,000,000 records with EF Core batched queries. We can do the same with the Bulk Extensions library in just 8 seconds.

SQL Bulk Insert with SqlBulkCopy

If we can't get the desired performance from EF Core, we can try using SqlBulkCopy. SQL Server supports bulk copy operations natively, so let's use this.

This implementation is slightly more complex than the EF Core examples. We need to configure the SqlBulkCopy instance and create a DataTable containing the objects we want to insert.

using var bulkCopy = new SqlBulkCopy(ConnectionString);

bulkCopy.DestinationTableName = "dbo.Users";

bulkCopy.ColumnMappings.Add(nameof(User.Email), "Email");
bulkCopy.ColumnMappings.Add(nameof(User.FirstName), "FirstName");
bulkCopy.ColumnMappings.Add(nameof(User.LastName), "LastName");
bulkCopy.ColumnMappings.Add(nameof(User.PhoneNumber), "PhoneNumber");

await bulkCopy.WriteToServerAsync(GetUsersDataTable());

However, the performance is blazing fast:

SQL Bulk Copy, for 100 users: 1.7 ms
SQL Bulk Copy, for 1,000 users: 7 ms
SQL Bulk Copy, for 10,000 users: 68 ms
SQL Bulk Copy, for 100,000 users: 646 ms
SQL Bulk Copy, for 1,000,000 users: 7,339 ms

Here's how you can create a DataTable and populate it with a list of objects:

DataTable GetUsersDataTable()
{
    var dataTable = new DataTable();

    dataTable.Columns.Add(nameof(User.Email), typeof(string));
    dataTable.Columns.Add(nameof(User.FirstName), typeof(string));
    dataTable.Columns.Add(nameof(User.LastName), typeof(string));
    dataTable.Columns.Add(nameof(User.PhoneNumber), typeof(string));

    foreach (var user in GetUsers())
    {
        dataTable.Rows.Add(
            user.Email, user.FirstName, user.LastName, user.PhoneNumber);
    }

    return dataTable;
}

Entity Framework Bulk Insert with EF Core Extensions

Can we do better than SqlBulkCopy?

Maybe, at least my benchmark results suggest that we can.

There's another awesome library called Entity Framework Extensions. It's much more than just a bulk insert library - so I highly recommend checking it out. However, we'll use it for bulk inserts today.

For our use case, the BulkInsertOptimizedAsync method is an excellent choice. We can pass the collection of objects, and it will perform an SQL bulk insert. It'll also do some optimizations under the hood to improve performance.

using var context = new ApplicationDbContext();

await context.BulkInsertOptimizedAsync(GetUsers());

The performance is nothing short of amazing:

EF Core - Entity Framework Extensions, for 100 users: 1.86 ms
EF Core - Entity Framework Extensions, for 1,000 users: 6.9 ms
EF Core - Entity Framework Extensions, for 10,000 users: 66 ms
EF Core - Entity Framework Extensions, for 100,000 users: 636 ms
EF Core - Entity Framework Extensions, for 1,000,000 users: 7,106 ms

EF Core Bulk Insert vs SqlBulkCopy vs Dapper

Not all bulk insert options are equal. Here's a comparison to help you choose the right approach:

| Method                                                 | Relative Speed | EF Core Integration         | Database Support   | License          |
| ------------------------------------------------------ | -------------- | --------------------------- | ------------------ | ---------------- |
| EF Core `AddRange` + `SaveChanges`                     | Medium         | Full (change tracking)      | All EF providers   | Free             |
| Dapper `ExecuteAsync`                                  | Medium         | None                        | Any ADO.NET        | Free             |
| EFCore.BulkExtensions `BulkInsertAsync`                | Fast           | Partial (bypasses tracking) | Multiple providers | Free (community) |
| `SqlBulkCopy`                                          | Fastest        | None                        | SQL Server only    | Free             |
| Entity Framework Extensions `BulkInsertOptimizedAsync` | Fastest        | Partial (bypasses tracking) | Multiple providers | Commercial       |

EF Core AddRange is the simplest option and works out of the box, but it goes through the change tracker, making it slower for large datasets.

Dapper is a good fit if you're already using it in your stack, but it doesn't integrate with your EF Core context.

EFCore.BulkExtensions hits a sweet spot: near-SqlBulkCopy speed, works with your existing DbContext, and is open source.

SqlBulkCopy is the raw speed champion for SQL Server but requires boilerplate DataTable setup and is SQL Server-only.

Entity Framework Extensions matches SqlBulkCopy performance with EF Core integration, but requires a commercial license.

When Should You Use Each EF Core Bulk Insert Method?

Choose the right method based on your scenario:

| Scenario                                                              | Recommended Method                 |
| --------------------------------------------------------------------- | ---------------------------------- |
| Small datasets (under 1,000 rows)                                     | EF Core `AddRange` + `SaveChanges` |
| Medium datasets with existing Dapper infrastructure                   | Dapper `ExecuteAsync`              |
| Large datasets, open-source required, EF Core codebase                | `EFCore.BulkExtensions`            |
| Maximum speed, SQL Server only                                        | `SqlBulkCopy`                      |
| Maximum speed with EF Core integration, commercial license acceptable | Entity Framework Extensions        |
| PostgreSQL, MySQL, or other non-SQL Server databases                  | `EFCore.BulkExtensions`            |

Results

Here are the results for all the bulk insert implementations:

| Method             |   Size     |      Speed
|------------------- |----------- |----------------:
| EF_OneByOne        | 100        |      19.800 ms |
| EF_OneByOne        | 1000       |     259.870 ms |
| EF_OneByOne        | 10000      |   8,860.790 ms |
| EF_OneByOne        | 100000     |            N/A |
| EF_OneByOne        | 1000000    |            N/A |

| Dapper_Insert      | 100        |      10.650 ms |
| Dapper_Insert      | 1000       |     113.137 ms |
| Dapper_Insert      | 10000      |   1,027.979 ms |
| Dapper_Insert      | 100000     |  10,916.628 ms |
| Dapper_Insert      | 1000000    | 109,064.815 ms |

| EF_AddAll          | 100        |       2.064 ms |
| EF_AddAll          | 1000       |      17.906 ms |
| EF_AddAll          | 10000      |     202.975 ms |
| EF_AddAll          | 100000     |   2,129.370 ms |
| EF_AddAll          | 1000000    |  21,557.136 ms |

| EF_AddRange        | 100        |       2.035 ms |
| EF_AddRange        | 1000       |      17.857 ms |
| EF_AddRange        | 10000      |     204.029 ms |
| EF_AddRange        | 100000     |   2,111.106 ms |
| EF_AddRange        | 1000000    |  21,605.668 ms |

| BulkExtensions     | 100        |       1.922 ms |
| BulkExtensions     | 1000       |       7.943 ms |
| BulkExtensions     | 10000      |      76.406 ms |
| BulkExtensions     | 100000     |     742.325 ms |
| BulkExtensions     | 1000000    |   8,333.950 ms |

| BulkCopy           | 100        |       1.721 ms |
| BulkCopy           | 1000       |       7.380 ms |
| BulkCopy           | 10000      |      68.364 ms |
| BulkCopy           | 100000     |     646.219 ms |
| BulkCopy           | 1000000    |   7,339.298 ms |

| EF Extensions      | 100        |       1.860 ms |
| EF Extensions      | 1000       |       6.923 ms |
| EF Extensions      | 10000      |      68.106 ms |
| EF Extensions      | 100000     |     636.231 ms |
| EF Extensions      | 1000000    |   7,106.891 ms |

Takeaway

SqlBulkCopy holds the crown for maximum raw speed and simplicity. However, Entity Framework Extensions deliver fantastic performance while maintaining the ease of use that EF Core is known for.

The best choice hinges on your project's specific demands:

• Performance is all that matters? SqlBulkCopy is your solution.
• Need excellent speed and streamlined development? EF Core is a smart choice.
• Want a balance between performance and ease of use? Consider using Entity Framework Extensions.

I leave it up to you to decide which option is best for your use case.

Hope this was helpful.

See you next week.

Frequently Asked Questions

What is the fastest way to bulk insert in EF Core?

The fastest approach depends on your requirements. EFCore.BulkExtensions and Entity Framework Extensions both offer native bulk insert methods that bypass EF's change tracking and generate optimized SQL. For pure speed, SqlBulkCopy from System.Data.SqlClient is typically the fastest for SQL Server.

Does EF Core support bulk insert natively?

EF Core does not have a dedicated bulk insert API. Using AddRange with SaveChanges batches SQL inserts but still goes through the change tracker. For true bulk inserts, you need a library like EFCore.BulkExtensions or raw SqlBulkCopy.

What is the difference between bulk insert and batch insert in EF Core?

Batch insert groups multiple INSERT statements into a single round trip using EF Core's built-in batching. Bulk insert bypasses EF entirely and streams data in a highly optimized binary format (like BCP for SQL Server), making it significantly faster for large datasets.

Is SqlBulkCopy faster than EF Core BulkExtensions?

SqlBulkCopy is generally the fastest option because it uses SQL Server's native bulk copy protocol and skips all ORM overhead. EFCore.BulkExtensions is slightly slower but integrates with your existing EF Core context and works across multiple database providers.

Can you bulk insert with EF Core and keep change tracking?

No. The primary benefit of bulk insert libraries is that they bypass EF Core's change tracker. If you need change tracking, use the standard EF Core Add or AddRange approach, but expect significantly worse performance for large datasets.

Is Dapper good for bulk insert operations?

Dapper is decent for medium-sized bulk inserts. It lets you pass a collection to ExecuteAsync and generates individual INSERT statements in one round trip. It is faster than EF Core's naive approach but slower than SqlBulkCopy or EFCore.BulkExtensions for very large datasets.

What is EFCore.BulkExtensions and when should you use it?

EFCore.BulkExtensions is an open-source library that extends EF Core with BulkInsert, BulkUpdate, BulkDelete, and BulkMerge methods. Use it when you need fast bulk operations that still integrate cleanly with your EF Core data model.