ORM-EFCore.BulkExtensions
[删除（380066935@qq.com或微信通知）]
EFCore.BulkExtensions
EntityFrameworkCore extensions:
-Bulk operations (Insert, Update, Delete, Read, Upsert, Sync, SaveChanges)
-Batch ops (Delete, Update) and Truncate.
Library is Lightweight and very Efficient, having all mostly used CRUD operation.
Was selected in top 20 EF Core Extensions recommended by Microsoft.
Latest version is using EF Core 6 and targeting .Net 6.
At the moment supports Microsoft SQLServer(2012+) or SqlAzure, PostgreSQL(9.5+) and SQLite.
-SQLServer under the hood uses SqlBulkCopy for Insert, for Update/Delete combines BulkInsert with raw Sql MERGE.
-PostgreSQL is using COPY BINARY combined with ON CONFLICT for Update (supported from v6+).
-For SQLite there is no Copy tool, instead library uses plain SQL combined with UPSERT.
Bulk Tests can not have UseInMemoryDb because InMemoryProvider does not support Relational-specific methods.
Instead Test options are SqlServer(Developer or Express), LocalDb(if alongside Developer v.), or for other adapters PostgreSQL/SQLite.
Usage
It's pretty simple and straightforward.
Bulk Extensions are made on DbContext and are used with entities List (supported both regular and Async methods):
context.BulkInsert(entities); context.BulkInsertAsync(entities);
context.BulkInsertOrUpdate(entities); context.BulkInsertOrUpdateAsync(entities); // Upsert
context.BulkInsertOrUpdateOrDelete(entities); context.BulkInsertOrUpdateOrDeleteAsync(entities); // Sync
context.BulkUpdate(entities); context.BulkUpdateAsync(entities);
context.BulkDelete(entities); context.BulkDeleteAsync(entities);
context.BulkRead(entities); context.BulkReadAsync(entities);
context.BulkSaveChanges(); context.BulkSaveChangesAsync(); // >= v6
-SQLite requires package: SQLitePCLRaw.bundle_e_sqlite3 with call to SQLitePCL.Batteries.Init()
Batch Extensions are made on IQueryable DbSet and can be used as in the following code segment.
They are done as pure sql and no check is done whether some are prior loaded in memory and are being Tracked.
(updateColumns is optional param in which PropertyNames added explicitly when need update to it's default value)
// Delete
context.Items.Where(a => a.ItemId > 500).BatchDelete();
context.Items.Where(a => a.ItemId > 500).BatchDeleteAsync();
// Update (using Expression arg.) supports Increment/Decrement
context.Items.Where(a => a.ItemId <= 500).BatchUpdate(a => new Item { Quantity = a.Quantity + 100 });
context.Items.Where(a => a.ItemId <= 500).BatchUpdateAsync(a => new Item { Quantity = a.Quantity + 100 });
// can be as value '+100' or as variable '+incrementStep' (int incrementStep = 100;)
// Update (via simple object)
context.Items.Where(a => a.ItemId <= 500).BatchUpdate(new Item { Description = "Updated" });
context.Items.Where(a => a.ItemId <= 500).BatchUpdateAsync(new Item { Description = "Updated" });
// Update (via simple object) - requires additional Argument for setting to Property default value
var updateCols = new List<string> { nameof(Item.Quantity) }; //Update 'Quantity' to default value('0'-zero)
var q = context.Items.Where(a => a.ItemId <= 500);
int affected = q.BatchUpdate(new Item { Description="Updated" }, updateCols); //result assigned to variable
// Truncate
context.Truncate<Entity>();
context.TruncateAsync<Entity>();
Bulk info
If Windows Authentication is used then in ConnectionString there should be Trusted_Connection=True; because Sql credentials are required to stay in connection.
When used directly each of these operations are separate transactions and are automatically committed.
And if we need multiple operations in single procedure then explicit transaction should be used, for example:
using (var transaction = context.Database.BeginTransaction())
{
context.BulkInsert(entities1List);
context.BulkInsert(entities2List);
transaction.Commit();
}
BulkInsertOrUpdate method can be used when there is need for both operations but in one connection to database.
It makes Update when PK(PrimaryKey) is matched, otherwise does Insert.
BulkInsertOrUpdateOrDelete effectively synchronizes table rows with input data.
Those in Db that are not found in the list will be deleted.
Partial Sync can be done on table subset using expression set on config with method:
bulkConfig.SetSynchronizeFilter<Item>(a => a.Quantity > 0);
Not supported for SQLite(Lite has only UPSERT statement) nor currently for PostgreSQL. Way to achieve there sync functionality is to Select or BulkRead existing data from DB, split list into sublists and call separately Bulk methods for BulkInsertOrUpdate and Delete.
BulkRead does SELECT and JOIN based on one or more Unique columns that are specified in Config UpdateByProperties.
More info in the Example at the bottom.
SaveChanges uses Change Tracker to find all modified(CUD) entities and call proper BulkOperations for each table.
Because it needs tracking it is slower then pure BulkOps but stil much faster then regular SaveChanges.
With config OnSaveChangesSetFK setting FKs can be controled depending on whether PKs are generated in Db or in memory.
Support for this method was added in version 6 of the library.
Before calling this method newly created should be added into Range:
context.Items.AddRange(newEntities); // if newEntities is parent list it can have child sublists
context.BulkSaveChanges();
Practical general usage could be made in a way to override regular SaveChanges and if any list of Modified entities entries is greater then say 1000 to redirect to Bulk version.
Note: Bulk ops have optional argument Type type that can be set to type of Entity if list has dynamic runtime objects or is inhereted from Entity class.
BulkConfig arguments
Bulk methods can have optional argument BulkConfig with properties (bool, int, object, List):
PROPERTY : DEFAULTvalue
----------------------- PropertiesToInclude: null,
PreserveInsertOrder: true, PropertiesToIncludeOnCompare: null,
SetOutputIdentity: false, PropertiesToIncludeOnUpdate: null,
BatchSize: 2000, PropertiesToExclude: null,
NotifyAfter: null, PropertiesToExcludeOnCompare: null,
BulkCopyTimeout: null, PropertiesToExcludeOnUpdate: null,
EnableStreaming: false, UpdateByProperties: null,
UseTempDB: false, EnableShadowProperties: false,
UniqueTableNameTempDb: true, IncludeGraph: false,
CustomDestinationTableName: null, OmitClauseExistsExcept: false,
CustomSourceTableName: null, DoNotUpdateIfTimeStampChanged: false,
CustomSourceDestinationMappingColumns: null, SRID: 4326,
TrackingEntities: false, DateTime2PrecisionForceRound: false,
WithHoldlock: true, TemporalColumns: { "PeriodStart", "PeriodEnd" },
CalculateStats: false, EnableShadowProperties: false,
SqlBulkCopyOptions: Default, OnSaveChangesSetFK: true,
SqlBulkCopyColumnOrderHints: null,
--------------------------------------------------------------------------------------
METHOD: SetSynchronizeFilter<T>
If we want to change defaults, BulkConfig should be added explicitly with one or more bool properties set to true, and/or int props like BatchSize to different number.
Config also has DelegateFunc for setting Underlying-Connection/Transaction, e.g. in UnderlyingTest.
When doing update we can chose to exclude one or more properties by adding their names into PropertiesToExclude, or if we need to update less then half column then PropertiesToInclude can be used. Setting both Lists are not allowed.
When using the BulkInsert_/OrUpdate methods, you may also specify the PropertiesToIncludeOnCompare and PropertiesToExcludeOnCompare properties. By adding a column name to the PropertiesToExcludeOnCompare, will allow it to be inserted and updated but will not update the row if any of the other columns in that row did not change. For example, if you are importing bulk data and want to keep an internal CreateDate or UpdateDate, you add those columns to the PropertiesToExcludeOnCompare.
Another option that may be used in the same scenario are the PropertiesToIncludeOnUpdate and PropertiesToExcludeOnUpdate properties. These properties will allow you to specify insert-only columns such as CreateDate and CreatedBy.
If we want Insert only new and skip existing ones in Db (Insert_if_not_Exist) then use BulkInsertOrUpdate with config PropertiesToIncludeOnUpdate = new List<string> { "" }
Additionaly there is UpdateByProperties for specifying custom properties, by which we want update to be done.
When setting multiple props in UpdateByProps then match done by columns combined, like unique constrain based on those cols.
Using UpdateByProperties while also having Identity column requires that Id property be Excluded.
Also with PostgreSQL when matching is done it requires UniqueIndex so for custom UpdateByProperties that do not have un.ind., it is temporarily created in which case method can not be in transation (throws: current transaction is aborted; CREATE INDEX CONCURRENTLY cannot run inside a transaction block).
If NotifyAfter is not set it will have same value as BatchSize while BulkCopyTimeout when not set has SqlBulkCopy default which is 30 seconds and if set to 0 it indicates no limit.
SetOutputIdentity have purpose only when PK has Identity (usually int type with AutoIncrement), while if PK is Guid(sequential) created in Application there is no need for them.
Also Tables with Composite Keys have no Identity column so no functionality for them in that case either.
var bulkConfig = new BulkConfig { SetOutputIdentity = true, BatchSize = 4000 };
context.BulkInsert(entities, bulkConfig);
context.BulkInsertOrUpdate(entities, new BulkConfig { SetOutputIdentity = true });
context.BulkInsertOrUpdate(entities, b => b.SetOutputIdentity = true); // e.g. BulkConfig with Action arg.
PreserveInsertOrder is true by default and makes sure that entites are inserted to Db as ordered in entitiesList.
When table has Identity column (int autoincrement) with 0 values in list they will temporary be automatically changed from 0s into range -N:-1.
Or it can be manually set with proper values for order (Negative values used to skip conflict with existing ones in Db).
Here single Id value itself doesn't matter, db will change it to next in sequence, what matters is their mutual relationship for sorting.
Insertion order is implemented with TOP in conjuction with ORDER BY. stackoverflow:merge-into-insertion-order.
This config should remain true when SetOutputIdentity is set to true on Entity containing NotMapped Property. issues/76
When using SetOutputIdentity Id values will be updated to new ones from database.
With BulkInsertOrUpdate for those that will be updated it has to match with Id column, or other unique column(s) if using UpdateByProperties in which case orderBy is done with those props instead of ID.
For Sqlite combination of BulkInsertOrUpdate and IdentityId automatic set will not work properly since it does not have full MERGE capabilities like SqlServer. Instead list can be split into 2 lists, and call separately BulkInsert and BulkUpdate.
SetOutputIdentity is useful when BulkInsert is done to multiple related tables, that have Identity column.
After Insert is done to first table, we need Id-s (if using Option 1) that were generated in Db because they are FK(ForeignKey) in second table.
It is implemented with OUTPUT as part of MERGE Query, so in this case even the Insert is not done directly to TargetTable but to TempTable and then Merged with TargetTable.
When used Id-s will be updated in entitiesList, and if PreserveInsertOrder is set to false then entitiesList will be cleared and reloaded.
Example of SetOutputIdentity with parent-child FK related tables:
int numberOfEntites = 1000;
var entities = new List<Item>();
var subEntities = new List<ItemHistory>();
for (int i = 1; i <= numberOfEntites; i++)
{
var entity = new Item { Name = $"Name {i}" };
entity.ItemHistories = new List<ItemHistory>()
{
new ItemHistory { Remark = $"Info {i}.1" },
new ItemHistory { Remark = $"Info {i}.2" }
};
entities.Add(entity);
}
// Option 1
using (var transaction = context.Database.BeginTransaction())
{
context.BulkInsert(entities, new BulkConfig { SetOutputIdentity = true });
foreach (var entity in entities) {
foreach (var subEntity in entity.ItemHistories) {
subEntity.ItemId = entity.ItemId; // sets FK to match its linked PK that was generated in DB
}
subEntities.AddRange(entity.ItemHistories);
}
context.BulkInsert(subEntities);
transaction.Commit();
}
// Option 2 using Graph (only for SQL Server)
// - all entities in relationship with main ones in list are BulkInsertUpdated
context.BulkInsert(entities, b => b.IncludeGraph = true);
// Option 3 with BulkSaveChanges() - uses ChangeTracker so little slower then direct Bulk
context.Items.AddRange(entities);
context.BulkSaveChanges();
When CalculateStats set to True the result returned in BulkConfig.StatsInfo (StatsNumber-Inserted/Updated/Deleted).
If used for pure Insert (with Batching) then SetOutputIdentity should also be configured because Merge is required.
TrackingEntities can be set to True if we want to have tracking of entities from BulkRead or if SetOutputIdentity is set.
UseTempDB when set then BulkOperation has to be inside Transaction.
UniqueTableNameTempDb when changed to false temp table name will be only 'Temp' without random numbers.
CustomDestinationTableName can be set with 'TableName' only or with 'Schema.TableName'.
CustomSourceTableName when set enables source data from specified table already in Db, so input list not used and can be empty.
CustomSourceDestinationMappingColumns dict can be set only if CustomSourceTableName is configured and it is used for specifying Source-Destination column names when they are not the same. Example in test DestinationAndSourceTableNameTest.
EnableShadowProperties to add (normal) Shadow Property and persist value. Disables automatic discrimator, use manual method.
IncludeGraph when set all entites that have relations with main ones from the list are also merged into theirs tables.
OmitClauseExistsExcept removes the clause from Merge statement, required when having noncomparable types like XML, and useful when need to active triggers even for same data.
_ Also in some sql collation small and capital letters are considered same (case-insensitive) so for BulkUpdate set it false.
DoNotUpdateIfTimeStampChanged if set checks TimeStamp for Concurrency, ones with conflict will not be updated.
SRID Spatial Reference Identifier - for SQL Server with NetTopologySuite.
DateTime2PrecisionForceRound If dbtype datetime2 has precision less then default 7, example 'datetime2(3)' SqlBulkCopy does Floor instead of Round so when this Property is set then Rounding will be done in memory to make sure inserted values are same as with regular SaveChanges.
TemporalColumns are shadow columns used for Temporal table. Default elements 'PeriodStart' and 'PeriodEnd' can be changed if those columns have custom names.
OnSaveChangesSetFK is used only for BulkSaveChanges. When multiply entries have FK relationship which is Db generated, this set proper value after reading parent PK from Db. IF PK are generated in memory like are some Guid then this can be set to false for better efficiency.
SqlBulkCopyOptions is Enum (only for SqlServer) with [Flags] attribute which enables specifying one or more options:
Default, KeepIdentity, CheckConstraints, TableLock, KeepNulls, FireTriggers, UseInternalTransaction
If need to set Identity PK in memory, Not let DB do the autoincrement, then need to use KeepIdentity:
var bulkConfig = new BulkConfig { SqlBulkCopyOptions = SqlBulkCopyOptions.KeepIdentity };
Useful for example when copying from one Db to another.
SetSynchronizeFilter A method that receives and sets expresion filter on entities to delete when using BulkInsertOrUpdateOrDelete.
Last optional argument is Action progress (Example in EfOperationTest.cs RunInsert() with WriteProgress()).
context.BulkInsert(entitiesList, null, (a) => WriteProgress(a));
Library supports Global Query Filters and Value Conversions as well.
Additionally BatchUpdate and named Property works with EnumToString Conversion.
It can map OwnedTypes, also next are links with info how to achieve NestedOwnedTypes and OwnedInSeparateTable.
Table splitting are somewhat specific but could be configured in way Set TableSplit.
With Computed and Timestamp Columns it will work in a way that they are automatically excluded from Insert. And when combined with SetOutputIdentity they will be Selected.
Spatial types, like Geometry, also supported and if Entity has one, clause EXIST ... EXCEPT is skipped because it's not comparable.
Performance for bulk ops measured with ActivitySources named: 'BulkExecute' (tags: 'operationType', 'entitiesCount')
Bulk Extension methods can be Overridden if required, for example to set AuditInfo.
If having problems with Deadlock there is useful info in issue/46.
TPH inheritance
When having TPH (Table-Per-Hierarchy) inheritance model it can be set in 2 ways.
First is automatically by Convention in which case Discriminator column is not directly in Entity but is Shadow Property.
And second is to explicitly define Discriminator property in Entity and configure it with .HasDiscriminator().
Important remark regarding the first case is that since we can not set directly Discriminator to certain value we need first to add list of entities to DbSet where it will be set and after that we can call Bulk operation. Note that SaveChanges are not called and we could optionally turn off TrackingChanges for performance. Example:
public class Student : Person { ... }
context.Students.AddRange(entities); // adding to Context so that Shadow property 'Discriminator' gets set
context.BulkInsert(entities);
TPT (Table-Per-Type) as of v5 is partially supported.
Read example
When we need to Select from big List of some Unique Prop./Column use BulkRead (JOIN done in Sql) for Efficiency:
// instead of WhereIN which will TimeOut for List with over around 40 K records
var entities = context.Items.Where(a => itemsNames.Contains(a.Name)).AsNoTracking().ToList(); // SQL IN
// or JOIN in Memory that loads entire table
var entities = context.Items.Join(itemsNames, a => a.Name, p => p, (a, p) => a).AsNoTracking().ToList();
// USE
var items = itemsNames.Select(a => new Item { Name = a }).ToList(); // Items list with only Name set
var bulkConfig = new BulkConfig { UpdateByProperties = new List<string> { nameof(Item.Name) } };
context.BulkRead(items, bulkConfig); // Items list will be loaded from Db with data(other properties)