c# - Entity Framework Bulk Insert/Update with Relationships -

i have scenario need perform db updates large amount of data. there foreign key relationships need added @ same time , getting list of foreign objects don't have hit database each time check if exist/add them etc:

using(dbentities db = new dbentities()) {    // list of books don't have hit every time    dictionary<int, book> books = db.books.todictionary(k => k.bookid, v => v);     // loop through big file import each row    foreach(var item in bigfile)    {       person person = new person { firstname = item.firstname, lastname = item.lastname };        foreach(var book in item.books)       {          if(!books.containskey(book.bookid))          {             // add book db if doesn't exist             book booktoadd = new book { bookid = book.bookid, name = book.name };             db.books.add(booktoadd);              books.add(booktoadd.bookid, booktoadd);          }           person.books.add(books[book.bookid]);       }        db.people.add(person);    }     db.savechanges(); } 

the problem solution import starts fast , slows down goes , becomes slow. seems down context getting bloated change tracking.

i have seen posts suggesting using db.configuration.autodetectchangesenabled = false when relationships not added. can make work forcing detectchanges() seems defeat purpose have every iteration of loop.

so, moved db context inside loop recreated each time. doing means can no longer have detached list of books have .any() , .single() call db each row (i don't know if major performance issue try hit db infrequently possible):

// loop through big file import each row foreach(var item in bigfile) {    // create db context each time    using(dbentities db = new dbentities())    {       person person = new person { firstname = item.firstname, lastname = item.lastname };        foreach(var book in item.books)       {          if(!db.books.any(m => m.bookid = bookid))          {             // add book db if doesn't exist             book booktoadd = new book { bookid = bookid, name = book.name              db.books.add(booktoadd);          }           person.books.add(db.books.single(m => m.bookid = bookid));       }        db.people.add(person);        db.savechanges();    } } 

this speeds considerably still starts slows down after around 5,000-10,000 rows , wondering options are? ...other doing stored procedures!

imo both solutions not good. first loading whole existing books table in memory (and db context) , second performs 2 db queries per person book - 1 any , 1 single.

since tests not show performance issues context change tracking, use variation of first approach elements second. instead of loading whole books table, use local dictionary populated on demand, single db query each new book id:

using (dbentities db = new dbentities()) {     // local book dictionary     dictionary<int, book> books = new dictionary<int, book>();      // loop through big file import each row     foreach (var item in bigfile)     {         person person = new person { firstname = item.firstname, lastname = item.lastname };          foreach (var itembook in item.books)         {             book book;              // try local dictionary             if (!books.trygetvalue(itembook.bookid, out book))             {                 // try db                 book = db.books.firstordefault(e => e.bookid == itembook.bookid);                 if (book == null)                 {                     // add book db if doesn't exist                     book = new book { bookid = itembook.bookid, name = itembook.name };                     db.books.add(book);                 }                 // add local dictionary                 books.add(book.bookid, book);             }              person.books.add(book);         }          db.people.add(person);     }      db.savechanges(); }