How to Perform Bulk Insert with EF Core

Entity Framework Core (EF Core) is a widely used Object-Relational Mapper (ORM) for .NET applications. It simplifies database operations by allowing developers to work with objects instead of SQL queries. However, EF Core is not optimized for bulk operations like inserting a large number of records. Using the default Add or AddRange methods for bulk inserts can lead to performance issues due to multiple database round-trips and change tracking overhead.

In this article, we will explore how to efficiently perform bulk inserts in EF Core using built-in approaches, third-party libraries, and raw SQL.

Why Bulk Insert Matters

When dealing with large datasets, inserting records one by one using EF Core’s default methods is inefficient and can cause:

• High Execution Time:
  Each insert involves a round trip to the database.

• Increased Memory Usage:
  EF Core tracks changes for each entity, consuming memory.

• Potential Bottlenecks:
  Large-scale operations can slow down the application.

To address these challenges, bulk insert techniques optimize database interactions by reducing round trips and bypassing unnecessary change tracking.

1. Default EF Core Approach

Example: **Using AddRange**

The simplest way to insert multiple records in EF Core is using AddRange:

public async Task BulkInsertWithAddRangeAsync(List<User> users)
{
    using var dbContext = new AppDbContext();

    dbContext.Users.AddRange(users);
    await dbContext.SaveChangesAsync();
}

Limitations:

• Change Tracking Overhead: EF Core tracks every entity in memory, consuming resources.

• Slow Performance: Inserts are sent to the database individually, leading to multiple round trips.

2. Using EF Core Extensions for Bulk Insert

Several third-party libraries provide optimized methods for bulk insert operations in EF Core. Here are two popular options:

(a) EFCore.BulkExtensions

EFCore.BulkExtensions is a library designed for high-performance bulk operations, including inserts, updates, and deletes.

Installation:

Add the package using NuGet:

Install-Package EFCore.BulkExtensions

Implementation:

public async Task BulkInsertWithBulkExtensionsAsync(List<User> users)
{
    using var dbContext = new AppDbContext();

    await dbContext.BulkInsertAsync(users);
}

Advantages:

• Minimal configuration required.

• Optimized for large-scale operations.

Limitations:

• External dependency.

• Limited to supported database providers (e.g., SQL Server).

(b) Z.EntityFramework.Extensions

Another powerful library is Z.EntityFramework.Extensions, which provides advanced features for bulk operations.

Installation:

Add the package using NuGet:

Install-Package EFCore.BulkExtensions

Implementation:

public async Task BulkInsertWithEFExtensionsAsync(List<User> users)
{
    using var dbContext = new AppDbContext();

    dbContext.BulkInsert(users);
}

Features:

• Support for complex operations like bulk updates and deletes.

• Enhanced performance compared to EF Core’s default methods.
  **

• Using Raw SQL for Bulk Insert**

If you want complete control over the bulk insert process and avoid external libraries, raw SQL is an efficient alternative.

Example:

public async Task BulkInsertWithRawSqlAsync(List<User> users)
{
    using var dbContext = new AppDbContext();

    var sql = "INSERT INTO Users (Name, Email, CreatedAt) VALUES (@Name, @Email, @CreatedAt)";

    var parameters = users.Select(u => new[]
    {
        new SqlParameter("@Name", u.Name),
        new SqlParameter("@Email", u.Email),
        new SqlParameter("@CreatedAt", u.CreatedAt)
    });

    foreach (var param in parameters)
    {
        await dbContext.Database.ExecuteSqlRawAsync(sql, param);
    }
}

Advantages:

• Full control over SQL execution.

• No additional library dependency.

Limitations:

• Requires manual handling of SQL queries.

• Potential for SQL injection if not handled carefully.

Best Practices for Bulk Insert

Use Transaction Scopes: Wrap bulk operations in transactions for atomicity.

using var transaction = await dbContext.Database.BeginTransactionAsync();
try
{
    await dbContext.BulkInsertAsync(users);
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
}

Batch Inserts: For extremely large datasets, insert records in batches to avoid overloading the database.

public async Task BulkInsertInBatchesAsync(List<User> users, int batchSize)
{
    using var dbContext = new AppDbContext();

    for (int i = 0; i < users.Count; i += batchSize)
    {
        var batch = users.Skip(i).Take(batchSize).ToList();
        await dbContext.BulkInsertAsync(batch);
    }
}

• ptimize Database Schema: Ensure indexes and constraints are

• appropriately set up to handle large inserts efficiently.

Conclusion

Bulk inserting data in EF Core can be a performance bottleneck if not handled properly. While EF Core’s AddRange method is straightforward, it’s not ideal for large-scale operations. Third-party libraries like EFCore.BulkExtensions and Z.EntityFramework.Extensions provide excellent solutions for efficient bulk inserts, while raw SQL gives you complete control.

By understanding and applying the techniques discussed in this article, you can significantly improve the performance of bulk data operations in your applications.

LinkedIn Account : LinkedIn
Twitter Account: Twitter
Credit: Graphics sourced from elmah.io