实体框架以错误的顺序插入子对象 [英] Entity Framework inserting child objects in the wrong order

查看：151
本文介绍了实体框架以错误的顺序插入子对象的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

问题



为什么EF首先在依赖的对象（Times SheetActivity）之前插入一个依赖关系的子对象（PersonnelWorkRecord）。另外我的修改方法是什么？



ERD（简体）



这是另一个系统预定义的我的直接控制。



EF设置并保存代码



我不知道我明白为什么/如何Entity Framework插入对象我按照它的顺序，但是这里是我用来插入父代码和几个孩子。

  using（var db = new DataContext（user））
 {
 timesheet。 State = State.Added; 
 timesheet.SequenceNumber = newSequenceNumber; 
 this.PrepareAuditFields（timesheet）; 
 
 //要阻止EF尝试添加所有子对象，请将它们从timehseets对象中删除。 
 timesheet = RemoveChildObjects（timesheet，db）; 
 
 //将Timesheet对象添加到数据库上下文中，并保存。 
 db.Timesheets.Add（timesheet）; 
 result = db.SaveChanges（）> 0; 
}



EF追踪EF的插入



当我运行代码时，我会在PersonnelWorkRecord（TimesheetActivityID）上获得SQL外键冲突，因为我还没有添加Activity（请参阅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]，...



数据上下文摘要



  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.Tim esheetID）.WillCascadeOnDelete（假）; 
 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） ;



删除子对象



子对象方法的代码。我添加了这种方法来从时间表的子对象相关对象中删除不是外键的对象。例如，我有一个Crew对象，但是我也有一个CrewID外键，所以我设置了Crew = null，所以EF不会尝试插入它，因为它已经存在。

  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人员= 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） ; 
} 
 
返回时间表; 
}



在EF保存之前调试值



从我的理解中，当调用dbContext.Save（）时，dbContex.ObjectNameHere.Local将被添加/修改/删除。 （取决于实体状态的设置。）这是EF在调用save（）之前尝试保存的内容，并获得SQL FK异常。


然后我得到FK异常。


INSERT语句与FOREIGN KEY约束
FK_PersonnelWorkRecords_TimesheetActivities。冲突发生在数据库VPMTEST_GC中的
，表dbo.TimesheetActivities，列
'TimesheetActivityID'。




笔记



请让我知道有什么我可以发布来帮助描述我的问题。我已经看过google / SO的答案，但到目前为止还没有完整的答案，看起来像EF无法确定插入对象的顺序，除非域模式的设置有所不同？我不能改变大多数对象的结构，因为它们被另一个系统所使用。我可以尝试更改我的EF调用，我不喜欢使用Raw SQL，因为对象是更广泛的，然后我发布在这里的简化版本。



类似问题：自我引用实体和插入顺序

解决方案

在您的 RemoveChildObjects 我看到这条线... 

  tsa.Timesheet = null;

所以，显然你正在设置反向导航属性 Timesheet.TimesheetActivities 到 null 。您是否使用 PersonnelWorkRecord.TimesheetActivity 和 PersonnelWorkRecord.PersonnelWorkday 进行相同操作，即将这些属性设置为 null 以及嵌套的 RemoveChildObjects 方法？



这可能是因为您有两条不同的路径，从 Timesheet 到 PersonnelWorkRecord ，即：

 时间表 - > TimesheetActivities  - > PersonnelWorkRecords 
时间表 - >人事工作日 - > PersonnelWorkRecords

当您调用 db.Timesheets.Add（timesheet）我相信EF将逐个遍历对象图中的每个分支，并确定相关对象（节点）依赖的路径，并确定关系中的主体以确定插入顺序。 时间表本身是所有关系的主体，因此很明显它必须先插入。然后，EF开始遍历其中一个集合 Timesheet.TimesheetActivities 或 Timesheet.PersonnelWorkdays 。哪一个首先没关系。显然，EF从 Timesheet.PersonnelWorkdays 开始。 （如果以 Timesheet.TimesheetActivities 开头，则不会解决问题，您将获得相同的异常，但使用 PersonnelWorkRecord.PersonnelWorkday 而不是 PersonnelWorkRecord.TimesheetActivity 。） PersonnelWorkday 仅依赖于 Timesheet 已经插入。所以， PersonnelWorkday 也可以插入。



然后EF继续遍历 PersonnelWorkday。 PersonnelWorkRecords 。相对于 PersonnelWorkday 依赖关系 PersonnelWorkRecord 再次没有问题，因为 PersonnelWorkday 之前已经插入。但是，当EF遇到 TimesBookActivity 依赖关系 PersonnelWorkRecord 时，会看到这个 Times SheetActivity 是 null （因为你设置为 null ）。它现在假设依赖关系由外键属性 Times SheetActivityID 单独描述，它必须引用现有记录。它插入 PersonnelWorkRecord ，这违反外键约束。



如果 PersonnelWorkRecord.TimesheetActivity 不是 null EF会检测到该对象尚未插入，但它是 PersonnelWorkRecord 。所以，可以确定这个 Times SheetActivity 必须在 PersonnelWorkRecord 之前插入

如果您不要将反向导航属性设置为 null >或至少不是 PersonnelWorkRecord 中的两个导航属性。 （将其他导航属性设置为 tsa.Creator ， tsa.Facility 等到 null 不应该是一个问题，因为这些相关对象真的已经存在于数据库中，并且为这些对象设置了正确的FK属性值。）


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

解决方案

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

这篇关于实体框架以错误的顺序插入子对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
C#/.NET最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆