Efficient Bulk Insert with EF Core: CSV and DataTable Strategies for MySQL and MSSQL

Most developers have written basic bulk‑insert code, but achieving high efficiency for large data sets requires special techniques; this guide demonstrates how to integrate fast bulk‑insert functionality into Entity Framework Core.

Using EF, a simple list of Student entities is created and saved with await _context.Students.AddRangeAsync(list); await _context.SaveChangesAsync(); . While acceptable for a few hundred rows, the method becomes prohibitively slow for tens of thousands, as shown in the test table:

Data Size

Time (s)

10

0.028

10,000

3.929

100,000

31.280

For MySQL, the LOAD DATA command can import a CSV file efficiently. The following method writes the CSV to a temporary file and uses MySqlBulkLoader to load it:

private async Task InsertCsvAsync(string csv, string tableName, List
columns)
{
    var fileName = Path.GetTempFileName();
    await File.WriteAllTextAsync(fileName, csv);
    var conn = _context.Database.GetDbConnection() as MySqlConnection;
    var loader = new MySqlBulkLoader(conn)
    {
        FileName = fileName,
        Local = true,
        LineTerminator = Extension.IsWin() ? "\r\n" : "\n",
        FieldTerminator = ",",
        TableName = tableName,
        FieldQuotationCharacter = '"',
        EscapeCharacter = '"',
        CharacterSet = "UTF8"
    };
    loader.Columns.AddRange(columns);
    await loader.LoadAsync();
}

For SQL Server, SqlBulkCopy is used. The core method opens a connection, maps columns, and writes the DataTable to the server:

public async Task InsertAsync(DataTable table)
{
    if (table == null) throw new ArgumentNullException();
    if (string.IsNullOrEmpty(table.TableName)) throw new ArgumentNullException("DataTable.TableName cannot be null");
    var conn = (SqlConnection)_context.Database.GetDbConnection();
    await conn.OpenAsync();
    using var bulk = new SqlBulkCopy(conn)
    {
        DestinationTableName = table.TableName
    };
    foreach (DataColumn column in table.Columns)
        bulk.ColumnMappings.Add(column.ColumnName, column.ColumnName);
    await bulk.WriteToServerAsync(table);
}

Both databases require the source data to be transformed: MySQL needs a CSV file, while SQL Server needs a DataTable . Helper extension methods are provided to convert entities to CSV strings ( CreateCsv<T> ) and to DataTable ( ToDataTable<T> ), handling nulls, GUID generation for empty string primary keys, date formatting, and proper escaping of commas, quotes, and line breaks.

To make bulk insertion database‑agnostic, a set of interfaces is defined:

public interface ISqlBulk
{
    void Insert(DataTable table);
    Task InsertAsync(DataTable table);
    void Insert
(IEnumerable
enumerable) where T : class;
    Task InsertAsync
(IEnumerable
enumerable) where T : class;
}
public interface IMysqlBulk : ISqlBulk
{
    Task InsertAsync
(string csvPath, string tableName = "") where T : class;
}
public interface ISqlServerBulk : ISqlBulk { }

A concrete SqlBulk class selects the appropriate implementation at runtime based on the EF Core database provider and forwards calls to the chosen bulk service:

public class SqlBulk : ISqlBulk
{
    private readonly ISqlBulk _bulk;
    public SqlBulk(DbContext context, IServiceProvider provider)
    {
        if (context.Database.IsMySql())
            _bulk = provider.GetService
();
        else if (context.Database.IsSqlServer())
            _bulk = provider.GetService
();
    }
    public void Insert(DataTable table) => _bulk.Insert(table);
    public async Task InsertAsync(DataTable table) => await _bulk.InsertAsync(table);
    public void Insert
(IEnumerable
enumerable) where T : class => _bulk.Insert(enumerable);
    public async Task InsertAsync
(IEnumerable
enumerable) where T : class => await _bulk.InsertAsync(enumerable);
}

These services are registered via an extension method on IServiceCollection :

public static IServiceCollection AddBatchDB
(this IServiceCollection services) where T : DbContext
{
    services.TryAddScoped
();
    services.TryAddScoped
();
    services.TryAddScoped
();
    services.AddScoped
();
    return services;
}

Performance tests comparing EF Core's default insert with the custom ISqlBulk implementation show significant speedups, especially for large batches:

Data Size

EF Default (s)

ISqlBulk (s)

10

0.028

0.030

10,000

3.929

1.581

100,000

31.280

15.408

The article concludes with a link to the GitHub repository (https://github.com/fuluteam/FL.DbBulk.git) where the full source code and further documentation are available.