Multiple one-to-one relations create duplicate foreign key field.

[KorucuTech]

Have 2 entities

public class Customer {
  public int Id {get; set;}
  public string Name {get; set;}
  public int? ShipToAddressId {get; set;}
  public int? BillToAddressId {get;set;}
  public virtual CustomerAddress ShipToAddress {get;set;}
  public virtual CustomerAddress BillToAddress {get;set}
  public virtual ICollection<CustomerAddress> CustomerAddresses {get;set;} 
}

public class CustomerAddress {
  public int Id {get;set;}
  public int CustomerId {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public virtual Customer Customer {get;set'}
}

with the following Fluent API in DbContext OnModelCreating method

        var customeraddress = builder.Entity<CustomerAddress>();
        customeraddress.HasKey(t => t.Id);
        customeraddress.Property(t => t.Id).ValueGeneratedOnAdd();
        customeraddress.Property(t => t.Name).IsRequired();
        customeraddress.Property(t => t.Address).IsRequired();

        var customer = builder.Entity<Customer>();
        customer.HasKey(t => t.Id);
        customer.Property(t => t.Id).ValueGeneratedOnAdd();
        customer.Property(t => t.Name).IsRequired();
        customer.HasOne(t => t.BillToAddress)
                .WithOne(c => c.Customer)
                .HasForeignKey<Customer>(f => f.BillToAddressId);
        customer.HasOne(t => t.ShipToAddress)
                .WithOne(c => c.Customer)
                .HasForeignKey<Customer>(f => f.ShipToAddressId);   

this generates an extra field called "BillToAddressId1" in Customers Table instead of using already existing "BillToAddressId" field.
So Customers table gets created with both fields.

thanks

[smitpatel]

        customer.HasOne(t => t.BillToAddress)
                .WithOne(c => c.Customer)
                .HasForeignKey<Customer>(f => f.BillToAddressId);
        customer.HasOne(t => t.ShipToAddress)
                .WithOne(c => c.Customer)
                .HasForeignKey<Customer>(f => f.ShipToAddressId);   

The Navigation Customer has been specified in both relationships. One navigation property can be part of only one relationship. So when you use it for other relationship, EF will modify the first relationship to remove Customer navigation from there. In the process that relationship will be modified to use fk property as BillToAddressId1.

[KorucuTech]

based on your explanation I made the following change and it worked fine.
Just removed what comes after HasOne.

customer.HasOne(t => t.ShipToAddress");
customer.HasOne(t => t.BillToAddress");

thanks

[divega]

@Kemaletdin glad to hear that it helped.

Anyway, it seems there are a few issues here:

1. The current behavior doesn't seem to make much sense, e.g. it is not clear why we would create a new FK for the first relationship. @smitpatel do you know if that is by design and if yes why?

2. I believe what the customer was trying to do from the perspective of the object model and its navigations actually makes sense:

   • a customer has a ship-to and a bill-to addresses;
   • addresses know what customer they belong to

   However the configuration of keys fails to express some necessary constraints (e.g. an address should not be associated with more than one customer), so maybe we can provide the following guidance, e.g.:

   • Make Address have a composite key formed by CustomerId and an AddressType property.
   • Make Customer have a collection navigation property of addresses

   So it is actually possible to model this scenario in that way with the current stack. The only pieces that seems to be missing is the ability to define BillToAddress and ShipToAddress as computed navigation properties that represent filtered views over the collection of all the addresses associated with a customer.

3. This case makes me wonder about the existence of scenarios in which last-one-wins rule can be too counterintuitive.

[smitpatel]

Answer to 1 above:
The way model building currently works in above case, when we use the same navigation Customer again, instead of removing the navigation from previous relationship and assigning to this one, we change the other navigation BillToAddress with ShipToAddress. Therefore as the stage when we have specified only navigations for second relationship, we have relationship with ShipToAddress & Customer navigations and BillToAddressId fk property (which was explicitly set before). At this stage we also re-discover the removed navigation BillToAddress to form relationship which by convention will use BillToAddressId1 fk property since BillToAddressId is already in use. Afterwards, we change the fk property of second relationship but that doesn't change the other relationship. Hence we get additional column.

[KorucuTech]

Is it not odd that the issue I was having was solved by just removing the foreign-key definitions.
When I have the foreign keys explicitly defined I am just stating something EF assumes if I did not explicitly define the keys.

[rowanmiller]

Is it not odd that the issue I was having was solved by just removing the foreign-key definitions.

The thing that made the difference between your original configuration...

 customer.HasOne(t => t.BillToAddress)
                .WithOne(c => c.Customer)
                .HasForeignKey<Customer>(f => f.BillToAddressId);
        customer.HasOne(t => t.ShipToAddress)
                .WithOne(c => c.Customer)
                .HasForeignKey<Customer>(f => f.ShipToAddressId);   

And the new configuration...

customer.HasOne(t => t.ShipToAddress");
customer.HasOne(t => t.BillToAddress");

Was the removal of .WithOne(c => c.Customer). The original configuration was trying to use CustomerAddress.Customer as the navigation property for the two relationships - which is not supported by EF.

I do agree the outcome if confusing though, we will discuss and see what we can come up with.

[AndriySvyryd]

We decided to throw in this case as it seems to be fairly common.