title: Why become referential constraints inconsistent after updating foreign key?
body: <p>Sorry for the nebulous title, it's hard to describe this in a single line:</p>
  
  <p>I have 2 entities <code>User</code> and <code>UserAddress</code>, where User has 2 foreign keys <code>DefaultInvoiceAddressId</code> and <code>DefaultDeliveryAddressId</code> and UserAddress has a <code>UserId</code> foreign key.</p>
  
  <p>The user object has navigation properties for the default addresses (<code>DefaultInvoiceAddress</code> and <code>DefaultDeliveryAddress</code>) as well as one for all of his addresses: <code>AllAddresses</code>. </p>
  
  <p>The mapping etc. works, creating and updating users and addresses works too. </p>
  
  <p>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 <code>UPDATE USER SET DefaultInvoiceAddressId = 5 WHERE Id = 3</code>.</p>
  
  <p>I've tried this the following way:</p>
  
  <pre><code>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 =&gt; u.DefaultInvoiceAddressId).IsModified = true;
          }
  
          if (type.HasFlag(User.AddressType.DefaultDelivery)) {
              user.DefaultDeliveryAddressId = address.Id;
              user.DefaultDeliveryAddress = null;
              context.Entry(user).Property(u =&gt; u.DefaultDeliveryAddressId).IsModified = true;
          }
      }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>In a nutshell,</strong> 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:</p>
  
  <ul>
  <li>Only the Id is set, the navigation property (<code>DefaultInvoiceAddress</code>) is re-set to null</li>
  <li>UserAddress.UserId = User.Id (obviously since it is already assigned to the user)</li>
  <li>The user object will become <code>Modified</code> (checked with debugger), since one of its properties is being marked as modified</li>
  <li>I also tried clearing both default address navigation properties, but that didn't help either</li>
  </ul>
  
  <p>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?</p>
  
  <p><strong>Update:</strong> </p>
  
  <p>Here are the mappings of the User entity:</p>
  
  <pre><code>// from UserMap.cs:
  ...
          Property(t =&gt; t.DefaultInvoiceAddressId).HasColumnName("DefaultInvoiceAddressId");
          Property(t =&gt; t.DefaultDeliveryAddressId).HasColumnName("DefaultDeliveryAddressId");
  
          // Relationships
          HasOptional(t =&gt; t.DefaultInvoiceAddress)
              .WithMany()
              .HasForeignKey(t =&gt; t.DefaultInvoiceAddressId);
  
          HasOptional(t =&gt; t.DefaultDeliveryAddress)
              .WithMany()
              .HasForeignKey(t =&gt; t.DefaultDeliveryAddressId);
  
          HasMany(t =&gt; t.AllAddresses)
              .WithRequired()
              .HasForeignKey(t =&gt; t.UserId)
              .WillCascadeOnDelete();
  </code></pre>
  
  <p>UserAddress has no navigation properties back to User; it only contanis HasMaxLength and HasColumnName settings (I exclude them to keep the question somewhat readable).</p>
  
  <p><strong>Update 2</strong></p>
  
  <p>Here's the executed command from Intellitrace:</p>
  
  <pre><code>The command text "update [TestSchema].[User]
  set [DefaultInvoiceAddressId] = @0
  where ([Id] = @1)
  " was executed on connection "Server=(localdb)\..."
  </code></pre>
  
  <p>Looks fine to me; seems only EF state manager gets confused by the key mappings.</p>