**If you are using Entity Framework Core v8.0.0+ you can use https://github.com/NorthernLight1/N.EntityFrameworkCore.Extensions
Entity Framework Extensions extends your DbContext with high-performance bulk operations: BulkDelete, BulkFetch, BulkInsert, BulkMerge, BulkSaveChanges, BulkSync, BulkUpdate, Fetch, FromSqlQuery, DeleteFromQuery, InsertFromQuery, UpdateFromQuery, QueryToCsvFile, SqlQueryToCsvFile
Supports: Multiple Schemas, Transaction, Asynchronous Execution, Inheritance Models (Table-Per-Hierarchy, Table-Per-Concrete)
Supports Databases: SQL Server
The latest stable version is available on NuGet.
Install-Package N.EntityFramework.ExtensionsBulkInsert() - Performs a insert operation with a large number of entities
var dbcontext = new MyDbContext();
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);
//Using Options
dbContext.BulkInsert(orders, options =>
{
options.CommandTimeout = 30;
options.BatchSize = 100000;
options.InsertIfNotExists = true;
options.InsertOnCondition = (s, t) => s.ExternalId == t.ExternalId;
options.KeepIdentity = true;
});
BulkDelete() - Performs a delete operation with a large number of entities
var dbcontext = new MyDbContext();
var orders = dbcontext.Orders.Where(o => o.TotalPrice < 5.35M);
dbcontext.BulkDelete(orders);
BulkFetch() - Retrieves entities that are contained in a list
var ids = new List<int> { 10001, 10002, 10003, 10004, 10005 };
var products = dbcontext.Products.BulkFetch(ids, options => { options.JoinOnCondition = (s, t) => s.Id == t.Id; }).ToList();
BulkUpdate() - Performs a update operation with a large number of entities
var dbcontext = new MyDbContext();
var products = dbcontext.Products.Where(o => o.Price < 5.35M);
foreach(var product in products)
{
order.Price = 6M;
}
dbcontext.BulkUpdate(products);
BulkMerge() - Performs a merge operation with a large number of entities
var dbcontext = new MyDbContext();
var products = new List<Product>();
var existingProducts = dbcontext.Products.Where(o => o.Price < 5.35M);
foreach(var product in existingProducts)
{
product.Price = 6M;
}
products.AddRange(existingProducts);
products.Add(new Product { Name="Hat", Price=10.25M });
products.Add(new Product { Name="Shirt", Price=20.95M });
dbcontext.BulkMerge(products);
BulkSaveChanges() - Saves all changes using bulk operations
var dbContext = new MyDbContext();
var orders = new List<Order>();
for(int i=0; i<10000; i++)
{
orders.Add(new Order { Id=-i,OrderDate = DateTime.UtcNow, TotalPrice = 2.99 });
}
dbContext.Orders.AddRange(orders);
dbContext.BulkSaveChanges();
BulkSync() - Performs a sync operation with a large number of entities.
By default any entities that do not exists in the source list will be deleted, but this can be disabled in the options.
var dbcontext = new MyDbContext();
var products = new List<Product>();
var existingProducts = dbcontext.Products.Where(o => o.Id <= 1000);
foreach(var product in existingProducts)
{
product.Price = 6M;
}
products.AddRange(existingProducts);
products.Add(new Product { Name="Hat", Price=10.25M });
products.Add(new Product { Name="Shirt", Price=20.95M });
//All existing products with Id > 1000 will be deleted
dbcontext.BulkSync(products);
Fetch() - Retrieves data in batches.
var dbcontext = new MyDbContext();
var query = dbcontext.Products.Where(o => o.Price < 5.35M);
query.Fetch(result =>
{
batchCount++;
totalCount += result.Results.Count();
},
new FetchOptions { BatchSize = 1000 }
);
dbcontext.BulkUpdate(products);
DeleteFromQuery() - Deletes records from the database using a LINQ query without loading data in the context
var dbcontext = new MyDbContext();
//This will delete all products
dbcontext.Products.DeleteFromQuery()
//This will delete all products that are under $5.35
dbcontext.Products.Where(x => x.Price < 5.35M).DeleteFromQuery()
InsertFromQuery() - Inserts records from the database using a LINQ query without loading data in the context
var dbcontext = new MyDbContext();
//This will take all products priced under $10 from the Products table and
//insert it into the ProductsUnderTen table
dbcontext.Products.Where(x => x.Price < 10M).InsertFromQuery("ProductsUnderTen", o => new { o.Id, o.Price });
UpdateFromQuery() - Updates records from the database using a LINQ query without loading data in the context
var dbcontext = new MyDbContext();
//This will change all products priced at $5.35 to $5.75
dbcontext.Products.Where(x => x.Price == 5.35M).UpdateFromQuery(o => new Product { Price = 5.75M })
Transaction
When using any of the following bulk data operations (BulkDelete, BulkInsert, BulkMerge, BulkSync, BulkUpdate, DeleteFromQuery, InsertFromQuery), if an external transaction exists, then it will be utilized.
var dbcontext = new MyDbContext();
var transaction = context.Database.BeginTransaction();
try
{
dbcontext.BulkInsert(orders);
transaction.Commit();
}
catch
{
transaction.Rollback();
}
| Name | Description |
|---|---|
| BulkDelete | |
| BulkDelete(items) | Bulk delete entities in your database. |
| BulkDelete(items, options) | Bulk delete entities in your database. |
| BulkDeleteAsync(items) | Bulk delete entities asynchronously in your database. |
| BulkDeleteAsync(items, cancellationToken) | Bulk delete entities asynchronously in your database. |
| BulkDeleteAsync(items, options) | Bulk delete entities asynchronously in your database. |
| BulkDeleteAsync(items, options, cancellationToken) | Bulk delete entities asynchronously in your database. |
| BulkFetch | |
| BulkFetch(items) | Retrieve entities that are contained in the items list. |
| BulkFetch(items, options) | Retrieve entities that are contained in the items list. |
| BulkFetchAsync(items) | Retrieve entities that are contained in the items list. |
| BulkFetchAsync(items, options) | Retrieve entities that are contained in the items list. |
| BulkInsert | |
| BulkInsert(items) | Bulk insert entities in your database. |
| BulkInsert(items, options) | Bulk insert entities in your database. |
| BulkInsertAsync(items) | Bulk insert entities asynchronously in your database. |
| BulkInsertAsync(items, cancellationToken) | Bulk insert entities asynchronously in your database. |
| BulkInsertAsync(items, options) | Bulk insert entities asynchronously in your database. |
| BulkInsertAsync(items, options, cancellationToken) | Bulk insert entities asynchronously in your database. |
| BulkInsertOptions | |
| AutoMapOutput | Assigns the ouput of all database generated columns to the entities. Perfomance can be improved by disabling this option. (Default=true) |
| CommandTimeout | Gets or sets the wait time (in seconds) before terminating the attempt. |
| IgnoreColumns | columns that will be excluded. |
| IncludeColumns | columns that will be include. |
| InsertIfNotExists | Inserts data into the target table only if it doesn't already exist. (Default=false) |
| InsertOnCondition | Gets or sets the join condition for inserting data. If this condition is null, then the primary key is used. (Default=null) |
| KeepIdentity | Keeps the identity when inserting data into a table. (Default=false) |
| UsePermanentTable | Uses a permanent table when inserting data. If a table uses Always Encrypt, a permanent table is required. (Default=false) |
| BulkMerge | |
| BulkMerge(items) | Bulk merge entities in your database. |
| BulkMerge(items, options) | Bulk merge entities in your database. |
| BulkMergeAsync(items) | Bulk merge entities asynchronously in your database. |
| BulkMergeAsync(items, cancellationToken) | Bulk merge entities asynchronously in your database. |
| BulkMergeAsync(items, options) | Bulk merge entities asynchronously in your database. |
| BulkMergeAsync(items, options, cancellationToken) | Bulk merge entities asynchronously in your database. |
| BulkSaveChanges | |
| BulkSaveChanges() | Save changes using high-performance bulk operations. Should be used instead of SaveChanges(). |
| BulkSaveChanges( acceptAllChangesOnSave) | Save changes using high-performance bulk operations. Should be used instead of SaveChanges(). |
| BulkSaveChangesAsync() | Save changes using high-performance bulk operations. Should be used instead of SaveChanges(). |
| BulkSaveChangesAsync( acceptAllChangesOnSave, cancellationToken) | Save changes using high-performance bulk operations. Should be used instead of SaveChanges(). |
| BulkSync | |
| BulkSync(items) | Bulk sync entities in your database. |
| BulkSync(items, options) | Bulk sync entities in your database. |
| BulkSyncAsync(items) | Bulk sync entities asynchronously in your database. |
| BulkSyncAsync(items, cancellationToken) | Bulk sync entities asynchronously in your database. |
| BulkSyncAsync(items, options) | Bulk sync entities asynchronously in your database. |
| BulkSyncAsync(items, options, cancellationToken) | Bulk sync entities asynchronously in your database. |
| BulkUpdate | |
| BulkUpdate(items) | Bulk update entities in your database. |
| BulkUpdate(items, options) | Bulk update entities in your database. |
| BulkUpdateAsync(items) | Bulk update entities asynchronously in your database. |
| BulkUpdateAsync(items, cancellationToken) | Bulk update entities asynchronously in your database. |
| BulkUpdateAsync(items, options) | Bulk update entities asynchronously in your database. |
| BulkUpdateAsync(items, options, cancellationToken) | Bulk update entities asynchronously in your database. |
| DeleteFromQuery | |
| DeleteFromQuery() | Deletes all rows from the database using a LINQ query without loading in context |
| DeleteFromQueryAsync() | Deletes all rows from the database using a LINQ query without loading in context using asynchronous task |
| DeleteFromQueryAsync(cancellationToken) | Deletes all rows from the database using a LINQ query without loading in context using asynchronous task |
| InsertFromQuery | |
| InsertFromQuery(tableName, selectExpression) | Insert all rows from the database using a LINQ query without loading in context |
| InsertFromQueryAsync(tableName, selectExpression) | Insert all rows from the database using a LINQ query without loading in context using asynchronous task |
| InsertFromQueryAsync(tableName, selectExpression, cancellationToken) | Insert all rows from the database using a LINQ query without loading in context using asynchronous task |
| UpdateFromQuery | |
| UpdateFromQuery(updateExpression, commandTimeout=null) | Updates all rows from the database using a LINQ query without loading in context |
| UpdateFromQueryAsync(updateExpression, commandTimeout=null) | Updates all rows from the database using a LINQ query without loading in context using asynchronous task |
| UpdateFromQueryAsync(updateExpression, commandTimeout=null, cancellationToken) | Updates all rows from the database using a LINQ query without loading in context using asynchronous task |
| Fetch | |
| Fetch(fetchAction) | Fetch rows in batches from the database using a LINQ query |
| Fetch(fetchAction, options) | Fetch rows in batches from the database using a LINQ query |
| FetchAsync(fetchAction) | Fetch rows asynchronously in batches from the database using a LINQ query |
| FetchAsync(fetchAction, options) | Fetch rows asynchronously in batches from the database using a LINQ query |
| FetchAsync(fetchAction, cancellationToken) | Fetch rows asynchronously in batches from the database using a LINQ query |
| FetchAsync(fetchAction, options, cancellationToken) | Fetch rows asynchronously in batches from the database using a LINQ query |