How to Perform Bulk Insert in EF Core: Methods, Libraries, and Best Practices

Entity Framework Core (EF Core) is a popular Object-Relational Mapper (ORM) for .NET applications, enabling developers to interact with databases using .NET objects instead of writing raw SQL queries. While EF Core excels in many areas, it isn’t inherently optimized for bulk operations, such as inserting large volumes of records. Utilizing the standard Add or AddRange methods for bulk inserts can lead to performance bottlenecks due to multiple database round-trips and the overhead of change tracking.

This guide provides effective strategies for performing bulk inserts in EF Core, exploring built-in methods, third-party libraries, and raw SQL approaches to optimize performance and resource usage.

Why Bulk Insert Matters

Handling large datasets efficiently is crucial for applications that require high performance and scalability. Bulk insert operations are essential in scenarios like:

• Data Importation: Importing large CSV files or data from external sources.
• Data Synchronization: Syncing data between different systems or services.
• Database Seeding: Populating databases with initial or extensive datasets.

Challenges with Default Insert Methods

Using EF Core’s default insertion methods for large datasets can result in:

• High Execution Time: Each entity insertion triggers a separate SQL INSERT statement, leading to increased total execution time.
• Increased Memory Usage: EF Core’s change tracking for each entity consumes significant memory, especially with thousands or millions of records.
• Potential Bottlenecks: Large-scale insert operations can strain the database, causing slowdowns or timeouts.

Bulk insert techniques mitigate these issues by optimizing database interactions, reducing the number of round-trips, and minimizing unnecessary overhead.

1. Default EF Core Approach

Using AddRange

The most straightforward method to insert multiple records in EF Core is using the AddRange method, followed by SaveChangesAsync:

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 each entity’s state, leading to increased memory consumption.
• Performance Degradation: Each INSERT operation is executed individually, resulting in multiple database round-trips.
• Scalability Issues: Handling thousands or millions of records becomes impractical due to the cumulative overhead.

Performance Considerations

• N+1 Problem: Each entity triggers a separate SQL command, leading to linear performance degradation as the number of entities increases.
• Latency Impact: Multiple round-trips to the database amplify latency, especially noticeable over networked environments.
• Resource Consumption: EF Core’s internal mechanisms, such as change tracking and logging, add to the resource burden during bulk operations.

For small datasets, AddRange might suffice, but alternative approaches are necessary for large-scale insertions to achieve optimal performance.

2. Using EF Core Extensions for Bulk Insert

To overcome the limitations of EF Core’s default methods, several third-party libraries offer optimized bulk operations. Two notable options are:

(a) EFCore.BulkExtensions

EFCore.BulkExtensions is an open-source library tailored for high-performance bulk operations within EF Core. It supports a variety of bulk actions, including insert, update, delete, and merge.

Installation

Add the package via NuGet:

dotnet add package EFCore.BulkExtensions

Basic Usage

using EFCore.BulkExtensions;

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

    await dbContext.BulkInsertAsync(users);
}

Advanced Configuration

Customize bulk operations using BulkConfig:

await dbContext.BulkInsertAsync(users, new BulkConfig
{
    PreserveInsertOrder = true, // Maintains the order of inserted entities
    SetOutputIdentity = true,   // Retrieves generated identity values post-insertion
    BatchSize = 5000             // Defines the number of records per batch
});

Advantages

• High Performance: Optimized for large-scale data operations, significantly reducing execution time.
• Flexible Configuration: Offers various settings to fine-tune bulk operations according to specific needs.
• Multi-Provider Support: Compatible with databases like SQL Server and PostgreSQL, among others.

Limitations

• External Dependency: Introduces an additional library into the project.
• Provider-Specific Features: Some features may be limited based on the underlying database provider.

(b) Z.EntityFramework.Extensions

Z.EntityFramework.Extensions is another powerful library provides a comprehensive suite of bulk operations and advanced features for EF Core.

Installation

Add the package via NuGet:

dotnet add package Z.EntityFramework.Extensions

Basic Usage

using Z.EntityFramework.Extensions;

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

    dbContext.BulkInsert(users);
    // For asynchronous operations:
    // await dbContext.BulkInsertAsync(users);
}

Advanced Configuration

Configure bulk operations with additional options:

dbContext.BulkInsert(users, options => 
{
    options.BatchSize = 5000;
    options.SqlBulkCopyOptions = System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity;
});

Advantages

• Extensive Feature Set: Supports bulk inserts, updates, deletes, merges, and more.
• Optimized Performance: Designed for high efficiency and speed in large data operations.
• Robust Support: Comprehensive documentation and support for various database providers.

Limitations

• Commercial Licensing: Requires purchasing a license for full feature access.
• External Dependency: Adds another library to the project ecosystem.

3. Using Raw SQL for Bulk Insert

For scenarios where third-party libraries are unsuitable, using raw SQL provides complete control over the bulk insert process without external dependencies.

Example Implementation

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 object[]
    {
        new SqlParameter("@Name", u.Name),
        new SqlParameter("@Email", u.Email),
        new SqlParameter("@CreatedAt", u.CreatedAt)
    }).ToList();

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

Advantages

• Full Control: Complete authority over the SQL commands and execution process.
• No Additional Dependencies: Eliminates the need for external libraries, keeping the project lightweight.

Limitations

• Manual Management: Requires explicit handling of SQL commands and parameters.
• Risk of SQL Injection: Necessitates careful parameterization to prevent security vulnerabilities.
• Performance Constraints: May not match the efficiency of specialized bulk libraries, especially for extremely large datasets.

Best Practices for Bulk Insert

Implementing bulk insert operations effectively requires adherence to certain best practices to ensure performance optimization and data integrity.

1. Disable Change Tracking During Bulk Operations

Change tracking can introduce significant overhead. Disabling it during bulk inserts can enhance performance:

dbContext.ChangeTracker.AutoDetectChangesEnabled = false;

// Perform bulk insert
await dbContext.BulkInsertAsync(users);

// Re-enable change tracking if needed
dbContext.ChangeTracker.AutoDetectChangesEnabled = true;

2. Utilize Transaction Scopes

Ensuring atomicity in bulk operations is crucial. Wrapping bulk inserts within a transaction guarantees that either all records are inserted successfully or none are, maintaining data consistency.

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

3. Batch Inserts for Extremely Large Datasets

Handling very large datasets in manageable batches prevents overloading the database and excessive memory consumption.

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

4. Optimize Database Schema

Efficient database schema design can significantly impact bulk insert performance:

• Index Management: Temporarily disabling or dropping non-essential indexes before bulk inserts and rebuilding them afterward can speed up the insertion process.
• Constraint Minimization: Reducing constraints that may hinder bulk operations can improve performance, provided data integrity is maintained.

5. Server and Network Optimization

Ensuring that the database server and network infrastructure can handle bulk operations without becoming bottlenecks is essential:

• Proximity: Hosting the application and database servers in close network proximity reduces latency.
• Network Bandwidth: Adequate bandwidth ensures that large data transfers occur swiftly without delays.

6. Testing and Benchmarking

Before deploying bulk insert operations to production, conduct thorough testing and benchmarking in staging environments to:

• Measure Performance: Compare execution times and resource usage against default methods.
• Ensure Data Integrity: Verify that all records are inserted correctly without loss or duplication.
• Identify Bottlenecks: Detect and address any performance issues that arise during bulk operations.

Advanced Examples

Bulk Insert with EFCore.BulkExtensions

Model Definition

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
}

DbContext Configuration

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Replace with your actual connection string
        optionsBuilder.UseSqlServer("Server=.;Database=MyDatabase;Trusted_Connection=True;");
    }
}

Implementation

using EFCore.BulkExtensions;

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

    // Disable change tracking for better performance
    dbContext.ChangeTracker.AutoDetectChangesEnabled = false;
    
    // Execute bulk insert with configuration
    await dbContext.BulkInsertAsync(users, new BulkConfig
    {
        SetOutputIdentity = true,
        BatchSize = 5000
    });
    
    // Re-enable change tracking if necessary
    dbContext.ChangeTracker.AutoDetectChangesEnabled = true;
}

Bulk Insert with Z.EntityFramework.Extensions

Implementation

using Z.EntityFramework.Extensions;

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

    await dbContext.BulkInsertAsync(users, options =>
    {
        options.BatchSize = 5000;
        options.SqlBulkCopyOptions = System.Data.SqlClient.SqlBulkCopyOptions.KeepIdentity;
    });
}

Bulk Insert Using Raw SQL

Implementation

public async Task BulkInsertUsersWithRawSqlAsync(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 object[]
    {
        new SqlParameter("@Name", u.Name),
        new SqlParameter("@Email", u.Email),
        new SqlParameter("@CreatedAt", u.CreatedAt)
    }).ToList();

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

Additional Scenarios

Beyond bulk inserts, EFCore.BulkExtensions and Z.EntityFramework.Extensions support various other bulk operations, including:

• Bulk Update: Efficiently updating multiple records in a single operation.
• Bulk Delete: Removing large numbers of records quickly.
• Bulk Merge (Upsert): Combining insert and update functionalities to synchronize data between datasets seamlessly.

These operations can further optimize database interactions, reducing the need for iterative and repetitive queries.

Conclusion

Entity Framework Core offers robust capabilities for interacting with databases using .NET objects. However, when it comes to bulk operations like inserting large volumes of data, the default methods can lead to performance inefficiencies. By leveraging third-party libraries such as EFCore.BulkExtensions and Z.EntityFramework.Extensions, or by implementing raw SQL approaches, developers can significantly enhance the performance and scalability of their applications.

Key Takeaways

• Default Methods Limitations: While simple to use, AddRange and SaveChangesAsync are not optimized for large datasets.
• Third-Party Libraries: EFCore.BulkExtensions and Z.EntityFramework.Extensions provide optimized, high-performance bulk operations with varying feature sets and licensing models.
• Raw SQL Alternative: For complete control and minimal dependencies, raw SQL offers an alternative, though it requires meticulous implementation to ensure security and efficiency.
• Best Practices: Disabling change tracking, using transactions, batching operations, optimizing database schemas, and thorough testing are essential for effective bulk insert operations.

By adopting these strategies, developers can ensure that their EF Core applications handle large-scale data operations efficiently, maintaining high performance and reliability.