C# 实体框架：违反多对多关系的参照完整性约束

Entity Framework: A referential integrity constraint violation on many to many relationship

提问by Not loved

Hey I have an application with a bunch of inproc caching and entity framework. When I want to write an update to an entity I reattach the cached copy. I track all things I've attached in the life cycle of the context so I don't try to attach them twice.

嘿，我有一个带有一堆 inproc 缓存和实体框架的应用程序。当我想向实体写入更新时，我会重新附加缓存的副本。我跟踪我在上下文的生命周期中附加的所有内容，因此我不会尝试附加它们两次。

I have an error occurring on attach (very rarely in most cases this works fine and is really fast) which says the following:

我在附加时发生了一个错误（在大多数情况下这很少工作并且非常快），它说如下：

A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship.

发生参照完整性约束冲突：定义参照约束的属性值在关系中的主体和从属对象之间不一致。

I've taken a really careful look at the entity which looks normal. I think this issue is due to the attachment/detachment of a foreign key when fixups runs.

我非常仔细地查看了看起来正常的实体。我认为这个问题是由于修复运行时外键的附加/分离造成的。

Is there a good way to get any more info on this error or can it occur for reasons other than that the entity was in a state which EF wasnt expecting?

是否有一种好方法可以获取有关此错误的更多信息，或者是否可能由于实体处于 EF 未预料到的状态以外的其他原因而发生？

EDIT: DB Diagram (note i'm using codefirst I just used the EDMX tool to make the diagram, I've also chopped a bunch of regular properties off the model for simplicity)

编辑：DB 图表（注意我使用的是 codefirst 我只是使用 EDMX 工具来制作图表，为了简单起见，我还从模型中删除了一堆常规属性）

采纳答案by Slauma

The error could occur for the one-to-many relationship between Personand Locationyou apparently have in your model in addition to the many-to-many relationship. For example the following code would throw the exception:

除了多对多关系之外，您的模型中显然存在的Person和之间的一对多关系可能会发生错误Location。例如，下面的代码会抛出异常：

using (var context = new MyContext())
{
    var person = new Person
    {
        CurrentLocationId = 1,
        CurrentLocation = new Location { Id = 2 }
    };
    context.People.Attach(person); // Exception
}

"The property values that define the referential constraints" are the foreign key property value CurrentLocationIdand the primary key value CurrentLocation.Id. If those values are different the exception is thrown. (Having CurrentLocationas nullthough is allowed.)

“定义引用约束的属性值”是外键属性值CurrentLocationId和主键值CurrentLocation.Id。如果这些值不同，则抛出异常。（有CurrentLocation因为null虽然是允许的。）

In my opinion this exception can only be thrown for foreign key associations because only for this type of association you have properties that define referential constraints at all in your model. It cannot be thrown for independent associations. Since every many-to-many relationship is an independent association (no foreign key property in the model) my guess is that the error is not related to your many-to-many relationship, but to the one-to-many.

在我看来，只能为外键关联抛出此异常，因为只有对于这种类型的关联，您才能在模型中完全定义引用约束的属性。不能为独立协会抛出它。由于每个多对多关系都是一个独立的关联（模型中没有外键属性），我的猜测是错误与您的多对多关系无关，而是与一对多关系有关。

回答by Jason Underhill

I have just been experiencing the same issue and the resolution to mine was that I had added mappings to the association and then setup the referential contstraints.

我刚刚遇到了同样的问题，我的解决方案是我已将映射添加到关联，然后设置引用约束。

Inorder to resolve the issue I had to open the mappings window for the association and there was a link to delete the mappings. Once done the Mapping Details window then said Mappings are not allowed.. It appears that adding the referential constraint leaves any mappings in place.

为了解决这个问题，我必须打开关联的映射窗口，并且有一个删除映射的链接。完成 Mapping Details 窗口后，就会说 Mappings are not allowed.. 似乎添加引用约束会保留任何映射。

Thought it may be worth posting in case anyone else is looking for solutions to this error message in the future.

认为可能值得发布以防其他人在未来寻找此错误消息的解决方案。

回答by OkurYazar

@LukeMcGregor hi,

@LukeMcGregor 嗨，

I think I can offer a different perspective as someone who has the same problem.

我想我可以提供与遇到相同问题的人不同的观点。

After I have performed all the necessary checks, I can say that I prefer to get this error.

在我执行了所有必要的检查之后，我可以说我更愿意得到这个错误。

Because in my scenario: I wanted to include an object that caused a mismatch error. It's the location object in your scenario. If I add an object with an ID, I get this error because the ID in the previous object (the one that is not updated) does not match the updated ID.

因为在我的场景中：我想包含一个导致不匹配错误的对象。它是您场景中的位置对象。如果我添加一个带有 ID 的对象，我会收到此错误，因为前一个对象（未更新的对象）中的 ID 与更新后的 ID 不匹配。

But it's not a big problem. As a solution; If it is still on the UI side, the object may still be included if it still exists.

但这不是什么大问题。作为解决方案；如果它仍然在 UI 端，如果它仍然存在，该对象可能仍然被包含在内。

You will either empty the object when you receive the update request from the user. (= Null) Or you will update the object with the ID updated by the user before the service-side update (attach, modified ... whatever) and update it in this way.

当您收到用户的更新请求时，您将清空该对象。(= Null) 或者您将在服务端更新之前使用用户更新的 ID 更新对象（附加、修改...等等）并以这种方式更新它。

That's it. It can remain as it is in the database and diagrams.

就是这样。它可以在数据库和图表中保持原样。

回答by Erez Lerner

I came across a very similar exception:

我遇到了一个非常相似的异常：

"A referential integrity constraint violation occurred: 
The property value(s) of 'ObjectA.PropertyX' on one end of a relationship 
do not match the property value(s) of 'ObjectB.PropertyY' on the other end."

The reason was this: The client side of the web API sent a PUT request with the entire object including the navigation property (in this example ObjectA (more correctly ObjectB.ObjectA) was a navigation property and was fully supplied by the client). This occurs because the client receives the entire object from the server and bounces it as-is back to the server with minor changes.

原因是这样的：Web API 的客户端发送了一个包含整个对象的 PUT 请求，包括导航属性（在这个例子中 ObjectA（更准确地说是 ObjectB.ObjectA）是一个导航属性，完全由客户端提供）。发生这种情况是因为客户端从服务器接收整个对象并将其原样退回到服务器，并进行细微的更改。

On the other hand, the ObjectB.PropertyY had just been changed (this was the reason for the PUT request in the first place).

另一方面，ObjectB.PropertyY 刚刚被更改（这首先是 PUT 请求的原因）。

Since ObjectB.PropertyY was a reference to the same object ObjectA (a foreign key), EF tried to reconcile this and failed with the above exception.

由于 ObjectB.PropertyY 是对同一对象 ObjectA（外键）的引用，因此 EF 尝试协调这一点并因上述异常而失败。

The solution was simple:

解决方法很简单：

ObjectB.ObjectA = null;

before the SaveChanges() solved this completely.

在 SaveChanges() 完全解决这个问题之前。

I hope this helps someone.

我希望这可以帮助别人。

回答by clarence_odbody

I just had this problem and came up with a pretty quick solution. My issue was with a many-many table.

我刚刚遇到了这个问题，并想出了一个非常快速的解决方案。我的问题是多对多表。

Public class Pictures_Tag
{
    [Key]
    [Column(Order = 0)]
    [ForeignKey("Picture")]
    public Int16 Picture_ID { get; set; }
    [Key]
    [Column(Order = 1)]
    [ForeignKey("ImageTag")]
    public Int16 ImageTag_ID { get; set; }
    public virtual Picture Picture { get; set; }
    public virtual ImageTag ImageTag { get; set; }
}

I added the line where I assigned Picture = db.Pictures...and then it worked fine (not exactly sure why)

我添加了我分配的行，Picture = db.Pictures...然后它工作正常（不完全确定为什么）

[HttpPost]
public ActionResult Edit2(WebSiteEF2017C.Models.Pictures_Tag p)
{     
    using (var db = new thisModel(Session["org"].ToString())
    {
         p.Picture = db.Pictures.Where(z => z.ID == p.Picture_ID).FirstOrDefault();
         db.Pictures_Tags.Attach(p);
         db.Entry(p).State = EntityState.Modified;
         db.SaveChanges();
         return View(db.Pictures_Tags.Include(x => x.Picture)
                    .Where(n => n.Picture_ID == p.Picture_ID & n.ImageTag_ID == p.ImageTag_ID).FirstOrDefault());
    }
}

回答by Migit

To add to @Slauma's answer, it isn't just when adding objects to your context. For your example, if you edit the CurrentLocationId in Person, you also need to edit the CurrentLocation object embedded in the Person object. EF will automatically populate the CurrentLocation object because CurrentLocationId has a foreign key in the CurrentLocation's table. When you edit the CurrentLocationId without updating the CurrentLocation object as well, they become out of sync. This is what causes the exception in this case.

要添加到@Slauma 的答案中，不仅仅是将对象添加到您的上下文中。对于您的示例，如果您在 Person 中编辑 CurrentLocationId，则还需要编辑嵌入在 Person 对象中的 CurrentLocation 对象。EF 将自动填充 CurrentLocation 对象，因为 CurrentLocationId 在 CurrentLocation 的表中有一个外键。当您编辑 CurrentLocationId 而不更新 CurrentLocation 对象时，它们会变得不同步。这就是在这种情况下导致异常的原因。

So let's say you needed to update the Person object's CurrentLocationId. We'll assume you pre-fetched the Person data and the Location data.

因此，假设您需要更新 Person 对象的 CurrentLocationId。我们假设您预取了 Person 数据和 Location 数据。

public class DbData 
{
    List<Person> PersonList;
    List<Location> LocationList;
    public DbData()
    {
        using (var context = new MyContext())
        {
             PersonList = context.Persons.ToList();
             LocationList = context.Locations.ToList();
        }
    }

    public void UpdatePersonLocation(Person person, int newLocationId)
    {
        using (var context = new MyContext())
        {
            var location = LocationList.Where(l=>l.id==newLocationId).Single();
            //you need to update both the id and the location for this to not throw the exception
            person.CurrentLocationId == newLocationId;
            person.CurrentLocation == location;  
            context.Entry(person).State = System.Data.Entity.EntityState.Modified;
            context.SaveChanges();
        }
    }
    //or if you're giving it the location object...
    public void UpdatePersonLocation(Person person, Location location)
    {
        using (var context = new MyContext())
        {
            //you need to update both the id and the location for this to not throw the exception
            person.CurrentLocationId == location.id;
            person.CurrentLocation == location;  
            context.Entry(person).State = System.Data.Entity.EntityState.Modified;
            context.SaveChanges();
        }
    }
}