为什么更新外键后引用约束不一致？ [英] Why become referential constraints inconsistent after updating foreign key?

问题描述

我有两个实体用户和 UserAddress ，其中用户有2个外键 DefaultInvoiceAddressId 和 DefaultDeliveryAddressId 和UserAddress有一个 UserId 外键。

用户对象具有默认地址的导航属性 DefaultInvoiceAddress 和 DefaultDeliveryAddress ）以及他所有地址的一个地址： AllAddresses 。

映射等工作，创建和更新用户和地址也起作用。

虽然将用户的现有地址设置为例如，但不起作用。 DefaultInvoiceAddress。在SQL术语中，我想要发生的是更新用户设置DefaultInvoiceAddressId = 5 WHERE Id = 3 。

以下方式尝试过：

  private void MarkAs（User user，UserAddress address，User.AddressType type）{
 if（context.Entry（user）.State == EntityState.Detached）
 context.Users.Attach（user）; 
 
 //猜测我真的不需要这样：
 if（context.Entry（address）.State == EntityState.Detached）
 context.UserAddresses.Attach（address ）; 
 
 if（type.HasFlag（User.AddressType.DefaultInvoice））{
 user.DefaultInvoiceAddressId = address.Id; 
 user.DefaultInvoiceAddress = null; 
 context.Entry（user）.Property（u => u.DefaultInvoiceAddressId）.IsModified = true; 
} 
 
 if（type.HasFlag（User.AddressType.DefaultDelivery））{
 user.DefaultDeliveryAddressId = address.Id; 
 user.DefaultDeliveryAddress = null; 
 context.Entry（user）.Property（u => u.DefaultDeliveryAddressId）.IsModified = true; 
} 
} 
  

此方法在创建新的UserAddress时也被调用当更新地址时。创建方案按预期工作，但在更新情况下，我收到以下错误：

 数据库的更改已成功提交，
，但更新对象上下文时发生错误。 
 ObjectContext可能处于不一致的状态。 
内部异常消息：发生参照完整性约束违规：
定义参照约束的属性值在关系中的主体和从属对象之间不一致。 
  

我使用我从数据库中读取的User对象和包含的DefaultDeliveryAddress来调用该方法，其中I通过加载加载加载。

  var user = mainDb.User.Get（UnitTestData.Users.Martin.Id，User。 Include.DefaultAddresses）; 
 var existingAddress = user.DefaultDeliveryAddress; 
 mainDb.User.Addresses.SetAs（user，existingAddress，User.AddressType.DefaultInvoice））
 // SetAs方法验证输入参数，调用MarkAs然后SaveChanges 
  

简而言之，我只想让用户的DefaultDeliveryAddress也是他的DefaultInvoiceAddress，这将很容易实现使用上面的SQL Update命令，但我缺少一些与我的EF代码。
我已经检查过：

• 只有Id被设置，导航属性（ DefaultInvoiceAddress ）被重新设置为null


• UserAddress.UserId = User.Id（显然因为它已经分配给用户）


• 用户对象将变为修改（使用调试器检查），因为其某个属性被标记为已修改


• 我也尝试清除这两个默认的地址导航属性，但这并没有帮助

我怀疑这个问题是由于用户实体有2个UserAddress的引用，并且这两个外键都被设置为指向相同的地址 - 如何让EF与之配合使用？

更新： / strong>

以下是用户实体的映射：

  // from UserMap.cs：
 ... 
属性（t => t.DefaultInvoiceAddressId）.HasColumnName（DefaultInvoiceAddressId）; 
属性（t => t.DefaultDeliveryAddressId）.HasColumnName（DefaultDeliveryAddressId）; 
 
 //关系
 HasOptional（t => t.DefaultInvoiceAddress）
 .WithMany（）
 .HasForeignKey（t => t.DefaultInvoiceAddressId）; 
 
 HasOptional（t => t.DefaultDeliveryAddress）
 .WithMany（）
 .HasForeignKey（t => t.DefaultDeliveryAddressId）; 
 
 HasMany（t => t.AllAddresses）
 .WithRequired（）
 .HasForeignKey（t => t.UserId）
 .WillCascadeOnDelete（）; 
  

UserAddress没有导航属性返回给用户;它只包含HasMaxLength和HasColumnName设置（我排除他们以保持问题有些可读性）。

更新2

这是Intellitrace执行的命令：

 命令文本update [TestSchema]。[User ] 
 set [DefaultInvoiceAddressId] = @ 0 
其中（[Id] = @ 1）
在连接上执行Server =（localdb）\ ...
  

对我来说看起来不错似乎只有EF状态管理器被密钥映射困惑。

解决方案

解决问题：显然，将导航属性设置为null，因为EF可能会将其解释为预期的更改/更新（至少这是我怀疑的）。

以下版本的 MarkAs 方法工作：

  private void MarkAs（User user，UserAddress address，User .AddressType type）{
 if（context.Entry（user）.State == EntityState.Detached）{
 //清除引导属性之前附加实体
 user.DefaultInvoiceAddress = null; 
 user.DefaultDeliveryAddress = null; 
 context.Users.Attach（user）; 
} 
 //地址不必附加
 
 if（type.HasFlag（User.AddressType.DefaultInvoice））{
 //以前我试过清除导航属性
 user.DefaultInvoiceAddressId = address.Id; 
 context.Entry（user）.Property（u => u.DefaultInvoiceAddressId）.IsModified = true; 
} 
 
 if（type.HasFlag（User.AddressType.DefaultDelivery））{
 user.DefaultDeliveryAddressId = address.Id; 
 context.Entry（user）.Property（u => u.DefaultDeliveryAddressId）.IsModified = true; 
} 
} 
  

总结未来读者的发现：

• 如果您打算通过外键属性更新实体，请清除导航属性。 EF不需要他们找出更新声明。


• 清除导向属性之前将实体附加到上下文中，否则EF可能将其解释为更改（在我的情况下，外键为空），如果不是这样，EF可能足够聪明以忽略导航属性更改）。

$ b $我不会立即接受我自己的答复，让其他（更有资格）的读者有机会回答;如果在接下来的2天内没有发布任何答案，我会接受这一个。

Sorry for the nebulous title, it's hard to describe this in a single line:

I have 2 entities User and UserAddress, where User has 2 foreign keys DefaultInvoiceAddressId and DefaultDeliveryAddressId and UserAddress has a UserId foreign key.

The user object has navigation properties for the default addresses (DefaultInvoiceAddress and DefaultDeliveryAddress) as well as one for all of his addresses: AllAddresses.

The mapping etc. works, creating and updating users and addresses works too.

What does not work though is setting an existing Address of a User as e.g. DefaultInvoiceAddress. In SQL terms, what I want to happen is UPDATE USER SET DefaultInvoiceAddressId = 5 WHERE Id = 3.

I've tried this the following way:

private void MarkAs(User user, UserAddress address, User.AddressType type) {
        if (context.Entry(user).State == EntityState.Detached)
            context.Users.Attach(user);

        // guess I don't really need this:
        if (context.Entry(address).State == EntityState.Detached)
            context.UserAddresses.Attach(address);

        if (type.HasFlag(User.AddressType.DefaultInvoice)) {
            user.DefaultInvoiceAddressId = address.Id;
            user.DefaultInvoiceAddress = null;
            context.Entry(user).Property(u => u.DefaultInvoiceAddressId).IsModified = true;
        }

        if (type.HasFlag(User.AddressType.DefaultDelivery)) {
            user.DefaultDeliveryAddressId = address.Id;
            user.DefaultDeliveryAddress = null;
            context.Entry(user).Property(u => u.DefaultDeliveryAddressId).IsModified = true;
        }
    }

This method is called both when creating new UserAddresses as well as when updating addresses. The create scenario works as expected, however in the update case I receive the following error:

The changes to the database were committed successfully, 
but an error occurred while updating the object context. 
The ObjectContext might be in an inconsistent state. 
Inner exception message: 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 call the method with a User object I retrive from the database and the DefaultDeliveryAddress it contains, which I load alongside it via eager loading.

var user = mainDb.User.Get(UnitTestData.Users.Martin.Id, User.Include.DefaultAddresses);
var existingAddress = user.DefaultDeliveryAddress;
mainDb.User.Addresses.SetAs(user, existingAddress, User.AddressType.DefaultInvoice))
// the SetAs method verfies input parameters, calls MarkAs and then SaveChanges

In a nutshell, I just want to make the DefaultDeliveryAddress of a user also his DefaultInvoiceAddress, which would be easily accomplished with the above SQL Update command, but I'm missing something with my EF code. I've already checked that:

• Only the Id is set, the navigation property (DefaultInvoiceAddress) is re-set to null
• UserAddress.UserId = User.Id (obviously since it is already assigned to the user)
• The user object will become Modified (checked with debugger), since one of its properties is being marked as modified
• I also tried clearing both default address navigation properties, but that didn't help either

I suspect this problem is due to the User entity having 2 references to UserAddress, and both foreign keys are set to refer to the same address - how can I get EF to work with that?

Update:

Here are the mappings of the User entity:

// from UserMap.cs:
...
        Property(t => t.DefaultInvoiceAddressId).HasColumnName("DefaultInvoiceAddressId");
        Property(t => t.DefaultDeliveryAddressId).HasColumnName("DefaultDeliveryAddressId");

        // Relationships
        HasOptional(t => t.DefaultInvoiceAddress)
            .WithMany()
            .HasForeignKey(t => t.DefaultInvoiceAddressId);

        HasOptional(t => t.DefaultDeliveryAddress)
            .WithMany()
            .HasForeignKey(t => t.DefaultDeliveryAddressId);

        HasMany(t => t.AllAddresses)
            .WithRequired()
            .HasForeignKey(t => t.UserId)
            .WillCascadeOnDelete();

UserAddress has no navigation properties back to User; it only contanis HasMaxLength and HasColumnName settings (I exclude them to keep the question somewhat readable).

Update 2

Here's the executed command from Intellitrace:

The command text "update [TestSchema].[User]
set [DefaultInvoiceAddressId] = @0
where ([Id] = @1)
" was executed on connection "Server=(localdb)\..."

Looks fine to me; seems only EF state manager gets confused by the key mappings.

解决方案

Figured out the problem: apparently it makes quite the difference when to set navigational properties to null, as EF might otherwise interpret that as an intended change / update (at least that is what I suspect).

The following version of the MarkAs method works:

private void MarkAs(User user, UserAddress address, User.AddressType type) {
        if (context.Entry(user).State == EntityState.Detached) {
            // clear navigation properties before attaching the entity
            user.DefaultInvoiceAddress = null;
            user.DefaultDeliveryAddress = null;
            context.Users.Attach(user);
        }
        // address doesn't have to be attached

        if (type.HasFlag(User.AddressType.DefaultInvoice)) {
            // previously I tried to clear the navigation property here
            user.DefaultInvoiceAddressId = address.Id;
            context.Entry(user).Property(u => u.DefaultInvoiceAddressId).IsModified = true;
        }

        if (type.HasFlag(User.AddressType.DefaultDelivery)) {
            user.DefaultDeliveryAddressId = address.Id;
            context.Entry(user).Property(u => u.DefaultDeliveryAddressId).IsModified = true;
        }
    }

To sum up my findings for future readers:

• If you intend to update entities via Foreign Key properties, clear navigation properties. EF doesn't need them to figure out the update statement.
• Clear navigation properties before you attach an entity to a context, otherwise EF might interpret that as a change (in my case the foreign key is nullable, if that isn't the case EF might be smart enough to ignore the navigation property change).

I will not accept my own answer right away to give other (more qualified) readers a chance to answer; if no answers are posted in the next 2 days, I'll accept this one.

这篇关于为什么更新外键后引用约束不一致？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！