DEV Community

Cover image for How to Perform Bulk Insert with EF Core
Odumosu Matthew
Odumosu Matthew

Posted on

2 2

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();
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Implementation:

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

    await dbContext.BulkInsertAsync(users);
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Implementation:

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

    dbContext.BulkInsert(users);
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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.

efcore

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();
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode
  • 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

Image of AssemblyAI

Automatic Speech Recognition with AssemblyAI

Experience near-human accuracy, low-latency performance, and advanced Speech AI capabilities with AssemblyAI's Speech-to-Text API. Sign up today and get $50 in API credit. No credit card required.

Try the API

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay