This article tests out variying methods of ways of doing bulk inserts in EntityFramework. The code shown in this article uses .NET Framework 4.8
and Entity Framework 6.5.0. Support is better in Entity Framework Core, so the code shown here for SqlBulkCopy must be adjusted a little bit to also work with EntityFramework Core.
A Github repo has been added here:
https://github.com/toreaurstadboss/BulkOperationsEntityFramework
A benchmark has been run to test out the different approaches. Not suprisingly, SqlBulkCopy is the fastest and most economic way of performing the bulk inserts (uses the least memory).
The code for handling SqlBulkCopy is provided via extension methods listed below:
BulkInsertExtensions.cs
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace BulkOperationsEntityFramework.Lib.Extensions
{
/// <summary>
/// Provides extension methods for performing bulk insert operations using SqlBulkCopy.
/// </summary>
public static class BulkInsertExtensions
{
/// <summary>
/// Performs a bulk insert of the specified entities into the database.
/// </summary>
/// <typeparam name="T">The type of the entity.</typeparam>
/// <param name="context">The DbContext instance.</param>
/// <param name="entities">The collection of entities to insert.</param>
/// <param name="tableName">
/// Optional: The name of the destination table. If null, the entity type name is used.
/// </param>
/// <param name="columnMappings">
/// Optional: Custom column mappings (propertyName → columnName).
///
/// <example>
/// Example 1: Rename columns
/// <code>
/// var mappings = new Dictionary<string, string>
/// {
/// { "Name", "ProductName" },
/// { "Price", "UnitPrice" }
/// };
/// await context.BulkInsertAsync(products, "Products", mappings);
/// </code>
/// </example>
///
/// <example>
/// Example 2: Ignore properties using attributes
/// <code>
/// public class Customer
/// {
/// public int Id { get; set; }
/// public string FullName { get; set; }
///
/// [BulkIgnore]
/// public string TempNotes { get; set; }
///
/// [NotMapped]
/// public string CalculatedField { get; set; }
/// }
///
/// var mappings = new Dictionary<string, string>
/// {
/// { "FullName", "CustomerName" }
/// };
/// await context.BulkInsertAsync(customers, "Customers", mappings);
/// </code>
/// </example>
///
/// <example>
/// Example 3: Snake_case mapping
/// <code>
/// var mappings = new Dictionary<string, string>
/// {
/// { "FirstName", "first_name" },
/// { "LastName", "last_name" },
/// { "Email", "email_address" }
/// };
/// await context.BulkInsertAsync(users, "user_accounts", mappings);
/// </code>
/// </example>
/// </param>
/// <param name="bulkCopyTimout">The bulk copy timeout in seconds. Default is set to 30.</param>
public static void BulkInsert<T>(this DbContext context, IEnumerable<T> entities, string tableName = null,
Dictionary<string, string> columnMappings = null, int bulkCopyTimeout = 30)
where T : class
{
var dataTable = entities.ToBulkDataTable(columnMappings, out var finalMappings);
BulkCopy(context, dataTable, tableName ?? typeof(T).Name, finalMappings, bulkCopyTimeout);
}
/// <summary>
/// Asynchronously performs a bulk insert of the specified entities into the database.
/// </summary>
/// <typeparam name="T">The type of the entity.</typeparam>
/// <param name="context">The DbContext instance.</param>
/// <param name="entities">The collection of entities to insert.</param>
/// <param name="tableName">
/// Optional: The name of the destination table. If null, the entity type name is used.
/// </param>
/// <param name="columnMappings">
/// Optional: Custom column mappings (propertyName → columnName).
///
/// <example>
/// Example 1: Rename columns
/// <code>
/// var mappings = new Dictionary<string, string>
/// {
/// { "Name", "ProductName" },
/// { "Price", "UnitPrice" }
/// };
/// await context.BulkInsertAsync(products, "Products", mappings);
/// </code>
/// </example>
///
/// <example>
/// Example 2: Ignore properties using attributes
/// <code>
/// public class Customer
/// {
/// public int Id { get; set; }
/// public string FullName { get; set; }
///
/// [BulkIgnore]
/// public string TempNotes { get; set; }
///
/// [NotMapped]
/// public string CalculatedField { get; set; }
/// }
///
/// var mappings = new Dictionary<string, string>
/// {
/// { "FullName", "CustomerName" }
/// };
/// await context.BulkInsertAsync(customers, "Customers", mappings);
/// </code>
/// </example>
///
/// <example>
/// Example 3: Snake_case mapping
/// <code>
/// var mappings = new Dictionary<string, string>
/// {
/// { "FirstName", "first_name" },
/// { "LastName", "last_name" },
/// { "Email", "email_address" }
/// };
/// await context.BulkInsertAsync(users, "user_accounts", mappings);
/// </code>
/// </example>
/// </param>
/// <param name="bulkCopyTimout">The bulk copy timeout in seconds. Default is set to 30.</param>
public static async Task BulkInsertAsync<T>(this DbContext context, IEnumerable<T> entities, string tableName = null,
Dictionary<string, string> columnMappings = null, int bulkCopyTimout = 30)
where T : class
{
var dataTable = entities.ToBulkDataTable(columnMappings, out var finalMappings);
await BulkCopyAsync(context, dataTable, tableName ?? typeof(T).Name, finalMappings, 30);
}
private static void BulkCopy(DbContext context, DataTable table, string tableName,
Dictionary<string, string> finalMappings, int bulkCopyTimeout = 30)
{
var connection = (SqlConnection)context.Database.Connection;
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
connection.Open();
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
foreach (var map in finalMappings)
{
bulkCopy.ColumnMappings.Add(map.Key, map.Value);
}
bulkCopy.WriteToServer(table);
}
if (wasClosed)
connection.Close(); // Ensure the connection is closed after the operation, if it was closed before
}
private static async Task BulkCopyAsync(DbContext context, DataTable table, string tableName, Dictionary<string, string> mappings,
int bulkCopyTimeout = 30)
{
var connection = (SqlConnection)context.Database.Connection;
var wasClosed = connection.State == ConnectionState.Closed;
if (wasClosed)
await connection.OpenAsync();
using (var bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.BulkCopyTimeout = bulkCopyTimeout;
foreach (var map in mappings)
{
bulkCopy.ColumnMappings.Add(map.Key, map.Value);
}
await bulkCopy.WriteToServerAsync(table);
}
if (wasClosed)
connection.Close(); //Ensure the connection is closed after the operation, if it was closed before
}
}
}
As the code shows, we use the
DbContext from EntityFramework and get the connection string from the Database.Connection.ConnectionString property. We also use the Database.Connection property and cast it to SqlConnection to get the connection object. As we see, SqlBulkCopy is part of ADO.Net and relies upon column mappings. The code above will ignore the properties marked by the
BulkIgnore attribute and the
NotMapped attribute
BulkIgnoreAttribute.cs
using System;
namespace BulkOperationsEntityFramework.Lib.Attributes
{
///
/// Indicates that a property should be ignored during bulk insert operations.
///
[AttributeUsage(AttributeTargets.Property)]
public class BulkIgnoreAttribute : Attribute
{
}
}
The following helper class creates a
DataTable for a collection of entities (an IEnumerable) and uses reflection to make such a mapping. Note that column mappings can also be provided in the BulkInsertExtensions methods shown above to override property to column mapping, if desired. Default sql bulk copy timeout is set to 30 seconds (it is also the default timeout)
DataTableExtensions.cs
The following code provides code for creating data table for a collection of objects (IEnumerable). The BulkInsertExtensions uses the specific method ToBulkDataTable. This data table skips properties that are marked with [NotMapped] or [BulkIgnore] attributes. Both ToDataTable methods shown here allows column mappings to customize the property to column mappings.
using BulkOperationsEntityFramework.Lib.Attributes;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
namespace BulkOperationsEntityFramework.Lib.Extensions
{
public static class DatatableExtensions
{
/// <summary>
/// Converts an IEnumerable of type T to a DataTable.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data"></param>
/// <param name="columnMappings"></param>
/// <returns></returns>
public static DataTable ToDataTable<T>(this IEnumerable<T> data, Dictionary<string, string> columnMappings = null)
{
var dataTable = new DataTable();
var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
foreach (var prop in properties)
{
var columnName = columnMappings != null && columnMappings.ContainsKey(prop.Name) ? columnMappings[prop.Name] : prop.Name;
dataTable.Columns.Add(columnName, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
}
foreach (var item in data)
{
var values = properties.Select(p => p.GetValue(item) ?? DBNull.Value).ToArray();
dataTable.Rows.Add(values);
}
return dataTable;
}
/// <summary>
/// Converts an IEnumerable of type T to a DataTable with specified column mappings. Tailored for Bulk operations.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entities"></param>
/// <param name="columnMappings"></param>
/// <param name="finalMappings"></param>
/// <returns></returns>
public static DataTable ToBulkDataTable<T>(this IEnumerable<T> entities, Dictionary<string, string> columnMappings, out Dictionary<string, string> finalMappings)
{
var dataTable = new DataTable();
var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
.Where(p =>
!Attribute.IsDefined(p, typeof(System.ComponentModel.DataAnnotations.Schema.NotMappedAttribute)) &&
!Attribute.IsDefined(p, typeof(BulkIgnoreAttribute)))
.ToArray();
finalMappings = new Dictionary<string, string>();
foreach (var prop in properties)
{
var columnName = columnMappings != null && columnMappings.ContainsKey(prop.Name)
? columnMappings[prop.Name]
: prop.Name;
dataTable.Columns.Add(columnName, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
finalMappings[prop.Name] = columnName;
}
foreach (var entity in entities)
{
var values = properties.Select(p => p.GetValue(entity) ?? DBNull.Value).ToArray();
dataTable.Rows.Add(values);
}
return dataTable;
}
}
}
BulkInsertBenchmark
The following benchmark is available in the solution for benchmarking the different approaches to bulk copy with EntityFramework.
- EF - add one and save in a loop. Not suprisingly, performs the worst due to the many roundtrips to the database
- EF - add one by one and save at the end. Better performance, since we have one roundtrip. Will handle poor cases where we try to add many items in a batch.
- EF - addrange and save at the end. Similar to the one above, one roundtrip to database
- Dapper - add as batch and save. Minor better speed than the previous two. One roundtrip to database.
- SqlBulkCopy - clearly the fastest way to insert a batch of entities to the database
using BenchmarkDotNet.Attributes;
using Bogus;
using BulkOperationsEntityFramework.Lib.Extensions;
using BulkOperationsEntityFramework.Models;
using Dapper;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
namespace BulkOperationsEntityFramework.Benchmarks
{
[MemoryDiagnoser]
public class BulkInsertBenchmark
{
private static readonly Faker Faker = new Faker();
[Params(100)]
public int Size { get; set; }
//First benchmark - Naive approach - add one and one entity and save changes everytime with round trip to database
[Benchmark(Description = "EFAddOneAndSave - Add one user and then save. Then add another one. Results in many round-trips to database.")]
public async Task EFAddOneAndSave()
{
using (var context = new ApplicationDbContext())
{
foreach (var user in GetUsers())
{
context.Users.Add(user);
await context.SaveChangesAsync();
}
}
}
[Benchmark(Description = "EFAddOneByOneAndSave - Add one by one user, but save once after adding them. Results in one round-trip to database")]
public async Task EFAddOneByOneAndSave()
{
using (var context = new ApplicationDbContext())
{
foreach (var user in GetUsers())
{
context.Users.Add(user);
}
await context.SaveChangesAsync();
}
}
[Benchmark(Description = "EFAddRange - Adds the users, then does the save. Results in one round-trip to database")]
public async Task EFAddRange()
{
using (var context = new ApplicationDbContext())
{
var users = GetUsers();
context.Users.AddRange(users);
await context.SaveChangesAsync();
}
}
[Benchmark(Description = "DapperInsertRange - Uses Dapper to insert the users. Results in one round-trip to database")]
public async Task DapperInsertRange()
{
var connectionString = ConfigurationManager.ConnectionStrings["App"].ConnectionString;
string sql = @"
INSERT INTO Users (Email, FirstName, LastName, PhoneNumber)
VALUES (@Email, @FirstName, @LastName, @PhoneNumber)
".Trim();
using (var connection = new SqlConnection(connectionString))
{
var users = GetUsers().Select(u => new
{
u.Email,
u.FirstName,
u.LastName,
u.PhoneNumber
}).ToArray();
await connection.ExecuteAsync(sql, users);
}
}
[Benchmark(Description = "SqlBulkCopy - Uses SqlBulkCopy to insert the users. Results in one round-trip to database")]
public async Task SqlBulkCopy()
{
using (var context = new ApplicationDbContext())
{
await context.BulkInsertAsync(GetUsers(), "Users");
}
}
private User[] GetUsers() =>
Enumerable.Range(1, Size).Select(i => new User
{
Email = Faker.Internet.Email(),
FirstName = Faker.Name.FirstName(),
LastName = Faker.Name.LastName(),
PhoneNumber = Faker.Phone.PhoneNumber()
}).ToArray();
}
}
The following screenshots shows the benchmark results after running Benchmark.NET for the mentioned approaches and comparing the performance.
Results
Benchmark for a given batch size of 100
Benchmark for a given batch size of 100
