Question
--------

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?

ERD (simplified)
----------------

This is predefined by another system out of my direct control. 
![ERD2](https://i.stack.imgur.com/9LESv.png)

EF setup and save code
----------------------

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.

```
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() > 0;
}

```

SQL Trace of EF's Inserts
-------------------------

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).

```
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], ...

```

Data Context Summary
--------------------

```
modelBuilder.Entity<PersonnelWorkday>().HasRequired(pwd => pwd.Personnel).WithMany(p => p.PersonnelWorkdays).HasForeignKey(pwd => pwd.PersonnelID).WillCascadeOnDelete(false);
modelBuilder.Entity<PersonnelWorkday>().HasRequired(pwd => pwd.Timesheet).WithMany(t => t.PersonnelWorkdays).HasForeignKey(pwd => pwd.TimesheetID).WillCascadeOnDelete(false);
modelBuilder.Entity<PersonnelWorkRecord>().HasRequired(pwr => pwr.PersonnelWorkday).WithMany(pwd => pwd.PersonnelWorkRecords).HasForeignKey(pwr => pwr.PersonnelWorkdayID).WillCascadeOnDelete(false);
modelBuilder.Entity<PersonnelWorkRecord>().HasRequired(pwr => pwr.TimesheetActivity).WithMany(ta => ta.PersonnelWorkRecords).HasForeignKey(pwr => pwr.TimesheetActivityID).WillCascadeOnDelete(false);
modelBuilder.Entity<TimesheetActivity>().HasRequired(ta => ta.ProjectActivity).WithMany(a => a.TimesheetActivities).HasForeignKey(ta => ta.ProjectActivityCodeID).WillCascadeOnDelete(false);
modelBuilder.Entity<TimesheetActivity>().HasOptional(ta => ta.Facility).WithMany(f => f.TimesheetActivities).HasForeignKey(tf => tf.FacilityID).WillCascadeOnDelete(false);
modelBuilder.Entity<TimesheetActivity>().HasRequired(ta => ta.Timesheet).WithMany(t => t.TimesheetActivities).HasForeignKey(ta => ta.TimesheetID).WillCascadeOnDelete(false);

```

Remove Child Objects
--------------------

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.

```
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;
    }

```

Debug of values before EF save
------------------------------

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.
![Step1](https://i.stack.imgur.com/a0mC3.png)
![Step2](https://i.stack.imgur.com/GECmu.png)
Then I get the FK exception.

> 
> 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.
> 
> 
> 

Notes
-----

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.

**Similar questions:** [Self referencing entity and insert order](https://stackoverflow.com/questions/17483134/self-referencing-entity-insert-order-using-entity-framework-code-first)