c# - Very slow runtime with Entity Framework nested loop (using nav properties) -

right now, i'm trying write method survey submission program utilizes normalized schema.

i have method meant generate survey team of people, linking several different ef models in process. however, method runs extremely smallest team sizes (taking 11.2 seconds execute 4-person team, , whopping 103.9 seconds 8 person team). after analysis, found 75% of runtime taken in following block of code:

 var teammembers = db.teammembers.where(m => m.teamid == teamid && m.onteam).tolist();                     foreach (teammember teammember in teammembers)                     {                         employee employee = db.employees.find(teammember.employeeid);                         surveyform form = new surveyform();                         form.submitter = employee;                         form.state = "not submitted";                         form.surveygroupid = surveygroup.surveygroupid;                         db.surveyforms.add(form);                         db.savechanges();                          foreach (teammember peer in teammembers)                         {                             foreach (surveysectiondetail sectiondetail in sectiondetails)                             {                                 foreach (surveyattributedetail attributedetail in attributedetails.where(a => a.sectiondetail.surveysectiondetailid == sectiondetail.surveysectiondetailid) )                                 {                                     surveyanswer answer = new surveyanswer();                                     answer.reviewee = peer;                                     answer.surveyformid = form.surveyformid;                                     answer.detail = attributedetail;                                     answer.sectiondetail = sectiondetail;                                     db.surveyanswers.add(answer);                                     db.savechanges();                                 }                             }                         }                     } 

i'm @ loss how might go cutting runtime. price pay having many related entities? know joins expensive operations, , i've got 3 or there inefficiency i'm overlooking?

thanks help!

edit: requested xiaoy312, here's how sectiondetails , attributedetails defined:

surveytemplate template = db.surveytemplates.find(surveytemplateid); list<surveysectiondetail> sectiondetails = new list<surveysectiondetail>(); list<surveyattributedetail> attributedetails = new list<surveyattributedetail>();                     foreach (surveytemplatesection section in template.surveytemplatesections)                     {                         surveysectiondetail sectiondetail = new surveysectiondetail();                         sectiondetail.sectionname = section.sectionname;                         sectiondetail.sectionorder = section.sectionorder;                         sectiondetail.description = section.description;                         sectiondetail.surveygroupid = surveygroup.surveygroupid;                         db.surveysectiondetails.add(sectiondetail);                         sectiondetails.add(sectiondetail);                         db.savechanges();                          foreach (surveytemplateattribute attribute in section.surveytemplateattributes)                         {                             surveyattributedetail attributedetail = new surveyattributedetail();                             attributedetail.attributename = attribute.attributename;                             attributedetail.attributescale = attribute.attributescale;                             attributedetail.attributetype = attribute.attributetype;                             attributedetail.attributeorder = attribute.attributeorder;                             attributedetail.sectiondetail = sectiondetail;                             db.surveyattributedetails.add(attributedetail);                             attributedetails.add(attributedetail);                             db.savechanges();                           }                     } 

use include avoid select n + 1 issue.

surveytemplate template = db.surveytemplates.include("surveytemplatesections")              .include("surveytemplatesections.surveytemplateattributes")              .first(x=> x.surveytemplateid == surveytemplateid); 

generate whole object graph , save db.

list<surveysectiondetail> sectiondetails = new list<surveysectiondetail>(); list<surveyattributedetail> attributedetails = new list<surveyattributedetail>(); foreach (surveytemplatesection section in template.surveytemplatesections) {    surveysectiondetail sectiondetail = new surveysectiondetail();    //some code    sectiondetails.add(sectiondetail);     foreach (surveytemplateattribute attribute in section.surveytemplateattributes)    {         surveyattributedetail attributedetail = new surveyattributedetail();         //some code         attributedetails.add(attributedetail);    } } db.surveysectiondetails.addrange(sectiondetails); db.surveyattributedetails.addrange(attributedetails); db.savechanges(); 

load employees want before loop, avoids database query every team member.

var teammemberids =  db.teammembers.where(m => m.teamid == teamid && m.onteam)     .select(x=>x.teammemberid).tolist();  var employees = db.employees.where(x => teammemberids.contains(x.employeeid)); 

create dictionary attributedetails based on sectiondetailid avoid query list on every iteration.

var attributedetailsgroupbysection = attributedetails.groupby(x => x.sectiondetailid)        .todictionary(x => x.key, x => x); 

move saving of surveyanswers , surveyforms outside of loops:

list<surveyform> forms = new list<surveyform>(); list<surveyanswer> answers = new list<surveyanswer>(); foreach (int teammemberid in teammemberids) {     var employee = employees.first(x => x.id == teammemberid);     surveyform form = new surveyform();     //some code     forms.add(form);     foreach (int peer in teammemberids)     {          foreach (surveysectiondetail sectiondetail in sectiondetails)          {               foreach (surveyattributedetail attributedetail in                           attributedetailsgroupbysection[sectiondetail.id])               {                    surveyanswer answer = new surveyanswer();                    //some code                    answers.add(answer);               }          }     } } db.surveyanswers.addrange(answers); db.surveyforms.addrange(forms); db.savechanges(); 

finally if want faster insertions can use entityframework.bulkinsert. extension, can save data this:

db.bulkinsert(answers); db.bulkinsert(forms);