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.

Can you share briefly more about your logic? What kind of relationship exist between `City` and `Company`? — ocuenca, Feb 10 '15 at 20:15
@octavioccl ok, wrote it in Edit 1, see the post again please. — Leandro Soares, Feb 11 '15 at 01:58

score 1 · Answer 1 · edited May 23 '17 at 12:23

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

Thank you for your time. But the problem is located between Cities and PostCodes, because i can't specify which primary key from "City" match the the foreign key from "PostCode". I can simulate all this in SQL and get it to work, the problem is that entity framework messes up the foreign keys from "PostCode". If you look closely to the SQL part i posted, you will see that the foreign key [dbo.PostCodes].CityCode matchs the [dbo.Cities].CompanyCode instead [dbo.Cities].CityCode. — Leandro Soares, Feb 11 '15 at 09:32
Hello Leandro, I have updated my answer. You can't map two FKs (although they represent the same field) in one property. You have to separate them. Hope this help you — ocuenca, Feb 11 '15 at 14:41
But i can map two FK's (to a composite primary key table) normally, this problem only occurs when i'm trying to do it from another composite primary key table — Leandro Soares, Feb 11 '15 at 15:13
I know, the FK mapping that was wrong was this:`[ForeignKey("CityCode, CompanyCode")]`. In the ForeignKey Attribute only one property is specified. Did you try the variant that I proposed you in the update? — ocuenca, Feb 11 '15 at 15:31
Yes, it works but the objective was to use only one company column — Leandro Soares, Feb 11 '15 at 17:53
But that is not possible if you are creating two different relationships — ocuenca, Feb 11 '15 at 18:08
It is possible, i can reproduce with SQL. But entity framework just messes up the Keys — Leandro Soares, Mar 27 '15 at 21:19

score 0 · Answer 2 · answered Feb 10 '15 at 16:51

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

yes, i agree that it would be easier, but right now it's not an option... Thanks — Leandro Soares, Feb 10 '15 at 17:04

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

2 Answers2

Update: