1

Why EF SaveChanges is going to insert/delete each record in a separate query?

Suppose this code:

using (var db = new AppEntities())
{
    using (var transaction = db.Database.BeginTransaction(System.Data.IsolationLevel.Serializable))
    {
        try
        {
            db.Table1.AddRange(list);
            db.Table2.RemoveRange(list);
        }
        db.SaveChanges();
        transaction.Commit();
    }
}

I check these lines with SQL Server Profiler, and surprisingly there is one INSERT INTO per each record! And worse than that, the same was for delete! This is not a serious problem for 100 rows. But with 5000 rows it takes 2 minutes!

Is it possible to tell EF, PLEASE do this in following way :

INSERT INTO TABLE1 VALUES (),(),(),();
ABS
  • 2,626
  • 3
  • 28
  • 44
  • 1
    There's no way to do this, but you can [improve your performance](https://stackoverflow.com/questions/6107206/improving-bulk-insert-performance-in-entity-framework) or use [external libraries](http://entityframework-extensions.net). – DavidG Nov 20 '17 at 18:26
  • use stored proc for bulk insert and deletes – Kashif Qureshi Nov 20 '17 at 18:27
  • @DavidG I think most effective improvement, is disposing ctx every n rows. But it is not possible in my code(db instance is in higher scope). And only disabling AutoDetectChanges and SaveChanges every 100 rows can't help much. – ABS Nov 20 '17 at 19:01
  • I've used [EF Extensions](http://entityframework-extensions.net/). It's unbelievable fast! But isn't it paid? In [FAQ section](http://entityframework-extensions.net/download) of this project, noted that the latest version always contains a trial that expires at the end of the month. And you have to download latest version end of the month! So we HAVE to release new version of our application every month?! – ABS Nov 20 '17 at 19:12
  • 1
    @AliBagheriShakib, if you purchase a license, you don’t have since the license is perpetual and will work forever. – Jonathan Magnan Nov 20 '17 at 19:51
  • @JonathanMagnan Thanks. Your licenses start with 599$ and this is too much for us! Is there a free version with fewer features that cover bulkinsert? You mean trial version stops working every month?! – ABS Nov 20 '17 at 20:00
  • @AliBagheriShakib, there is some comment I cannot do here on Stack Overflow such as talking about pricing. I recommend you to contact us directly. – Jonathan Magnan Nov 20 '17 at 20:08

0 Answers0