composite key as foreign key

Question

I am using Entity framework 4.1 in MVC 3 application. I have an entity where I have primary key consists of two columns ( composite key). And this is being used in another entity as foreign key. How to create the relationship ? In normal scnerios we use :

public class Category { public string CategoryId { get; set; } public string Name { get; set; } public virtual ICollection<Product> Products { get; set; } } public class Product { public int ProductId { get; set; } public string Name { get; set; } public string CategoryId { get; set; } public virtual Category Category { get; set; } }

but what if category has two columns key ?

Ladislav Mrnka · Accepted Answer · 2011-03-26 09:56:08Z

You can use either fluent API:

public class Category { public int CategoryId1 { get; set; } public int CategoryId2 { get; set; } public string Name { get; set; } public virtual ICollection<Product> Products { get; set; } } public class Product { public int ProductId { get; set; } public string Name { get; set; } public int CategoryId1 { get; set; } public int CategoryId2 { get; set; } public virtual Category Category { get; set; } } public class Context : DbContext { public DbSet<Category> Categories { get; set; } public DbSet<Product> Products { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<Category>() .HasKey(c => new {c.CategoryId1, c.CategoryId2}); modelBuilder.Entity<Product>() .HasRequired(p => p.Category) .WithMany(c => c.Products) .HasForeignKey(p => new {p.CategoryId1, p.CategoryId2}); } }

Or data annotations:

public class Category { [Key, Column(Order = 0)] public int CategoryId2 { get; set; } [Key, Column(Order = 1)] public int CategoryId3 { get; set; } public string Name { get; set; } public virtual ICollection<Product> Products { get; set; } } public class Product { [Key] public int ProductId { get; set; } public string Name { get; set; } [ForeignKey("Category"), Column(Order = 0)] public int CategoryId2 { get; set; } [ForeignKey("Category"), Column(Order = 1)] public int CategoryId3 { get; set; } public virtual Category Category { get; set; } }

Do I need to keep the virtual properties ( public virtual Category Category { get; set; }) as well as data annovations ?
virtual on navigation properties is necessary for lazy loading. virtual on scalar properties helps with change tracking of attached objects.
What would you do if the foreign key table's column names were different than what is in the parent? Fore example, In product, how would you label the ForeignKey attribute if the column names looked like: PCategoryId2, PCategoryId3?
Regarding to this line: .HasRequired(p => p.Category) but Product doesn't have a property of the Entity Catagory but two ids which make the composite key of a catagory. Can you please explain, because I believe it won't even compile... Thanks!

Christophe · Accepted Answer · 2017-08-26 22:30:53Z

I believe the easiest way is to use Data Annotation on the Navigation property like this: [ForeignKey("CategoryId1, CategoryId2")]

public class Category { [Key, Column(Order = 0)] public int CategoryId1 { get; set; } [Key, Column(Order = 1)] public int CategoryId2 { get; set; } public string Name { get; set; } public virtual ICollection<Product> Products { get; set; } } public class Product { [Key] public int ProductId { get; set; } public string Name { get; set; } public int CategoryId1 { get; set; } public int CategoryId2 { get; set; } [ForeignKey("CategoryId1, CategoryId2")] public virtual Category Category { get; set; } }

This worked great. I too prefer to use this on Navigation properties. However, how can I set cascadeDelete: false for this property only, not site-wide? Thanks
In some cases the foreign key is also part of the current table's composite key. This way worked. The other way (@Ladislov) did not. I got the error: "Duplicate Column attribute"
RoLYroLLs: cascadeDelete is set in the migration file (after using the add-migration package manager command). An example: AddForeignKey("dbo.Product", "GuidedActivityID", "dbo.GuidedActivity", "ID", cascadeDelete: false);

Ogglas · Accepted Answer · 2022-02-25 13:47:28Z

In .NET Core and .NET 5 < the documentation only shows Data annotations (simple key).

https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-composite-key%2Csimple-key#foreign-key

However using the example from @LadislavMrnka you will get a error message like this:

System.InvalidOperationException: There are multiple properties with the [ForeignKey] attribute pointing to navigation ''. To define a composite foreign key using data annotations, use the [ForeignKey] attribute on the navigation.

Using that error message you can write the code like this:

public class Product { [Key] public int ProductId { get; set; } public string Name { get; set; } public int CategoryId2 { get; set; } public int CategoryId3 { get; set; } [ForeignKey("CategoryId2,CategoryId3")] public virtual Category Category { get; set; } }

Fluent API (composite key) example from Microsoft:

internal class MyContext : DbContext { public DbSet<Car> Cars { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Car>() .HasKey(c => new { c.State, c.LicensePlate }); modelBuilder.Entity<RecordOfSale>() .HasOne(s => s.Car) .WithMany(c => c.SaleHistory) .HasForeignKey(s => new { s.CarState, s.CarLicensePlate }); } } public class Car { public string State { get; set; } public string LicensePlate { get; set; } public string Make { get; set; } public string Model { get; set; } public List<RecordOfSale> SaleHistory { get; set; } } public class RecordOfSale { public int RecordOfSaleId { get; set; } public DateTime DateSold { get; set; } public decimal Price { get; set; } public string CarState { get; set; } public string CarLicensePlate { get; set; } public Car Car { get; set; } }

Collectives™ on Stack Overflow

composite key as foreign key

3 Answers 3

11 Comments

3 Comments

Comments

Linked

Hot Network Questions