This project was built as an extension to add bulk operations functionality to the Entity Framework (EF6 and EFCore).
It works as extension methods of the DbContext class and is very simple to use. The library uses the same connection your context created and if the context's database have a CurrentTransaction it will use it, otherwise it creates an internal one for the scope of the operation.
It relies on the SqlBulkCopy class to perform all the operations, because of that, it can't handle navigation properties and will not persist relationships between entities, but there is a workaround for that if the foreign keys are being explicitly mapped in your model classes. See the workaround in the examples below.
- Bulk insert, update, insert or update, delete operations;
- Support context transaction (Uses the same connection and transaction of the context);
- If the context has no transaction it creates and uses an internal one for safety;
- Support tables with AutoIncrement key, not auto increment keys and composite keys;
- Output database generated Ids;
- Output database computed columns;
- Support Table-Per-Hierarchy(TPH);
- For EF6 you can use it with .NetFramewok 4.5+;
- For EFCore you can use it with .NetFramewok 4.5.1+ or .NetCore1.0+;
You can see the release notes on the Releases page
You can install it using the nuget package for your EF version:
- For EF6: EntityFramework.BulkExtensions
- For EFCore: EntityFramework.BulkExtensions.EFCore
You just need to call the methods bellow for the feature you want to use passing the collection of entities to perform the operation.
context.BulkInsert(entities);context.BulkUpdate(entities);context.BulkInsertOrUpdate(entities);context.BulkDelete(entities);//Generated Ids are populated by adding the optional parammetercontext.BulkInsert(entities,InsertOptions.OutputIdentity);context.BulkInsertOrUpdate(entities,InsertOptions.OutputIdentity);//Computed columns are populated by adding the optional parammetercontext.BulkInsert(entityList,InsertOptions.OutputComputed);context.BulkInsertOrUpdate(entityList,InsertOptions.OutputComputed);context.BulkUpdate(entityList,UpdateOptions.OutputComputed);//It is possible to combine optionscontext.BulkInsert(entityList,InsertOptions.OutputIdentity|InsertOptions.OutputComputed);context.BulkInsertOrUpdate(entityList,InsertOptions.OutputIdentity|InsertOptions.OutputComputed);There is two ways of using this method. By only using the list as parameters for this extension method it will perform a standard SqlBulkCopy operation, witch will not return the Ids of the inserted entities because of a limitation of the SqlBulkCopy class.
By also selecting the option 'InsertOptions.OutputIdentity' as the second parameter, the method will fill the generated Ids for the entities inserted(If they are database generated. e.g. auto increment), using temporary tables to output and select the generated Ids under the hood. See the exemples below:
usingEntityFramework.BulkExtensionsvar entityList =newList<MyEntity>();entityList.Add(newMyEntity());entityList.Add(newMyEntity());entityList.Add(newMyEntity());//Bulk insert extension methodcontext.BulkInsert(entityList);/* But if you want the generated ids you can call the function as below */context.BulkInsert(entityList,InsertOptions.OutputIdentity);entityList.First().Id//Should have the id generated by the database./* The ids generated by the database will be set for every inserted item in the entities collection */You can explicitly set the foreign keys of your entity and insert it. See the example below.
usingEntityFramework.BulkExtensionsvar role =context.Set<Roles>().Single(entity =>entity.Name=="Admin").ToList();varentityList=newList<User>();entityList.Add(newUser{RoleId=role.Id});//Set the role id on the newly created userentityList.Add(newUser{RoleId=role.Id});entityList.Add(newUser{RoleId=role.Id});entityList.Add(newUser{RoleId=role.Id});entityList.Add(newUser{RoleId=role.Id});entityList.Add(newUser{RoleId=role.Id});//Bulk insert extension methodcontext.BulkInsert(entityList);/* By explicitly setting the foreing key the relationship will be persisted in the database. */usingEntityFramework.BulkExtensionsRandom rnd =newRandom();//Read some entities from database.varentityList=context.Set<MyEntity>().Where(entity =>entity.Owner=="Steve").ToList();foreach(varentityinentityList){//Replace the old value with some random new value.entity.Value=rnd.Next(1000);}//Bulk update extension methodcontext.BulkUpdate(entityList);/* Under the hood, this operation will create a mirror table of your entity's table, bulk insert the updated entities using the SqlBulkCopy class, use the MERGE sql command to transfer the data to the original entity table using the primary keys to match entries and then drop the mirror table. The original course of action of the entity framework would be create an UPDATE command for each entity, wich suffers a big performance hit with an increased number of entries to update. */usingEntityFramework.BulkExtensionsRandom rnd =newRandom();//Read some entities from database.varentityList=context.Set<MyEntity>().Where(entity =>entity.Owner=="Steve").ToList();foreach(varentityinentityList){//Replace the old value with some random new value.entity.Value=rnd.Next(1000);}//Add some new entities.for(vari=0;i<10;i++){entityList.Add(newMyEntity{Value=rnd.Next(1000);});}//Bulk update extension methodcontext.BulkInsertOrUpdate(entityList);/* Also, if you want the generated ids for the newly added entitites you can use the code below*/context.BulkInsertOrUpdate(entityList,InsertOptions.OutputIdentity);/* Under the hood, this operation will create a mirror table of your entity's table, bulk insert the updated entities using the SqlBulkCopy class, use the MERGE sql command to transfer the data to the original entity table using the primary keys to match entries, the ones not matched are new and will be inserted, and then drop the mirror table. The original course of action of the entity framework would be create an UPDATE command for each entity, wich suffers a big performance hit with an increased number of entries to update. */usingEntityFramework.BulkExtensions//Read some entities from database.var entityList =context.Set<MyEntity>().Where(entity =>entity.Owner=="Steve").toList();//Bulk delete extension methodcontext.BulkDelete(entityList);/* This operation will delete all the entities in the list from the database. */It is possible to output the values of computed columns in the same way the generated Ids are outputed. There is a new option InsertOptions.OutputComputed for the BulkInsert and BulkInsertOrUpdate operations and a new UpdateOptions.OutputComputed option for BulkUpdate. This option can be combined with the InsertOptions.OutputIdentity.
usingEntityFramework.BulkExtensionsvar entityList =newList<MyEntity>();entityList.Add(newMyEntity());entityList.Add(newMyEntity());entityList.Add(newMyEntity());//Bulk insert extension methodcontext.BulkInsert(entityList,InsertOptions.OutputComputed);//Accessing a computed property will return the generated valuevarfirstComputedValue=entityList.First().MyComputedProperty;/* The computed values generated by the database will be set for every inserted item in the entities collection *///You can also output computed values on BulkInsertOrUpdatecontext.BulkInsertOrUpdate(entityList,InsertOptions.OutputComputed);//And you can output computed values on BulkUpdate too.context.BulkUpdate(entityList,UpdateOptions.OutputComputed);//Combination with InsertOptions.OutputIdentity works as well.context.BulkInsert(entityList,InsertOptions.OutputIdentity|InsertOptions.OutputComputed);//ORcontext.BulkInsertOrUpdate(entityList,InsertOptions.OutputIdentity|InsertOptions.OutputComputed);/*This will output the generated indentity and the computed columns.The work with transactions is pretty straightforward and flexible. If you are performing multiple operations on the context using a transaction it is safe to use any bulk operation, the operations use the transaction of the context to perform database manipulation.
usingEntityFramework.BulkExtensions//Begin a transaction on your context.using(vartransaction=context.Database.BeginTransaction()){varrnd=newRandom();//Read some entities from database.varupdateList=context.Set<MyEntity>().Where(entity =>entity.Owner=="Steve").ToList();foreach(varentityinupdateList){//Replace the old value with some random new value.entity.Value=rnd.Next(1000);entity.OtherProperty="some random string";}//Bulk update extension methodcontext.BulkUpdate(updateList);// 1st operation//Read other entities from database.vardeleteList=context.Set<MyEntity>().Where(entity =>entity.Owner=="Bob").toList();//Bulk delete extension methodcontext.BulkDelete(deleteList);// 2nd operation//Commit the transactiontransaction.Commit();}/* The two operations will run on the same transaction, if something goes worng the rollback wouldundo the changes made by the two bulk operations.*/If you are not using transaction, each bulk operations creates a transaction for the scope of the operation.
usingEntityFramework.BulkExtensionsvar rnd =newRandom();//Read some entities from database.varupdateList=context.Set<MyEntity>().Where(entity =>entity.Owner=="Steve").ToList();foreach(varentityinupdateList){//Replace the old value with some random new value.entity.Value=rnd.Next(1000);entity.OtherProperty="some random string";}//Bulk update extension methodcontext.BulkUpdate(updateList);// 1st operation//Read other entities from database.vardeleteList=context.Set<MyEntity>().Where(entity =>entity.Owner=="Bob").toList();//Bulk delete extension methodcontext.BulkDelete(deleteList);// 2nd operation/* Each operations will run on it's own transaction. For example, if something goes worng with the delete operation the changes made by it would be undone but the changes made by the update before would persist.*/This library is based on the SqlBulkTools by Greg Taylor.