N.EntityFrameworkCore.Extensions.MySql 10.0.5.6

N.EntityFrameworkCore.Extensions.MySql

Bulk data extensions for Entity Framework Core — MySQL provider.

Questions, bugs, and feature requests: open an issue.

Installation

dotnet add package N.EntityFrameworkCore.Extensions.MySql

The meta-package N.EntityFrameworkCore.Extensions also installs this provider along with SqlServer, PostgreSql, Sqlite, and Oracle.

Setup

Call SetupEfCoreExtensions() in your DbContext.OnConfiguring override:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseMySql("your-connection-string", ServerVersion.AutoDetect("your-connection-string"))
        .SetupEfCoreExtensions();
}

For best bulk-insert performance, enable MySQL local infile support on both the client and server. The provider uses MySqlBulkCopy when possible and falls back to batched inserts when local infile is unavailable.

optionsBuilder
    .UseMySql("your-connection-string;AllowLoadLocalInfile=true", ServerVersion.AutoDetect("your-connection-string"))
    .SetupEfCoreExtensions();

The MySQL server must also have local_infile=1 enabled.

Usage

Common examples below. For options tables, result objects, and the full API reference, see the main README.

BulkInsert

var orders = new List<Order>();
for (int i = 0; i < 10000; i++)
    orders.Add(new Order { OrderDate = DateTime.UtcNow, TotalPrice = 2.99 });

dbContext.BulkInsert(orders);
await dbContext.BulkInsertAsync(orders);

With options:

dbContext.BulkInsert(orders, options =>
{
    options.BatchSize = 5000;
    options.KeepIdentity = true;
    options.InsertIfNotExists = true;
});

Progress reporting:

dbContext.BulkInsert(orders, options =>
{
    options.NotifyAfter = 1000;
    options.RowsCopied = e => Console.WriteLine($"Copied {e.RowsCopied} rows");
});

From an async stream (batches internally; default BatchSize is 10000 when unset):

await dbContext.BulkInsertAsync(ReadOrdersAsync());

async IAsyncEnumerable<Order> ReadOrdersAsync()
{
    // yield entities from a stream, channel, or remote source
    yield break;
}

BulkDelete

var orders = dbContext.Orders.Where(o => o.TotalPrice < 5.35M).ToList();

dbContext.BulkDelete(orders);
await dbContext.BulkDeleteAsync(orders);

Soft delete (updates columns from entity values instead of a physical delete):

foreach (var order in orders)
    order.IsDeleted = true;

dbContext.BulkDelete(orders, options =>
{
    options.SoftDeleteColumns = x => new { x.IsDeleted };
});

BulkFetch

var stubs = new List<Product>
{
    new() { Id = 10001 },
    new() { Id = 10002 },
    new() { Id = 10003 },
};
var products = dbContext.Products.BulkFetch(stubs).ToList();
await dbContext.Products.BulkFetchAsync(stubs);

Attach fetched entities to the change tracker:

var products = dbContext.Products
    .BulkFetch(stubs, options => { options.AttachToContext = true; })
    .ToList();

BulkUpdate

var products = dbContext.Products.Where(o => o.Price < 5.35M).ToList();
foreach (var product in products) product.Price = 6M;

dbContext.BulkUpdate(products);
await dbContext.BulkUpdateAsync(products);

Update only specific columns:

dbContext.BulkUpdate(products, options =>
{
    options.InputColumns = o => new { o.Price };
});

BulkMerge (Upsert)

BulkMergeResult<Product> result = dbContext.BulkMerge(products);
Console.WriteLine($"Inserted: {result.RowsInserted}, Updated: {result.RowsUpdated}");

With a custom match condition:

dbContext.BulkMerge(products, options =>
{
    options.MergeOnCondition = (s, t) => s.Id == t.Id;
    options.IgnoreColumnsOnInsert = o => new { o.CreatedDate };
    options.IgnoreColumnsOnUpdate = o => new { o.CreatedDate };
});

Use UseNativeUpsert for INSERT ... ON DUPLICATE KEY UPDATE on the primary key (BulkMerge only; custom MergeOnCondition is not applied):

dbContext.BulkMerge(products, options =>
{
    options.UseNativeUpsert = true;
});

BulkSync

BulkSyncResult<Product> result = dbContext.BulkSync(products);
Console.WriteLine($"Inserted: {result.RowsInserted}, Updated: {result.RowsUpdated}, Deleted: {result.RowsDeleted}");

Soft-delete unmatched rows instead of removing them:

dbContext.BulkSync(orders, options =>
{
    options.SoftDeleteColumns = x => new { IsDeleted = true };
});

BulkSaveChanges

dbContext.Orders.AddRange(orders);
dbContext.BulkSaveChanges();
await dbContext.BulkSaveChangesAsync();

Fetch

dbContext.Products.Where(o => o.Price < 5.35M).Fetch(result =>
{
    Console.WriteLine($"Batch {result.Batch}: {result.Results.Count} rows");
},
new FetchOptions<Product> { BatchSize = 1000 });

await dbContext.Products.Where(o => o.Price < 5.35M).FetchAsync(async result =>
{
    await ProcessBatchAsync(result.Results);
},
new FetchOptions<Product> { BatchSize = 1000 });

DeleteFromQuery

dbContext.Products.Where(x => x.Price < 5.35M).DeleteFromQuery();
await dbContext.Products.Where(x => x.Price < 5.35M).DeleteFromQueryAsync();

InsertFromQuery

dbContext.Products
    .Where(x => x.Price < 10M)
    .InsertFromQuery("ProductsUnderTen", o => new { o.Id, o.Price });

Note: Creating the destination table uses MySQL DDL, which auto-commits. If the table does not exist and a user transaction is open, InsertFromQuery throws InvalidOperationException. Inserting into an existing table inside a transaction is supported.

UpdateFromQuery

dbContext.Products
    .Where(x => x.Price == 5.35M)
    .UpdateFromQuery(o => new Product { Price = 5.75M });

QueryToCsvFile

QueryToFileResult result = dbContext.Products
    .Where(x => x.Price > 5M)
    .QueryToCsvFile("products.csv");

SqlQueryToCsvFile

dbContext.Database.SqlQueryToCsvFile(
    "output.csv",
    "SELECT Id, Name, Price FROM Products WHERE Price > @p0",
    5M);

QueryToJsonFile

dbContext.Products.Where(x => x.Price > 5M).QueryToJsonFile("products.json");
await dbContext.Products.QueryToJsonFileAsync("products.json");

SqlQueryToJsonFile

dbContext.Database.SqlQueryToJsonFile("output.json", "SELECT Id, Name FROM Products WHERE Price > @p0", 5M);

QueryToJsonLinesFile

dbContext.Products.Where(x => x.Price > 5M).QueryToJsonLinesFile("products.jsonl");

SqlQueryToJsonLinesFile

dbContext.Database.SqlQueryToJsonLinesFile("output.jsonl", "SELECT Id, Name FROM Products WHERE Price > @p0", 5M);

Clear / Truncate

dbContext.Orders.Clear();
await dbContext.Orders.ClearAsync();

dbContext.Orders.Truncate();
await dbContext.Orders.TruncateAsync();

FromSqlQuery

var sqlQuery = dbContext.Database.FromSqlQuery(
    "SELECT * FROM Products WHERE Price > @p0", 5M);
int count = sqlQuery.Count();

Transactions

using var transaction = dbContext.Database.BeginTransaction();
try
{
    dbContext.BulkInsert(orders);
    dbContext.BulkUpdate(products);
    transaction.Commit();
}
catch
{
    transaction.Rollback();
    throw;
}

For full documentation including all options, result objects, and the complete API reference, see the main README.