Handling Large Data Imports

Use bulk copy techniques to bypass ORM overhead for initial data loading.

Handling Large Data Imports with Bulk Copy

In this lesson, you will learn how to bypass the overhead of EF Core’s change tracking and state management to perform high-speed data insertions during large-scale imports.

When you use the standard dbContext.AddRange() method for importing tens of thousands of records, EF Core performs significant work: it tracks every object, computes relationships, and generates individual INSERT statements. While this is great for day-to-day business logic, it becomes a performance bottleneck when loading massive datasets.

Bypassing the ORM Overhead

To achieve maximum throughput, we step outside the ORM's "tracked" ecosystem and use the underlying database driver’s Bulk Copy capabilities. In the SQL Server ecosystem, this is handled by the SqlBulkCopy class. By streaming data directly into the database, we eliminate the per-row overhead of the EF Core context.

Here is how you can implement a high-speed load using your existing DbContext connection:

public void BulkInsertData(MyDbContext context, DataTable dataTable)
{
    // Ensure we use the raw connection from the context
    using var connection = (SqlConnection)context.Database.GetDbConnection();
    connection.Open();

    using var bulkCopy = new SqlBulkCopy(connection);
    bulkCopy.DestinationTableName = "YourTableName";
    
    // Map your DataTable columns to database columns
    bulkCopy.ColumnMappings.Add("SourceColumn", "TargetColumn");

    bulkCopy.WriteToServer(dataTable);
}

When to Use This Approach

You should reach for this technique during initial database seeding, nightly batch imports of external telemetry, or data migration tasks where you are processing millions of rows.

Because SqlBulkCopy operates at the database driver level, it is not subject to EF Core’s business logic or validation rules. This means you must ensure your data is clean and matches the database schema constraints before you initiate the transfer. Once the operation completes, your DbContext will be unaware of these new records, so you may need to clear your local cache or rely on subsequent queries to reflect the new state of the database.

Key Takeaway: To handle large data imports efficiently, bypass EF Core’s change tracker by utilizing native bulk copy APIs to stream data directly into the database.