EF Core Net 5 - Code First - Multiple relationships to same entity

Question

I am trying to set multiple relationships between two entities like below:

One company has multiple adresses.
One company has one default address.
One company has one default billing address.
One company has one default delivery address.

public abstract class BaseAddress : AbstractValidatableEntity
{
    public AddressType Type { get; set; }
    public AddressStatus Status { get; set; }
    public Country Country { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Address3 { get; set; }
    public string Address4 { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
}

public class CompanyAddress : BaseAddress
{
    public Guid CompanyId { get; set; }
    public Company Company { get; set; }
}

public class Company : AbstractValidatableEntity
{
    public string Name { get; set; }
    public List<User> Users { get; set; }
    public Guid OwnerId { get; set; }
    public User Owner { get; set; }
    public List<CompanyAddress> Addresses { get; set; }
    public CompanyAddress DefaultAddress { get; set; }
    public CompanyAddress DefaultBillingAddress { get; set; }
    public CompanyAddress DefaultDeliveryAddress { get; set; }
}

public class CompanyConfiguration : AbstractEntityConfiguration<Company>
{
    public override void Configure(EntityTypeBuilder<Company> builder)
    {
        base.Configure(builder);
        // Table name
        builder
            .ToTable("Companies");
        // Columns
        builder
            .Property(s => s.Name)
            .IsRequired(true)
            .HasMaxLength(255);
        // Relationships
        builder
            .HasMany(s => s.Users)
            .WithOne(u => u.Company)
            .HasForeignKey(u => u.CompanyId)
            .IsRequired(false)
            .HasConstraintName("FK_COMPANY_USERS");
        builder
            .HasMany(s => s.Addresses)
            .WithOne(a => a.Company)
            .HasForeignKey(a => a.CompanyId)
            .IsRequired()
            .HasConstraintName("FK_COMPANY_ADDRESSES")
            .OnDelete(DeleteBehavior.Cascade);
        builder
            .HasOne(s => s.DefaultAddress)
            .WithOne()
            .HasForeignKey<CompanyAddress>(da => da.CompanyId)
            .IsRequired(false)
            .HasConstraintName("FK_COMPANY_DEFAULT_ADDRESS")
            .OnDelete(DeleteBehavior.Restrict);
        builder
            .HasOne(s => s.DefaultBillingAddress)
            .WithOne()
            .HasForeignKey<CompanyAddress>(da => da.CompanyId)
            .IsRequired(false)
            .HasConstraintName("FK_COMPANY_DEFAULT_BILLING_ADDRESS")
            .OnDelete(DeleteBehavior.Restrict);
        builder
            .HasOne(s => s.DefaultDeliveryAddress)
            .WithOne()
            .HasForeignKey<CompanyAddress>(da => da.CompanyId)
            .HasConstraintName("FK_COMPANY_DEFAULT_DELIVERY_ADDRESS");
        // Indexes
        builder
            .HasIndex(s => s.Name)
            .HasDatabaseName("IX_COMPANY_NAME");
    }
}

But when creating the database I get the following error:

System.InvalidOperationException: 'Unable to determine the relationship represented by navigation 'Company.Addresses' of type 'List'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.'

Note that I am trying to achieve 1 Many-to-One relationship and 3 One-To-One relationships to the same table, and this is what differs from all other questions I have searched here.

I have read multiple similar problems but they all had the following scenario:
One company has multiple adresses.
One company has one default address.

If I remove the second and third One-To-One relationships in my code it works perfectly. I am not sure what to do code-wise.

I do know that I could do this DB-wise. Having one CompanyAdresses table and having these properties on Company table: DefaultAddressId, DefaultBillingAddressId, DefaultDeliveryAddressId.

Thanks in advance to all of you.

you have already public AddressType Type { get; set; } public AddressStatus Status { get; set; } What they are for? I think it is enough to mark address type and you don't need any extra 3 company addresses — Serge, Aug 21 '21 at 18:13
Hi @Serge, thanks for the answer. That could be and approach. I will think about it... but anyways the question remains. I mean, IF I ever face this situation again I do not know how to get out of it... what should I do in this case? — Paullus Nava, Aug 21 '21 at 18:34

dglozano · Accepted Answer · 2021-08-25T21:51:35.447

You should have a ForeignKey for each of the relations between Company and Address that has a different semantics. For example:


public class Company : AbstractValidatableEntity
{
    public string Name { get; set; }
    public List<User> Users { get; set; }
    public Guid OwnerId { get; set; }
    public User Owner { get; set; }
    public List<CompanyAddress> Addresses { get; set; }
    public CompanyAddress DefaultAddress { get; set; }
    public CompanyAddress DefaultBillingAddress { get; set; }
    public CompanyAddress DefaultDeliveryAddress { get; set; }


    public Guid DefaultAddressId { get; set; }
    public Guid DefaultBillingAddressId { get; set; }
    public Guid DefaultDeliveryAddressId { get; set; }
}

Then, configure each relationship with its own FK.

...
builder
            .HasOne(s => s.DefaultAddress)
            .WithOne()
            .HasForeignKey<Company>(da => da.DefaultAddressId)
            .IsRequired(false)
            .HasConstraintName("FK_COMPANY_DEFAULT_ADDRESS")
            .OnDelete(DeleteBehavior.Restrict);
        builder
            .HasOne(s => s.DefaultBillingAddress)
            .WithOne()
            .HasForeignKey<Company>(da => da.DefaultBillingAddressId)
            .IsRequired(false)
            .HasConstraintName("FK_COMPANY_DEFAULT_BILLING_ADDRESS")
            .OnDelete(DeleteBehavior.Restrict);
        builder
            .HasOne(s => s.DefaultDeliveryAddress)
            .WithOne()
            .HasForeignKey<Company>(da => da.DefaultDeliveryAddressId)
            .HasConstraintName("FK_COMPANY_DEFAULT_DELIVERY_ADDRESS");
...

You could check this and this related answers.

Hi @dglozano this is not possible. When setting the `HasForeignKey(da => da.DefaultAddressId)` (for example) `CompanyAddress` would have to have `DefaultAddressId`. — Paullus Nava, Aug 25 '21 at 21:21
After looking at your references I noticed that the problem is that you copied my source and forgot to change the generic parameter of `HasForeignKey` from `CompanyAddress` to `Company`. The correct answer should be `.HasForeignKey(da => da.DefaultAddressId)`. Please fix it and notify me so I can upvote and mark as resolved. Thanks! — Paullus Nava, Aug 25 '21 at 21:31
@PaullusNava you are right, copy-pasta :) have edited the answer. — dglozano, Aug 25 '21 at 21:52

Dorin Baba · Answer 2 · 2021-08-25T22:18:59.307

Here is another way of dealing with this problem and configuring the relationships directly into the models.

public class Company : AbstractValidatableEntity
{
    public string Name { get; set; }
    public List<User> Users { get; set; }
    public Guid OwnerId { get; set; }
    public User Owner { get; set; }

    [InverseProperty("Companies")]    
    public List<Address> Addresses { get; set; }
    
    [InverseProperty("HostedCompanies")]
    [ForeignKey("DefaultAdressID")]
    public Address DefaultAddress { get; set; }
    public Guid DefaultAddressID { get; set; }

    [InverseProperty("BillingCompanies")]
    [ForeignKey("DefaultBillingAddressID")]
    public Address DefaultBillingAddress { get; set; } 
    public Guid DefaultBillingAddressID { get; set; }

    [InverseProperty("DeliveryCompanies")]
    [ForeignKey("DefaultDeliveryAddressID")]
    public Address DefaultDeliveryAddress { get; set; }
    public Guid DefaultDeliveryAddressID { get; set; }
}

In your Address Model add these

public class Address {
    ...
    public ICollection<Company> Companies { get; set; }
    public ICollection<Company> HostedCompanies { get; set; }
    public ICollection<Company> BillingCompanies { get; set; }
    public ICollection<Company> DeliveryCompanies{ get; set; }
    ...
}

And now you can remove CompanyAddress class, it's not usable anymore. Actually, EF under the hood will generate the CompanyAddress table for you. And you can clear your modelBuilder too:

public override void Configure(EntityTypeBuilder<Company> builder)
    {
        base.Configure(builder);
        
        // Table name
        builder
            .ToTable("Companies");
        
        // Columns
        builder
            .Property(s => s.Name)
            .IsRequired(true)
            .HasMaxLength(255);
        
        // Relationships
        builder
            .HasMany(s => s.Users)
            .WithOne(u => u.Company)
            .HasForeignKey(u => u.CompanyId)
            .IsRequired(false)
            .HasConstraintName("FK_COMPANY_USERS");
        
        // Indexes
        builder
            .HasIndex(s => s.Name)
            .HasDatabaseName("IX_COMPANY_NAME");
    }

Hi @dorin-baba, I will take a look at this approach as well out of curiosity, but my intention with creating the CompanyAddress table was that I did not want Address to have knowledge of Company in code. Thanks for your answer. If it addresses the problem I will upvote it as well, but dglozano 's approach better fits my needs, so I will mark his answer as the accepted. :) — Paullus Nava, Aug 26 '21 at 08:21
That's Ok :) I use this approach because, for example, when I need the billing address of a company, I use `company.BillingAddress`, or when I need all of the companies for which address `a` is the billing address, I use `a.BillingCompanies` and Entity Framework takes care of it. I think is this case, using CompanyAddress would be a little bit more complicated and I'd have to write a few lines more :) — Dorin Baba, Aug 26 '21 at 10:12

EF Core Net 5 - Code First - Multiple relationships to same entity

2 Answers2