Entity Framework inserting child objects in the wrong order

Question

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

Accepted Answer

In your `RemoveChildObjects` method I see the line...

```
tsa.Timesheet = null;
```

So, apparently your are setting the inverse navigation property of `Timesheet.TimesheetActivities` to `null`. Are you doing the same with `PersonnelWorkRecord.TimesheetActivity` and `PersonnelWorkRecord.PersonnelWorkday`, i.e. do you set those properties to `null` as well in the nested `RemoveChildObjects` methods?

This could be a problem because you have two different paths from `Timesheet` to `PersonnelWorkRecord`, namely:

```
Timesheet -> TimesheetActivities -> PersonnelWorkRecords
Timesheet -> PersonnelWorkdays -> PersonnelWorkRecords
```

When you call `db.Timesheets.Add(timesheet)` I believe EF will traverse each branch in the object graph one by one and determine on the path which related objects ("nodes") are dependent and which are principal in a relationship to determine the order of insertion. `timesheet` itself is principal for all its relationships, therefore it is clear that it must be inserted first. Then EF starts to iterate through one of the collections `Timesheet.TimesheetActivities` or `Timesheet.PersonnelWorkdays`. Which one comes first doesn't matter. Apparently EF starts with `Timesheet.PersonnelWorkdays`. (It would not solve the problem if it would start with `Timesheet.TimesheetActivities`, you would get the same exception, but with `PersonnelWorkRecord.PersonnelWorkday` instead of `PersonnelWorkRecord.TimesheetActivity`.) `PersonnelWorkday` is only dependent on `Timesheet` which is already inserted. So, `PersonnelWorkday` can be inserted as well.

Then EF continues traversing with `PersonnelWorkday.PersonnelWorkRecords`. With respect to the `PersonnelWorkday` dependency of `PersonnelWorkRecord` there is again no problem because the `PersonnelWorkday` has already been inserted before. But when EF encounters the `TimesheetActivity` dependency of `PersonnelWorkRecord` it will see that this `TimesheetActivity` is `null` (because you've set it to `null`). It assumes now that the dependency is described by the foreign key property `TimesheetActivityID` alone which must refer to an existing record. It inserts the `PersonnelWorkRecord` and this violates a foreign key constraint.

If `PersonnelWorkRecord.TimesheetActivity` is not `null` EF would detect that this object hasn't been inserted yet but it is the principal for `PersonnelWorkRecord`. So, it can determine that this `TimesheetActivity` must be inserted before the `PersonnelWorkRecord`.

I would hope that your code works if you don't set the inverse navigation properties to `null` - or at least not the two navigation properties in `PersonnelWorkRecord`. (Setting the other navigation properties like `tsa.Creator`, `tsa.Facility`, etc. to `null` should not be a problem because those related objects really already exist in the database and you have set the correct FK property values for those.)

Entity Framework inserting child objects in the wrong order

Solution

Problem