Why become referential constraints inconsistent after updating foreign key?

Question

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.

Lawful

Post the User & Address definitions and configurations. Also post the actual SQL statemnts produced. – Reardon

Hmm, the SELECT can simply be gotten from a query but for UPDATE/INSERT you'll need the SQL Mgmt studio or a profile (mini profiler seems worth a look). – Reardon

I found a way to get the statement with Intellitrace, I've updated my question with the command. – Lawful

Answer 1

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.

Lawful