Batch Delete with Entity Framework and Microsoft Azure -
i use entity framework 6.1, mvc5, asp.net 4.5 , azure websites (free) + azure sql.
when try delete 45 000 entities, takes more 45 minutes. have navigation properties in entities.
i tried :
context.contacts.removerange(db.groups.find(id).contacts.tolist());
and :
foreach(var contact in db.groups.find(id).contacts.tolist()) { context.database.executesqlcommand("delete contacts contactid = {0}", contact.contactid); }
i'm sure it's not normal... causing problem ?
when calling .tolist()
pulling of entities memory start. , looping through these calling same piece of sql 45000 times if understand situation.
you shouldn't need entire object returned delete it. suggest pulling out ids of contacts delete selecting id in query:
var ids = db.groups.find(id).contacts.select(x => x.contactid).tolist();
i implementing either entityframeworkextensions library(https://github.com/loresoft/entityframework.extended) handle (making sure batch deletes), or using in query, delete contacts contactid in (1,2,3,....)
again, making sure batch this.
the extensions library lets delete like:
m_context.contacts.delete(x => idlist.contains(x.contactid));
note execute immediately, , not when call savechanges()
overall, code this:
public int removecontacts(ilist<int> _ids) { int index = 0; int numdeleted = 0; while (index < _ids.count()) { var batch= _ids.skip(index).take(max_batch_size); //using extensions method numdeleted += context.contacts.delete(x => batch.contains(x.contactid)); //using sql context.database.executesqlcommand("delete contacts contactid in {0}", batch); index += max_batch_size; } return numdeleted ; }
Comments
Post a Comment