title: Entity Framework inserting child objects in the wrong order
body: <h2>Question</h2>
  
  <p>Why is EF first inserting a child object (PersonnelWorkRecord) with a dependency, before the object that it is depended on (TimesheetActivity). Also what are my options on correcting this?</p>
  
  <h2>ERD (simplified)</h2>
  
  <p>This is predefined by another system out of my direct control. 
  <img src="https://i.stack.imgur.com/9LESv.png" alt="ERD2"></p>
  
  <h2>EF setup and save code</h2>
  
  <p>I am not sure I understand why/how Entity Framework is inserting the objects I have in the order it does however here is the code I am using to insert a parent and several children.</p>
  
  <pre><code>using (var db = new DataContext(user))
  {
       timesheet.State = State.Added;
       timesheet.SequenceNumber = newSequenceNumber;
       this.PrepareAuditFields(timesheet);
  
       //To stop EF from trying to add all child objects remove them from the timehseets object.
       timesheet = RemoveChildObjects(timesheet, db);
  
       //Add the Timesheet object to the database context, and save.
       db.Timesheets.Add(timesheet);
       result = db.SaveChanges() &gt; 0;
  }
  </code></pre>
  
  <h2>SQL Trace of EF's Inserts</h2>
  
  <p>When I run the code I get a SQL foreign key violation on the PersonnelWorkRecord  (TimesheetActivityID) because I have not yet added the Activity (see trace).</p>
  
  <pre><code>exec sp_executesql N'insert [dbo].[Timesheets]([ProjectID], [TimesheetStatusID], ...
  exec sp_executesql N'insert [dbo].[PersonnelWorkdays]([TimesheetID], [PersonnelID], ...
  exec sp_executesql N'insert [dbo].[PersonnelWorkRecords]([PersonnelWorkdayID],[TimesheetActivityID], ...
  </code></pre>
  
  <h2>Data Context Summary</h2>
  
  <pre><code>modelBuilder.Entity&lt;PersonnelWorkday&gt;().HasRequired(pwd =&gt; pwd.Personnel).WithMany(p =&gt; p.PersonnelWorkdays).HasForeignKey(pwd =&gt; pwd.PersonnelID).WillCascadeOnDelete(false);
  modelBuilder.Entity&lt;PersonnelWorkday&gt;().HasRequired(pwd =&gt; pwd.Timesheet).WithMany(t =&gt; t.PersonnelWorkdays).HasForeignKey(pwd =&gt; pwd.TimesheetID).WillCascadeOnDelete(false);
  modelBuilder.Entity&lt;PersonnelWorkRecord&gt;().HasRequired(pwr =&gt; pwr.PersonnelWorkday).WithMany(pwd =&gt; pwd.PersonnelWorkRecords).HasForeignKey(pwr =&gt; pwr.PersonnelWorkdayID).WillCascadeOnDelete(false);
  modelBuilder.Entity&lt;PersonnelWorkRecord&gt;().HasRequired(pwr =&gt; pwr.TimesheetActivity).WithMany(ta =&gt; ta.PersonnelWorkRecords).HasForeignKey(pwr =&gt; pwr.TimesheetActivityID).WillCascadeOnDelete(false);
  modelBuilder.Entity&lt;TimesheetActivity&gt;().HasRequired(ta =&gt; ta.ProjectActivity).WithMany(a =&gt; a.TimesheetActivities).HasForeignKey(ta =&gt; ta.ProjectActivityCodeID).WillCascadeOnDelete(false);
  modelBuilder.Entity&lt;TimesheetActivity&gt;().HasOptional(ta =&gt; ta.Facility).WithMany(f =&gt; f.TimesheetActivities).HasForeignKey(tf =&gt; tf.FacilityID).WillCascadeOnDelete(false);
  modelBuilder.Entity&lt;TimesheetActivity&gt;().HasRequired(ta =&gt; ta.Timesheet).WithMany(t =&gt; t.TimesheetActivities).HasForeignKey(ta =&gt; ta.TimesheetID).WillCascadeOnDelete(false);
  </code></pre>
  
  <h2>Remove Child Objects</h2>
  
  <p>Here is the code for the child objects method. I added this method to remove the objects from the timesheets' child objects related objects that are not foreign keys. For example I have a Crew object but I also have a CrewID foreign key, so I have set Crew = null so that EF does not try to insert it since it already exists.</p>
  
  <pre><code>private Timesheet RemoveChildObjects(Timesheet timesheet, DataContext db)
  {
          timesheet.Crew = null;
          timesheet.Foreman = null;
          timesheet.Location = null;
          timesheet.Project = null;
          timesheet.SigningProjectManager = null;
          timesheet.TimesheetStatus = null;
          timesheet.Creator = null;
          timesheet.Modifier = null;
  
          if (timesheet.TimesheetActivities != null)
          {
              foreach (TimesheetActivity tsa in timesheet.TimesheetActivities)
              {
                  tsa.Creator = null;
                  if (tsa.EquipmentWorkRecords != null)
                  {
                      tsa.EquipmentWorkRecords = RemoveChildObjects(tsa.EquipmentWorkRecords, db);
                  }
                  tsa.Facility = null;
                  tsa.Modifier = null;
                  if (tsa.PersonnelWorkRecords != null)
                  {
                      tsa.PersonnelWorkRecords = RemoveChildObjects(tsa.PersonnelWorkRecords, db);
                  }
                  tsa.ProjectActivity = null;
                  tsa.Structures = null;
                  tsa.Timesheet = null;
              }
          }
  
          if (timesheet.TimesheetEquipment != null)
          {
              foreach (TimesheetEquipment te in timesheet.TimesheetEquipment)
              {
                  te.Equipment = null;
                  te.Timesheet = null;
              }
          }
  
          if (timesheet.EquipmentWorkdays != null)
          {
              timesheet.EquipmentWorkdays = RemoveChildObjects(timesheet.EquipmentWorkdays, true, db);
          }
  
          if (timesheet.TimesheetPersonnel != null)
          {
              foreach (TimesheetPersonnel tp in timesheet.TimesheetPersonnel)
              {
                  tp.Personnel = null;
                  tp.PersonnelWorkday = null;
                  if (tp.PersonnelWorkday != null)
                  {
                      tp.PersonnelWorkday = RemoveChildObjects(tp.PersonnelWorkday, db);
                  }
                  tp.Timesheet = null;
              }
          }
  
          if (timesheet.PersonnelWorkdays != null)
          {
              timesheet.PersonnelWorkdays = RemoveChildObjects(timesheet.PersonnelWorkdays, true, db);
          }
  
          return timesheet;
      }
  </code></pre>
  
  <h2>Debug of values before EF save</h2>
  
  <p>From my understanding anything an dbContex.ObjectNameHere.Local will be added/modified/deleted when a dbContext.Save() is called. (Depending on what the entity State is set too.) Here is what EF is trying to save before I call the save() and get an SQL FK exception.
  <img src="https://i.stack.imgur.com/a0mC3.png" alt="Step1">
  <img src="https://i.stack.imgur.com/GECmu.png" alt="Step2">
  Then I get the FK exception.</p>
  
  <blockquote>
    <p>The INSERT statement conflicted with the FOREIGN KEY constraint
    "FK_PersonnelWorkRecords_TimesheetActivities". The conflict occurred
    in database "VPMTEST_GC", table "dbo.TimesheetActivities", column
    'TimesheetActivityID'. The statement has been terminated.</p>
  </blockquote>
  
  <h2>Notes</h2>
  
  <p>Please let me know if there is anything I can post to help describe my question. I have looked around google / SO for answers but so far no solid answers, it looks like EF can not determine the order of inserting objects unless the Domain model is setup differently? I am not able to change the structure of most objects as they are used by another system. I can attempt to change my EF call, I would prefer not to use Raw SQL as the objects are quite a bit more extensive then the simplified versions I have posted here.</p>
  
  <p><strong>Similar questions:</strong> <a href="https://stackoverflow.com/questions/17483134/self-referencing-entity-insert-order-using-entity-framework-code-first">Self referencing entity and insert order</a></p>