2

How to batch delete data in DB with Entity Framework. This my C# code,and now it work very slow. Is that because it was deleted only one row a time? How to delete it faster. Now,20K rows deleting need 30 min. 

        var ts = from t in db.MyTasks
                 where t.MyTaskID == id
                 select t;
        var sjs = from t in ts
                  from s in t.Comments
                  where s.CommentJobs.Count() > 0
                  select s;
        var ss = from t in ts
                 from s in t.Comments
                 select s;
        sjs.ToList().ForEach(o => {
            db.CommentJobs.RemoveRange(o.CommentJobs);
        });
        db.Comments.RemoveRange(ss);
        db.MyTasks.RemoveRange(ts);
        db.SaveChanges();
Yeezh
  • 141
  • 1
  • 7
  • Which version of EF ? – Paul Zahra Aug 17 '15 at 11:30
  • If EF6 + you can use the new RemoveRange() method ... http://stackoverflow.com/questions/21568479/how-can-i-delete-1-000-rows-with-ef6 which should speed things up nicely and negate the need of 'jumping out of the model' and using something like SqlQuery(). – Paul Zahra Aug 17 '15 at 11:31
  • @Paul Zahra -My EF version is 6.1.3.And I have already using RemoveRange() in my code.But it is still very slow. – Yeezh Aug 17 '15 at 11:37
  • 1
    Possible duplicate of http://stackoverflow.com/questions/869209/bulk-deleting-in-linq-to-entities – bubi Aug 17 '15 at 12:51
  • RemoveRange() won't really help with performance; it is primarily for programming convenience. There is something called EntityFramework.Extended (https://github.com/loresoft/EntityFramework.Extended) that supposedly does batch deletes. I have never used it but you might want to check it out. – Jeff Prince Aug 17 '15 at 15:36
  • @Jeff Prince This EntityFramework.Extended really works. It can delete 20K rows in 2sec. And if this @ can make StackOverflow Site sending you a notification,please give me a message.I am a fresh about this site,dont know how to reply a comment.Thank you. – Yeezh Aug 18 '15 at 05:58
  • @Yizhi. Cool, I'm glad it worked out for you. I am going to have to give that a try. – Jeff Prince Aug 19 '15 at 13:35

1 Answers1

0

For bulk operations like this, you can use SqlQuery() method.

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39