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
I have three aggregate roots "Buyer, Seller, Employee" class. One value object "Address" class. The "Buyer" aggregate root class has billing address and shipping address property. The "Seller" aggregate root class has only one address property. How do i configure the entities using fluent api for this scenario? — Yuvaraj Velmurugan, Jun 05 '22 at 13:36

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

2 Answers2