Boosting Entity Framework Performance: When to Use Each Insertion Method

• Use AddRange to add multiple entities at once. EF batches these inserts into fewer SQL statements compared to calling Add for each entity individually.

Batching Saves:

• Call SaveChanges on the DbContext periodically after adding a certain number of entities (e.g., 1000). This reduces database roundtrips and improves performance.

DbContext Pooling:

• Consider creating a pool of DbContext instances and disposing of them after a specific number of inserts. This avoids connection overhead for each insert. (Use libraries like Microsoft.EntityFrameworkCore.DbContextPool for this)

Disabling Features (for large inserts only):

• Change Tracking: EF tracks changes made to entities. For large inserts, this can be resource-intensive. You can temporarily disable it using context.ChangeTracker.AutoDetectChangesEnabled = false;. Remember to re-enable it afterward for regular operations.
• Validation: EF can validate entities before saving. If you trust your data, you can disable validation using context.ChangeTracker.ValidateEntitiesBeforeSaves = false;. Re-enable it for scenarios where data integrity is crucial.
• Proxy Creation: EF creates proxy objects for entities. Disabling proxy creation with context.Configuration.ProxyCreationEnabled = false; can improve performance, but it might affect lazy loading functionalities. Use this cautiously.

SqlBulkCopy (when applicable):

• This is the most performant option for inserting large datasets. It bypasses EF and directly interacts with the database using bulk insert functionalities offered by the specific database engine (e.g., SQL Server Bulk Copy). This requires working directly with SQL commands and data manipulation, but the performance gains can be significant.

using System.Linq;

// ... your entity and DbContext classes

List<MyEntity> entities = // populate with your entities

using (var context = new MyDbContext())
{
  context.MyEntities.AddRange(entities);

  // Save after every 1000 entities
  int batchSize = 1000;
  for (int i = 0; i < entities.Count; i += batchSize)
  {
    context.SaveChanges();
  }
}

DbContext Pooling (using Microsoft.EntityFrameworkCore.DbContextPool):

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;

// ...

var serviceCollection = new ServiceCollection();
serviceCollection.AddDbContextPool<MyDbContext>(options =>
{
  // Configure your DbContext options
});

var serviceProvider = serviceCollection.BuildServiceProvider();

using (var scope = serviceProvider.CreateScope())
{
  var context = scope.ServiceProvider.GetRequiredService<MyDbContext>();
  // Use the context for your inserts
}

Disabling Change Tracking (example):

using System;

// ...

using (var context = new MyDbContext())
{
  context.ChangeTracker.AutoDetectChangesEnabled = false;

  // Add your entities here

  context.ChangeTracker.AutoDetectChangesEnabled = true; // Re-enable
  context.SaveChanges();
}

Important Note: Disabling change tracking and other features is for specific scenarios and should be used with caution. Make sure to re-enable them for regular data access operations.

SqlBulkCopy (example for SQL Server):

using System.Data.SqlClient;

// ...

List<MyEntity> entities = // populate with your entities

using (var connection = new SqlConnection("your connection string"))
{
  connection.Open();

  using (var bulkCopy = new SqlBulkCopy(connection))
  {
    bulkCopy.DestinationTableName = "MyTable";

    // Map entity properties to destination table columns
    bulkCopy.ColumnMappings.Add("Id", "Id");
    bulkCopy.ColumnMappings.Add("Name", "Name");
    // ... add mappings for other properties

    bulkCopy.WriteToServer(entities.Select(e => new { e.Id, e.Name })); // Assuming simple properties
  }
}

Several libraries offer functionalities specifically designed for bulk inserts in EF, often exceeding the performance of built-in methods. Here are two popular options:

Stored Procedures:

For complex data insertion logic or scenarios requiring specific database optimizations, you can leverage stored procedures. You can call these procedures from your EF code, passing the necessary data. This approach keeps your data access logic centralized and potentially improves performance by utilizing database-specific optimizations.

Asynchronous Inserts:

For very large datasets, consider using asynchronous inserts to avoid blocking the main thread. EF Core supports asynchronous methods like SaveChangesAsync to perform inserts in the background. This allows your application to remain responsive while data insertion happens asynchronously.

• Third-party libraries: Offer convenient and performant solutions but might introduce external dependencies.
• Stored Procedures: Provide greater control and optimization but require additional database development effort.
• Asynchronous Inserts: Improve responsiveness for massive datasets but add complexity in managing asynchronous operations.