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
– Serge, Commented Aug 21, 2021 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
– Paullus Nava, Commented Aug 21, 2021 at 18:34

dglozano · Accepted Answer · 2021-08-25 21:51:35Z

2

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.

edited Aug 25, 2021 at 21:51

answered Aug 21, 2021 at 19:59

dglozano

6,6472 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paullus Nava Over a year ago

Hi @dglozano this is not possible. When setting the HasForeignKey<CompanyAddress>(da => da.DefaultAddressId) (for example) CompanyAddress would have to have DefaultAddressId.

Paullus Nava Over a year ago

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<T> from CompanyAddress to Company. The correct answer should be .HasForeignKey<Company>(da => da.DefaultAddressId). Please fix it and notify me so I can upvote and mark as resolved. Thanks!

dglozano Over a year ago

@PaullusNava you are right, copy-pasta :) have edited the answer.

Dorin Baba · Accepted Answer · 2021-08-25 22:18:59Z

1

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");
    }

edited Aug 25, 2021 at 22:18

answered Aug 25, 2021 at 22:13

Dorin Baba

1,7481 gold badge14 silver badges24 bronze badges

3 Comments

Paullus Nava Over a year ago

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. :)

Dorin Baba Over a year ago

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 :)

Yuvaraj Velmurugan Over a year ago

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?

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related