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

asp.net-mvc-5 composite-primary-key entity-framework entity-framework-6 foreign-key-relationship

Question

I have two stools and layered main keys, and Both has a foreign key on one of the main keys to another table in common.

The issue is that the foreign keys get messed up when I create migrations.

I have to employ 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; }
}
  • The primary key for PostCode and City is composite (Code, CompanyCode).
  • A foreign key to the table is held by PostCode (CityCode, CompanyCode).

The CompanyCode is a portion of the composite foreign key that is also a portion of the primary key. to City, which is the problem.

I mean the following when I say that it screws up the foreign keys:

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, CompanyCode is referred to by CityCode and CityCode is referred to by CompanyCode.

There are no examples of this situation on the internet.

Where have I erred?

I appreciate it.

Edit 1

A simple primary key, CompanyCode, is located between City and Company. Both PostCodes and Company are the same.

1
2
2/11/2015 1:55:50 AM

Popular Answer

If both, thenCity and Company You want to establish a one-on-one connection. I'm sorry, but using your model makes it impossible. Entity Framework needs that the main key of the dependent end also be the foreign key when defining a one-to-one connection; otherwise, EF doesn't recognize the relationship as one-to-one. The dependent party in your situation isCity You want to add another PK, but you have a difficulty with it.CityCode , which violates the definition of what a one-to-one relationship is since, for instance, the following records could occur:

Company             City 
Id            CityCode CompanyId
1               ee33a      1
2               aa23b      1

As a result, I believe that in order to realize your scenario, you must establish a one-to-many relationship betweenCompany and City Using Data Annotations might go like this:

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

Leaving out theCities navigating using a propertyCompany If you prefer not to use references to the cities associated with aCompany .

The same holds true for thePostCode Entity.

Update:

To fulfill your goals in thePostCode entity, you must map the FKs as follows:

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

A nice illustration of how to handle the composite FKs is Here.

1
5/23/2017 12:23:34 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow