17

Entity Framework can be very slow on mass insert/update/delete operations. Even the often suggested tweaks to turn off AutoDetectChanges and/or ValidateOnSaveEnabled does not always help.

I have come across the Z.EntityFramework.Extensions on NuGet, but it seems to be a commercial product, which will only work for a certain period of time.

https://www.nuget.org/packages/Z.EntityFramework.Extensions/

So far, I really only need BulkInsert(), BulkUpdate() and BulkDelete().

My question is:

Is there any reliable non-commercial library, that does nearly the same as Z.EntityFramework.Extensions?

Thanks for any hints!

Michael
  • 938
  • 1
  • 10
  • 34
  • For batch update and delete you can use https://github.com/loresoft/EntityFramework.Extended. For batch insert - just create new context every 100 or so entities. So create context, insert 100, save changes. Then create new one, insert next 100, save changes etc. Wrap all that in transaction scope. That's not really batch insert but will be much faster than inserting all entities in the same context instance. – Evk Feb 20 '17 at 14:32
  • 3
    ORMs in general are *NOT* suitable for batch operations, much less bulk inserts. It's like using tweezers to transport a truckload of pebbles. You can't cover this up with any extension. The best option for bulk inserts is to use SqlBulkCopy to perform a *real*, minimally logged, streaming bulk insert operation. If you want to perform bulk updates, just use the appopriate UPDATE statement. If you want to *UPSERT* from external sources, import everything into a staging table and use MERGE to update the target table – Panagiotis Kanavos Feb 20 '17 at 14:48
  • In other words, you are looking for something that can convert an unbreliable, unsafe, non-scaleable process to a reliable one. Why not use the reliable, scaleable process from the start? – Panagiotis Kanavos Feb 20 '17 at 14:49
  • @Evk I have tested that method and it is still too slow. – Michael Feb 20 '17 at 14:57
  • You mean inserts are slow? And what about that library you mentioned? How much faster is bulk insert there? – Evk Feb 20 '17 at 14:58
  • @Evk On SQL-Server 5000 inserts or deletes it is about 15-20 seconds. I disabled AutoDetect and AutoValidate and created a new context very 100 items. With the mentioned library it is less then 2 seconds. – Michael Feb 20 '17 at 15:03
  • 2
    Sorry for the late comment, I came up with the same question and now I had found this: - https://github.com/borisdj/EFCore.BulkExtensions with MIT license. With 4.62 million downloads, it has more downloads than the Z.EntityFramework.Extensions meanwhile. – Jana Weschenfelder May 17 '21 at 08:25
  • @JanaWeschenfelder, EFCore.BulkExtensions is no longer free... It costs between $500-$4000 for an enterprise: https://github.com/borisdj/EFCore.BulkExtensions/issues/1079 – Jonathan Magnan Mar 31 '23 at 18:55
  • @Jonathan Magnan I do not work on that project anymore, so it does not matter to me personally anymore. And if I should I have the problem sometime in the future again: EF Core became significantly faster the past years, and then there is still Dapper. Large companies use other stuff in the main today, like Messaging Queues, e.g. RabbitMQ and Apache Kafka e.g. in combination with Elasticsearch. ;-) – Jana Weschenfelder Mar 31 '23 at 19:30

1 Answers1

19

Disclaimer: I'm the owner of Entity Framework Extensions

You are right. This is a commercial product.

Every month, a free trial is available, but you will have to purchase the product for the production environment.

Bulk Insert

For BulkInsert, there are some free alternatives but be careful, they don't support all inheritances & associations and are no longer supported:

Disclaimer: I'm the owner of Entity Framework Plus

For Batch Update && Batch Delete, you can use this library:

// DELETE all users which has been inactive for 2 years
ctx.Users.Where(x => x.LastLoginDate < DateTime.Now.AddYears(-2))
         .Delete();

// UPDATE all users which has been inactive for 2 years
ctx.Users.Where(x => x.LastLoginDate < DateTime.Now.AddYears(-2))
         .Update(x => new User() { IsSoftDeleted = 1 });
Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
  • Thank you, Jonathan. I will mark this as a valid answer, because I think, as the owner of such a product, you have a good overview on other competitors. Really sad, that there is no insert in EF Plus, but I guess there has to be some distinction to the commercial product. – Michael Feb 20 '17 at 15:09
  • 3
    Unfortunately, we tried to offer some open source features, but this business model just cannot work. So yes, we need some distinction between both libraries. Open source/free projects most of the time fail at being supported after a time because the cost/time/complexity keep being dramatically increased as more users use it. By example, last year, this free library took us more time to support than our other libraries combined! – Jonathan Magnan Feb 20 '17 at 19:47
  • Hi Michael, just wondering if you came across SqlBulkTools during your research? – Greg R Taylor Mar 21 '17 at 10:33
  • 1
    @JonathanMagnan You are a life saver man , but I think you can add more sale plans – sepehr May 08 '17 at 13:40
  • Hello, @sepehr, you can contact us directly if you have some suggestions, we will be happy to analyze your idea with you and see if that could work. – Jonathan Magnan May 08 '17 at 16:38
  • As of version 1.8.11 TPH & TPC still don't work. And it has been requested for years. Very unreliable. I would not count on the code of this program. – N73k Nov 10 '18 at 15:46
  • @N73k, It already works on `Entity Framework Extensions`. Very soon, "Batch Update" and "Batch Delete" will be free in `EFE` – Jonathan Magnan Nov 10 '18 at 21:21
  • No, it doesn't work completely. For example, Delete & DeleteFromQuery don't work with TPT - only the top level table is affected. – N73k Nov 12 '18 at 14:26
  • Not the better place to provide support but feel free to send your example that doesn't work: info@zzzprojects.com, we will add support to them. – Jonathan Magnan Nov 13 '18 at 02:22
  • zzzprojects stuff fo EF is great but it doesn't mix with unit tested code :( – Ian Nov 29 '18 at 16:58
  • Hello Ian, it now works great with Effort: https://entityframework-effort.net/ – Jonathan Magnan Nov 30 '18 at 19:59
  • 1
    @JonathanMagnan: I am confused about licensing. The page at (https://github.com/zzzprojects/EntityFramework-Plus/wiki/PRO-License) says: "PRO License Jonathan Magnan edited this page on Sep 12, 2018 · 3 revisions No setup required. The Entity Framework Plus library is FREE!" – g.pickardou May 19 '19 at 18:59
  • I'd like to know too, i have installed the plugin but i can't find any trial info on my project and I'd like to make sure i can use it for production – Jackal Aug 03 '19 at 13:10
  • 2
    If you landed here, this is worth a look (has MIT license) --> https://github.com/borisdj/EFCore.BulkExtensions – IdusOrtus May 20 '21 at 14:18
  • 1
    @IdusOrtus EFCore.BulkExtensions is no longer free... It costs between $500-$4000 for an enterprise: https://github.com/borisdj/EFCore.BulkExtensions/issues/1079 – Jonathan Magnan Mar 31 '23 at 18:42