EF Insert entity with large number of new child entities

Question

I've seen a number of "Bulk Insert in EF" questions however all of these deal with a usecase where a user is trying to insert a large array of items.

I've a situation where I have a new Parent entity with ~1500 new related entities attached to it. Both the parent and the child entities are mapped to their own tables in EF.

At the moment I'm using something like:

//p is already created and contains all the new child items    
public void SaveBigThing(Parent p){
  if(p.Id == 0){
    // we've got a new object to add
    db.BigObjects.Add(p);
  }
  db.SaveChanges();
}

Entity Framework at the moment creates an individual insert statement for each and every child item. Which takes 50 seconds or so. I want to be able to use db.ChildEntity.AddRange(items) But I'm unsure if there's a better way than to use 2 separate operations. First create the parent to get it's Id then a AddRange for all the child items?

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

IMHO You dont need to add parent first in-order to insert child items. You could do that in one shot.

You could try this in EF 5 AddRange is only available in EF 6 or higher.

This will not insert the item in bulk it will generate the query and insert at one shot

Ef bulk insertion reference

Another reference

public bool InsertParent(Parent parentObject)
{   
    //Assuming this is the parent table
    db.BigObjects.Add(parentObject);
    InsertChilds(parentObject); //Insert childs
    db.SaveChanges();
}

public bool InsertChilds(Parent parentObject)
{
    // This will save more time . 
    DataContext).Configuration.AutoDetectChangesEnabled = false;
    foreach(var child in parentObject.Childs)
    {
        //This will set the child parent relation
        child.Parent = childParent;
        db.ChildEntity.Add(child);
    }
    DataContext).Configuration.AutoDetectChangesEnabled = true;

}

edited May 23 '17 at 11:59

Community

1
1

answered Jan 05 '16 at 13:02

Eldho

7,795
5
40
77

I'm using ef6. There also doesn't appear to be a .Insert method on a DbSet. I don't think this would bulk insert the child items anyway. – Ben Ford Jan 05 '16 at 13:07
Sorry it was `Add()` i misplaced with `insert()` as i was using `UnitOfWork` – Eldho Jan 05 '16 at 13:17
My issue is that SQL server is having to complete thousands of separate SQL inserts to add all the child items. I believe using the process you've described it will still do the same. – Ben Ford Jan 05 '16 at 13:43
Yes i too believe the process is same, but in my experience i had inserted to 17K items within less than 2 mints time . There has more than 4 `tables` if you need more performance use `StoredProcedure` – Eldho Jan 05 '16 at 13:49

EF Insert entity with large number of new child entities

1 Answers1