EntityFramework - Composite Key Table with FK to another Composite Key Table

Question

I've two tables both with composite primary keys. Both have in common one of the primary key's with a foreign key to another table.

The problem is that when i create the migrations, it messes up the foreign keys.

I must use data annotations.

Example:

public class City
{
    [Key, Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string CityCode { get; set; } 

    [Key, Column(Order = 2)]
    public string CompanyCode { get; set; }

    public string Description { get; set; }     

    [ForeignKey("CompanyCode")]
    public virtual Company Company { get; set; }
}

public class PostCode
{
    [Key, Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Code { get; set; } 

    [Key, Column(Order = 1)]
    public string CompanyCode { get; set; }

    public string Description { get; set; } 

    public string CityCode { get; set; }      

    [ForeignKey("CompanyCode")]
    public virtual Company Company { get; set; }

    [ForeignKey("CityCode, CompanyCode")]
    public virtual City City { get; set; }
}

• PostCode and City have a composite primary key (Code, CompanyCode).
• PostCode has a foreign key to the table City (CityCode, CompanyCode).

The problem is that the CompanyCode is part of the primary key and at the same time is part of the composite foreign key to City.

When i say that it messes up the foreign keys i mean the following:

CONSTRAINT [FK_dbo.PostCodes_dbo.Companies_CompanyCode] FOREIGN KEY ([CompanyCode]) REFERENCES [dbo].[Companies] ([CompanyCode]) ON DELETE CASCADE,
CONSTRAINT [FK_dbo.PostCodes_dbo.Cities_CompanyCode_CityCode] FOREIGN KEY ([CompanyCode], [CityCode]) REFERENCES [dbo].[Cities] ([CityCode], [CompanyCode])

In the second constraint, it references CompanyCode with CityCode and CityCode with CompanyCode.

I can't find any example in the internet with any scenario like this.

Where am i wrong?

Thanks in advance.

Edit 1

Between City and Company there is a simples primar key CompanyCode. The same for PostCodes and Company.

Answer 1

If between City and Company you want to create a one-to-one relationship I'm afraid that is not possible following your model. When you are configuring a one-to-one relationship, Entity Framework requires that the primary key of the dependent end also be the foreign key, otherwise EF doesn't see it as one-to-one relationship. The dependend end in your case es City, but you have a problem, you want to add another PK, that is CityCode, that breaks what it means a one to one relationship because, for example, the below records could happen:

Company             City 
Id            CityCode CompanyId
1               ee33a      1
2               aa23b      1

That's way, if you want to achieve your escenario, I guess that you have to create a one-to-many relationship between Company and City. Using Data Annotations could be this way:

public class City
{
    [Key, Column(Order = 1)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string CityCode { get; set; }

    [Key, ForeignKey("Company"),Column(Order = 2)]
    public string CompanyCode { get; set; }

    public string Description { get; set; }

    public virtual Company Company { get; set; }
}

public class Company
{
    public string Id { get; set; }
    public virtual ICollection<City> Cities { get; set; }
}

You can omit the Cities navigation property in Company if you don't want have reference to the cities related to a Company.

The same applies to the PostCode Entity.

Update:

To achieve what you want in the PostCode entity, you have to map the FKs this way:

public class PostCode
{
    [Key, Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string Code { get; set; }

    [Key,ForeignKey("City"), Column(Order = 2)]
    public string CityCompanyCode { get; set; }

    public string Description { get; set; }

    [ForeignKey("City"), Column(Order = 1)]
    public string CityCode { get; set; }


    public virtual City City { get; set; }

    [ForeignKey("Company")]
    public string CompanyCode { get; set; }
    public virtual Company Company { get; set; }
}

Here is a good example of how you should treat the composite FKs

Answer 2

I always add a primary key to my tables, por example: CityId int, PostCode int. with this i resolve relations.

public class PostCode
{   
[Key, Column(Order = 0)]
public string PostCodeId  { get; set; } 

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public string Code { get; set; } 

public string CompanyCode { get; set; }

public string Description { get; set; } 

public string CityCode { get; set; }      

[ForeignKey("CompanyCode")]
public virtual Company Company { get; set; }

[ForeignKey("CityCode, CompanyCode")]
public virtual City City { get; set; }
}

thanks