Create one-to-one relation without adding reference in one of the tables

Question

I have 2 tables Company (Id uniqueidentifier, ...) and Address (Id int, ...) and representative entities Company and Address.

They should have a one-to-one relationship. As I don't want to have any reference to Company in the Address entity, I thought of creating a join table CompanyAddress (CompanyId, AddressId).

In mapping, I did the following:

modelBuilder.Entity<Company>(
            entity =>
                {
                     entity.HasOne(c => c.Address)
                          .WithOne(c => c.Company)
                          .HasForeignKey<Address>(c => c.Id);
                }

but I get an error:

The relationship from 'Address.Company' to 'Company.Address' with foreign key properties {'Id' : int} cannot target the primary key {'Id' : Guid} because it is not compatible. Configure a principal key or a set of foreign key properties with compatible types for this relationship.

This issue I cannot resolve without help.

Or, maybe, there is different way of mapping for one-to-one?

Answer 1

The error and your concern about the bi-directional reference are actually two separate issues.

Firstly, you don't require a Company navigation property in Address if you don't want it:

modelBuilder.Entity<Company>(
        entity =>
            {
                 entity.HasOne(c => c.Address)
                      .WithOne()
                      .HasForeignKey<Address>(c => c.Id);
            }

By default with one-to-one relationships it will attempt to join on the two table PKs, which is also how you are explicitly mapping via: .HasForeignKey<Address>(c => c.Id); Your problem is that Company has a PK type of Guid while your Address's PK is int.

For a better example of a one-to-one say you have Company and a CompanyDetails table where the Details held large, seldom used pieces of information that you don't want to read every time you load a company. The PK in both of these tables could/should just be a CompanyId as there is always just at most a pair of matching records. (Whether Details is required or optional) It would be confusing to have a CompanyId paired with a CompanyDetailId as a PK/FK. This is one reason I'm not a fan of using "Id" as a PK since it can mask potential issues like this where FKs are typically named by type but PKs are not.

Since Addresses are likely not going to just be reserved for Companies, where Address.Id == Company.Id, What you need is to have an AddressId FK in your Company Table and update the FK reference: .HasForeignKey<Company>(c => c.AddressId);