Multiple foreign keys pointing to same table in Entity Framework 4.1 code first

Question

I'm stuck at trying to write the Entity Framework 4.1 code first model for the following DB relationship.

Here is a visual of the relationship.

dbo.[Companies] can have either Seller or Debtor as Company Types.

dbo.[SellerDebtors] defines the connection a Seller Company has with a Debtor Company.

The code i've written is based on my original EF 4.0 POCO model code. This is what I've come up with - This code does not work.

public class SellerDebtor
{
    public int SellerDebtorId { get; set; }
    public int DebtorCompanyId { get; set; }
    public int SellerCompanyId { get; set; }

    public Company DebtorCompany { get; set; }
    public Company SellerCompany { get; set; }

    public ICollection<SellerDebtorInfo> SellerDebtorInfos { get; set; }
    public ICollection<SellerDebtorFile> SellerDebtorFiles { get; set; }    
}


public class Company
{
    public int CompanyId { get; set; }
    public string CompanyType { get; set; }
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<CompanyInfo> CompanyInfos { get; set; }
    public virtual ICollection<CompanyFile> CompanyFiles { get; set; }

    public virtual ICollection<SellerDebtor> SellerDebtorDebtorCompanies { get; set; }
    public virtual ICollection<SellerDebtor> SellerDebtorSellerCompanies { get; set; }

}

At the moment, I'm getting this as an error:

System.Data.SqlClient.SqlException: Invalid column name 'DebtorCompany_CompanyId'.
Invalid column name 'SellerCompany_CompanyId'.
Invalid column name 'Company_CompanyId'.
Invalid column name 'Company_CompanyId1'.

Ideally, I'd like to be able to maintain the naming of the relationships.

I'm guessing i need to set some attributes but i'm not sure what to set.

Answer 1

EF is not able to determine by convention which navigation properties on your 2 classes belong together and creates 4 relationships (without an end on the other side) instead of 2 (with ends on both sides). This problem occurs always when you have more than one navigation property of the same type (Company in your case) in the same class. You could try to fix this the following way:

public class SellerDebtor
{
    public int SellerDebtorId { get; set; }
    [ForeignKey("DebtorCompany")]
    public int DebtorCompanyId { get; set; }
    [ForeignKey("SellerCompany")]
    public int SellerCompanyId { get; set; }

    [InverseProperty("SellerDebtorDebtorCompanies")]
    public Company DebtorCompany { get; set; }
    [InverseProperty("SellerDebtorSellerCompanies")]
    public Company SellerCompany { get; set; }

    public ICollection<SellerDebtorInfo> SellerDebtorInfos { get; set; }
    public ICollection<SellerDebtorFile> SellerDebtorFiles { get; set; }    
}

[InverseProperty(...)] defines the navigation property on the other end of the relationship and it tells EF explicitely which pairs of navigation properties belong together in a relationship.

Answer 2

This blog has the example using Fluent API configurations.

Multiple foreign keys within same table using CodeFirst Entity Framework and Fluent API

modelBuilder.Entity<Branch>().HasOptional(b => b.PrimaryContact)         
            .WithMany(a => a.PrimaryContactFor).HasForeignKey(b=>b.PrimaryContactID);